By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,609 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,609 IT Pros & Developers. It's quick & easy.

Problem with PreparedStatement and DB2-Function...

P: n/a
In my Java-application I create an PreparedStatement like this:
PreparedStatement v_stmt = dbcon.prepareStatement("SELECT * FROM
mytable WHERE UPPER(userID) LIKE UPPER(?)");
v_stmt.setString(1, "me");

This Statement crashes with:
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -418, SQLSTATE:
42610, SQLERRMC: null

But if I try this:
PreparedStatement v_stmt = dbcon.prepareStatement("SELECT * FROM
mytable WHERE userID LIKE ?");
v_stmt.setString(1, "me");
it works.

Can anybody explain, why the first statement crashes or how can I
create a "case-insensitive-preparedStatement"?

Thanx

Markus

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
In article <11*********************@g49g2000cwa.googlegroups. com>,
Th*********@Freenet.de says...

Can anybody explain, why the first statement crashes or how can I
create a "case-insensitive-preparedStatement"?


C:\Temp>db2 ? sql0418
SQL0418N A statement contains a use of a parameter marker that
is not valid.

Explanation:
<snipped>

User Response:

Correct the syntax of the statement. If untyped parameter
markers are not allowed, use the CAST specification to give the
parameter marker a data type.

Nov 12 '05 #2

P: n/a
Markus wrote:
In my Java-application I create an PreparedStatement like this:
PreparedStatement v_stmt = dbcon.prepareStatement("SELECT * FROM
mytable WHERE UPPER(userID) LIKE UPPER(?)");
v_stmt.setString(1, "me");

This Statement crashes with:
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -418, SQLSTATE:
42610, SQLERRMC: null

But if I try this:
PreparedStatement v_stmt = dbcon.prepareStatement("SELECT * FROM
mytable WHERE userID LIKE ?");
v_stmt.setString(1, "me");
it works.

Can anybody explain, why the first statement crashes or how can I
create a "case-insensitive-preparedStatement"?


The problem is that DB2 cannot resolve the UPPER function because it does
not know the data type of the value provided by the parametere marker. You
might want to try to fix this issue:

PreparedStatement v_stmt = dbcon.prepareStatement("SELECT * FROM mytable " +
"WHERE UPPER(userID) LIKE UPPER(CAST(? AS VARCHAR(32000)))");

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #3

P: n/a
Ich vermute mal, dass ich auch in deutsch schreiben kann. :-)

Mein PreparedStatement funktioniert mit dem Cast wunderbar.
Danke erstmal dafür.

Jetzt aber gleich noch eine neue Frage:
Kann ich mir per SQL irgendwie die Signatur eine StoredProcedure
anzeigen lassen, so dass ich mir nicht immer mein File raussuchen muss
um nachzuschauen, welche Parameter die PS hatte?
Bzw. ist es auch möglich mir den gesamten "Quelltext" der
StoredProcedure mit einem SELECT anzeigen zu lassen.
Also sowas wie
SELECT src FROM ??? WHERE procname = 'meineProzedur'

Danke schonmal

Markus

Nov 12 '05 #4

P: n/a
Markus wrote:
Ich vermute mal, dass ich auch in deutsch schreiben kann. :-) Yes, you can, but keep in mind that English carries you a lot further
and is the accepted lingua franka.
Kann ich mir per SQL irgendwie die Signatur eine StoredProcedure
anzeigen lassen, so dass ich mir nicht immer mein File raussuchen muss
um nachzuschauen, welche Parameter die PS hatte? That's all in SYSCAT.ROUTINEPARMS:
http://publib.boulder.ibm.com/infoce...n/r0001044.htm
PARMNAME, ROWTYPE and ORDINAL will be a good start.
Bzw. ist es auch möglich mir den gesamten "Quelltext" der
StoredProcedure mit einem SELECT anzeigen zu lassen.
Also sowas wie
SELECT src FROM ??? WHERE procname = 'meineProzedur'

SELECT text FROM SYSCAT.ROUTINES
WHERE routineschema='SRIELAU' AND routinename = 'PROC'

Note that CLP will cut of the CLOB after a couple of kb.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #5

P: n/a
Thank, this is a good tip - I'll try it at work.
Just one more question:
Is it possible to cast a BLOB back to a VARCHAR?
On my INSERTs the cast FROM VARCHAR to BLOB works fin but this
statement doesn't work:
SELECT CAST(data AS VARCHAR(32000)) FROM mytable

Any suggestions?

Markus

Nov 12 '05 #6

P: n/a
Markus wrote:
Thank, this is a good tip - I'll try it at work.
Just one more question:
Is it possible to cast a BLOB back to a VARCHAR?
On my INSERTs the cast FROM VARCHAR to BLOB works fin but this
statement doesn't work:
SELECT CAST(data AS VARCHAR(32000)) FROM mytable

Any suggestions?

Markus

Once a BLOB always a BLOB.
We introduced casting from VARCHAR FOR BIT DATA to VARCHAR in V8.
Now, what you can do of course it to write an external UDF.
What you do in C is your business ;-)
In my current proect I convert VARCHAR FOR BIT DATA into FLOATs ;-)
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.