Using host variables with embedded SQL isn't working for me
Hi
I'm using an embedded SQL statement with a a cursor-based Embedded SQL query that uses host variables in the where clause, however, what I'm doing doesn't seem to work. Can anyone help?
The code I'm using is
set tMessageName = "AssessmentsMessage"
set tIdentifier = "SectionCode"
set ID = 0
&sql(
Declare IDs Cursor For
select %ID
INTO :ID
from GMECC_DocmanConnect_Tables.ParisConnecMessagetSettings
Where MessageName = :tMessageName
and Identifier = :tIdentifier
)
&sql(Open IDs) For {
&Sql(fetch IDs) If SQLCODE Quit // SQLCODE = 100, now rows found
w !, ID
If $$$ISERR(tSC) Quit
} &Sql(Close IDs)The table being queried contains 9 rows, and with the two parameters provided, it should return only 1 row, and instead returns nothing.
What am I missing?
Comments
If you try this what does it return?
&sql(select count(*) INTO :tCount from GMECC_DocmanConnect_Tables.ParisConnecMessagetSettings Where MessageName = :tMessageName and Identifier = :tIdentifier) write SQLCODE, ":", tCount,!
When I do that, it returns 0: 0.
If I don't have a where clause on the statement, it returns the correct number of rows 9, outputs 0:9.
If I include the first part of the where clause (MessageName = :tMessageName) and omit the second (Identifier= :tIdentifier) it returns 7, which is a value I'd expect. Having both returns 0, and I have checked the spelling of the second variable which is correct.
What happens if you omit the first part of the where clause?
How about this? Do the values returned in tMsgNmList and tIdentList match the values you're searching for?
&sql(select LIST(MessageName) INTO :tMsgNmList, LIST(Identifier) INTO :tIdentList from GMECC_DocmanConnect_Tables.ParisConnecMessagetSettings) write SQLCODE, ":", tMsgNmList,":",tIdentList,!
I messed up the syntax:
&sql(select LIST(MessageName), LIST(Identifier) INTO :tMsgNmList, :tIdentList from GMECC_DocmanConnect_Tables.ParisConnecMessagetSettings)
...and, it's a shot in the dark, but how about putting parenthesis around each condition?
(MessageName = :tMessageName) and (Identifier = :tIdentifier)
I solved the problem: user error! One of the variable values was misspelt in the database. Thanks for taking the time to answer!
if this is inside a method add [ProcedureBlock = 0] to the method
I have added that, but it had no effect. Thanks for taking the time to reply though.
maybe try adding %NOINDEX to your where clauses in case you have an index issue.
I have added that, but it had no effect. Thanks for taking the time to reply though.
After further experimentation, I have found that entirely omitting the where clause causes only 1 value to outputted in the second loop that examines the cursor.
When does "tSC" get defined? This line might be the problem, causing the for loop to stop:
If $$$ISERR(tSC) Quit
(Comment out that line and watch the results appear :-))
OMG! I can't believe I didn't notice that. Thank you for this!
You're welcome Andy