Output SQLPROC variable from DEBUG or similar

[RDKells]

Hello,

I have an SQL proc that builds a SELECT statement in a VARCHAR variable and then executes it via a cursor.

The SQL being built has a syntax error and I am having a hard time finding it, I really need the SQL it is running to find it but I don't know how I can get a hold of it?

I've run the proc in debug and I can see the statement but I can't work with it and I can't see any option to output an EVAL to a print or anything.

I have tried creating a variable of the same type and just before the SQL variable is used in the PREPARE statement I run a SET to set my variable with the SQL but for some reason the variable isn't set - having tested it, despite it saying in my job log "variable set successfully", the variable seems to only be set if the SQLPROC finishes normally.

Any ideas?

Cheers,
Ryan

[RDKells]

Ok, so I just commented out the cursor related commands (or whatever the terminology is) so it finishes normally.

However; I have found that if I call it through STRSQL it updates my variables but if I use Run SQL Scripts it doesn't, weird.

[Scott M]

Maybe it has to do with commitment control and the SET operation is being rolled back on abnormal end. Those settings can be different in STRSQL and Run SQL scripts and can be changed in each. I don't know for sure that SET can be rolled back or committed but that might explain what you are seeing.

[B.Hauser]

Did you check for SQL errors in your program? I assume you are coding in pure SQL, so you should have at least CONTINUE Handlers to trap specific or any SQL error. Within the handler it is possible to retrieve the Error Message and other information with the GET DIAGNOSTIC statement.

You may have a look at the following article:
Guru: Logging SQL Errors And Warnings

https://www.itjungle.com/2017/10/30/guru-logging-sql-errors-warnings/

I assume you try to update a table that is not registered in a journal and you created your stored procedure with the default for commitment control (*CHG).

Birgitta

[RDKells]

It is pure SQL and we don't use commitment, the SQL proc has SET COMMIT = *NONE. The procedure isn't changing anything it's just a select statement to return the status of a home shopping order - the files its using aren't journaled.

Thanks for the information regarding CONTINUE/GET DIAGNOSTIC, I'm OK with like interactive SQL (if that's what you call it) but not with procedures, mainly due to lack of exposure as my shop avoids them like the plague because our change management software regularly has issues with them, they are therefore considered high risk.

Example being this procedure; its around 20,000 chars/800 lines, which I'm told this is small by senior devs despite being the largest we have and it crashes our change management software.

Cheers,
Ryan