Strange Cobol ODBC Problem 'Must Declare scaler variables'

[AndrewDe]

The Following SQL works SQL Server 2005 executed from Fujitsu COBOL via an ODBC Driver. ( EXEC Statements excluded)

SELECT FIELDA, FIELDB FROM CTRY_TAB
WHERE FIELDC = 'A' AND FIELDD = :XCTRY

XCTRY IS A DECLARED SQL VARIABLE.

The following does not work with SQL Server but does with MySQL.

SELECT FIELDA, FIELDB FROM CTRY_TAB
WHERE FIELDD = :XCTRY AND FIELDC = 'A'

The error message is 00137 must declare scalar variable '@P1AND'

I believe that the variable is being concatenated the the AND ? I guess it is simple if you know what option to set but ut baffles me !!!

 

[Glenn9999]

Not so strange really. Most SQL/COBOL interfaces insist on what are referred to as scalar variables, meaning they have to be directly relatable variable definitions as opposed to a group variable or a redefinition.

01 XCTRY PIC X(20).

Is an acceptable scalar definition of xctry, while.

01 XCTRY.
05 CODE1 PIC 9(2).
05 CODE2 PIC X(10).
05 CODE3 PIC 9(8).

OR

01 XCTRY-DATA.
04 XCTRY-CODE.
08 CODE1 PIC 9(2).
08 CODE2 PIC X(10).
08 CODE3 PIC 9(8).
04 XCTRY REDEFINES XCTRY-CODE PIC X(20).

would be unacceptable scalar definitions of xctry.

Evidently MySQL is not being standard in this regard.

 

[AndrewDe]

Thanks GLen but it is defined correctly. I would love it to be an easy fix like that. Do you have any other ideas

 

[MarcLodge]

Andrew,
I confess first up to knowing very little about fujitsu cobol and it's relationship with SQL server through ODBC, but.... looking at the problem it appears to me that something is not recognising the end of the :XCTRY variable and the beginning of the next part of the sql.

If I had to debug this I would try:
Making sure that I had a space between :XCTRY and 'AND'
Putting an end of line between :XCTRY and 'AND'
Placing the :XCTRY variable in quotes

As I said at the top, I don't know how the db2 language interface reacts with fujitsu/odbc, but it may be that messing about with it may provide you with the answer.

Good luck, hope this helps in some way, and please let us know how you get on.

Marc