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

Question

If I understand this link correctly, I should be able to pass query parameter values to my ODBC command and have it execute successfully. It doesn't, so here's my problem code:

OdbcConnection myConnection = new OdbcConnection("DSN=myODBCConnection");

myConnection.Open();

OdbcCommand myCommand = myConnection.CreateCommand();

myCommand.CommandText = "SELECT * FROM MyTable FETCH FIRST ? ROWS ONLY";
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("P1", 5);

OdbcDataReader myDataReader;

// Fails here! It doesn't recognize P1 as a parameter to pass in for ?.
myDataReader = myCommand.ExecuteReader();

The code should select the first 5 rows from MyTable. Instead, it throws this error:

System.Data.Odbc.OdbcException: ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ? was not valid. Valid tokens: ROW ROWS.

Thanks for anyone who'd like to help! And yes, if I execute a parameterless query, it works fine.

Apparently you cannot parametrize FETCH FIRST ROWS as discussed under http://stackoverflow.com/questions/3751884/paramertize-fetch-first-n-rows-only-in-db2 you can either put it direct in the sql string or use a different strategy — Boklucius, Mar 05 '14 at 22:48
It isn't an answer to my question, but since I'm executing string SQL statements anyway, I could probably just pass my criteria as string values using String.Format, as demonstrated here: [link](http://stackoverflow.com/questions/22210427/batch-update-from-a-linq-query). — David Alan Condit, Mar 05 '14 at 22:50
Boklucius errors like this make me want to throw in the towel and join an Amish community. You're right - I tried parameterizing an actual field (which is what I really wanted to do, I only parameterized FETCH FIRST N ROWS because I thought it'd be an easy test) and the ? parameter marker worked. Thanks for your help! If you want to change your comment to an answer I'll mark it correct. — David Alan Condit, Mar 05 '14 at 22:57

score 4 · Accepted Answer · edited May 23 '17 at 12:03

4

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

edited May 23 '17 at 12:03

Community

1
1

answered Mar 05 '14 at 23:04

Boklucius

1,896
17
19

2

FETCH FIRST ROWS is not an absolute directive to the database engine. It is a suggestion, a hint to tell the optimiser that you want to see results as fast as possible as opposed to minimising the total run time. – Buck Calabro Mar 06 '14 at 02:35

score 0 · Answer 2 · answered Mar 18 '14 at 04:24

AFAIK, parameter markers are allowed where the resulting data type can be determined, e.g., where a column-definition is available. See Table 1. under the PREPARE statement for a list of allowed marker locations and how to specify markers at those locations.

Since there is no "column" to which the value must be compatible, there has been no development done to give this (a 'fetch first' marker) any meaning to DB2. Also, since OLAP specifications can be used to provide ROW_NUMBER(), there probably isn't much demand to put development effort into a probably less useful element of queries. (As noted earlier, the FETCH FIRST clause is generally intended more for performance rather than strict results, though it does actually limit number of rows available in the result set.)

If FETCH FIRST is required for some reason, you'll need to use a dynamic statement.

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

2 Answers2