Re: JDBCR4 program with a null field in the WHERE statement

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

You can't specify *NULL like that -- it'll think that you want to set a 
string containing the text '*NULL' rather than setting the value to a 
database null.

There's an additional (optional) parameter to setString that can be set 
to *ON for null or *OFF for not-null, however.

So you'd do it like this:

      JDBC_SetString(prepstm: 1: '': *ON);

As for the SELECT statement, it has to follow the syntax rules of the 
database engine.  I'm guessing by the "dbo" prefix that you're working 
with Microsoft SQL Server.  If so, you'll need to determine if they 
allow you to do "WHERE (field = null)" the way you've done it or not... 
 I personally do not have enough experience with MS to know.




On 6/26/2013 3:20 PM, Kim Massar wrote:
> I am using Scott's JDBCR programs to get data from our SQL server into an RPG program.  I have used this successfully before now I have a problem with a null field in my WHERE statement.  Is it possible to compare for nulls?  Is it possible to check for nulls in my process_flag field below?  I have tried '*Null' and 'is_null'
>
>
>
> prepstm = JDBC_prepStmt(conn : 'SELECT * ' +
>                     'FROM dbo.Error_File ' +
>                     ' WHERE (dbo.Error_File.Process_flag = ?)'   );
>
>
>           If (prepstm = *NULL);
>               jdbc_close(conn);
>               return;
>           EndIf;
>
>           // Set SQL parameter values
>           JDBC_SetString(prepstm:1:'*NULL');
>
>
>
>
> Email Confidentiality Notice: The information contained in this transmission is confidential, proprietary or privileged and may be subject to protection under the law, including the Health Insurance Portability and Accountability Act (HIPAA). The message is intended for the sole use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any use, distribution, copying or printing of this message is strictly prohibited and may subject you to criminal or civil penalties. If you received this transmission in error, please contact the sender immediately by replying to this email and then delete the material from any computer.