As400 SQL Query with Parameter

AS400 SQL query with Parameter

Ok, I got the solution by playing around and trying different things.

As I said before, I am used to OLEDB and ADO.Net so I am used to doing things like:

Select FIELD1, FIELD2 From Mylibrary.MyTable WHERE FIELD1 = @Field1

which work in Access and SQL Server but not in AS/400.

I got the following to work:

Select FIELD1, FIELD2 From Mylibrary.MyTable WHERE FIELD1 = ?

cmd.ActiveConnection = connstr
cmd.CommandType = 1'4   'Stored Procedures '1 Text
cmd.CommandText = sql
Set Param1 = cmd.CreateParameter("@Field1", 129, 1, 9, "myvalue")  ' 129 String
cmd.Parameters.Append Param1
Set rs = cmd.Execute()

This is all VbScript. The trick was to add the question mark(?) in the sql statement.

AS400 SQL Script on a parameter file returns

What you are seeing is EBCDIC output instead of ASCII. This is due to the CCSID not being specified in the database as mentioned in other answers. The ideal solution is to assign the CCSID to your field in the database. If you don't have the ability to do so and can't convince those responsible to do so, then the following solution should also work:

SELECT CAST(SUBSTR(F00001,1,20) AS CHAR(20) CCSID(37))
FROM QS36F."FX.PARA"
WHERE K00001 LIKE '16FFC%%%%%'
FETCH FIRST 5 ROWS ONLY

Replace the CCSID with whichever one you need. The CCSID definitions can be found here: https://www-01.ibm.com/software/globalization/ccsid/ccsid_registered.html

DB2 Query Functions Not Working With Parameters

As @Mustaccio pointed out in the comments, casting as a CHAR is the equivalent to CHAR(1). I corrected this to declare an actual length and the query is working.

DB2 - How to run an ad hoc select query with a parameter in IBM System i Access for Windows GUI Tool

I ran across this post while searching for the same question. My coworker provided the answer. It is indeed possible to declare variables in an ad hoc SQL statement in Navigator. This is how it is done:

CREATE OR REPLACE VARIABLE variableName VARCHAR(50);
SET variableName = 'blah';
SELECT * FROM table WHERE column = variableName;
DROP VARIABLE variableName;

If you don't drop the variable name it will hang around until who knows when...

Use query parameters with an ODBC connection to an iSeries AS400 database in .NET?

Apparently you cannot parametrize FETCH FIRST ROWS as discussed under Paramertize Fetch First n Rows Only in DB2 you can either put it direct in the sql string or use a different strategy.

don't know how to change a comment to answer

call program in interactive sql as400

STRSQL supports the SQL CALL statement.

The best option is to define the program as External SQL Stored procedure

--note 
----- numeric-->zoned decimal
----- decimal-->packed decimal
CREATE PROCEDURE MYLIB.MYPROGRAM_SP 
  (IN number numeric(5,0))  
LANGUAGE RPGLE
EXTERNAL NAME 'MYLIB/MYPROGRAM'
PARAMETER STYLE GENERAL;

Then you can

CALL MYLIB.MYPROGRAM_SP(12345)

Technically, every *PGM object on the IBM i is a stored procedure. You can call it without explicitly defining it as shown above. But assumptions are made about the parms in that case. It's much better to provide the DB with the interface definition.

Note that STRSQL is a 20 year old tool, it has various limitations including not supporting OUT or INOUT parameters of stored procedures.

A much better choice is to use the Run SQL Scripts component of IBM's Access Client Solutions (ACS)

Using OLEDB parameters in .NET when connecting to an AS400/DB2

You're right, same question as AS400 SQL query with Parameter, which contains the solution.

---

Related Topics