Unanswered: quote problem with string/varchar in Dynamic SQL
I´m new using DB2 and I´m facing some problems and have decided to ask for help.
I´ve been reading about using dynamic SQL to develop stored procedures so that I could give it table/column/etc in its call. I managed to get some working, but in a little more complex query I´ve got stucked and I´m thinking it is due to the quotes.
Please take a look:
CREATE OR REPLACE PROCEDURE TEST_DYNAMIC_SELECT(
IN PROC_LANGUAGE CHAR(5),
IN PROC_CHANNEL DECIMAL(3),
IN PROC_PROJECT VARCHAR(50),
IN PROC_CONDICTION VARCHAR(150))
RESULT SETS 1
DECLARE SEL_STMT VARCHAR(500);
DECLARE C1 CURSOR WITH RETURN TO CALLER FOR S1;
SET SEL_STMT = 'SELECT A. TEXT ,A. RTEXTOID ,A. HALT ,A. CPROJ
FROM TABLE1 A, TABLE2 B WHERE A. CDSEN = B. CDSEN
AND A. CHANNEL = B. CHANNEL ' || PROC_PROJECT || ' AND B. LANGUAGE = ' || PROC_LANGUAGE || '
AND B. CHANNEL = ' || PROC_CHANNEL || ' ' || PROC_CONDICTION || ' ORDER BY A. TEXT';
PREPARE S1 FROM SEL_STMT;
I have 4 parameters:
PROC_LANGUAGE = static (ex pt_PT)
PROC_CHANNEL = static (ex 2)
PROC_PROJECT = dynamic (could be any project the users is trying to get data)
PROC_CONDICTION = dynamic (if a field from the screen is empty it appends one more AND in the end)
When I try to call it:
CALL TEST_DYNAMIC_SELECT('pt_PT',1,'AND A. CPROJ = 22','') (note that I wrote the first dynamic param)
The operating-system is a Windows XP + SP3, DB2 I almost sure it is an Express 9.7.5, but I´m acessing it remotely using DBVisualizer.
For now I´calling it from DBVisualizer console but when I get it working properly I will have to call it inside a Java program.
Triple single quotes is the way to go? I´m a system analyst, who worked with Java not long ago but I´ve never had experience with databases other than CRUD inside applications, or calling stored procedures someone else had done.
Well let´s keep thins going please, I still need help...I tried what was said but still get the error:
CALL TEST_DYNAMIC_SELECT('''pt_PT''',1,'AND A. CPROJ = 22','')
[Error Code: -433, SQL State: 22001] DB2 SQL Error: SQLCODE=-433, SQLSTATE=22001, SQLERRMC='pt_PT', DRIVER=3.50.152
Now the quotes can be seen in the log and I noticed the error code now is -433 (Error: VALUE IS TOO LONG), besides (pt_PT) should fit a char(5) lenght I changed it to VARCHAR(10) just to check it.
As it started to send error -206 in my 3rd param I tried to put in the same way I did with the first and it turned into
DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=AND A. CHANNEL = B. CHANNEL 'AND ;CDSEN