SQL PreparedStatement

Thread view:

Christian - 01 Dec 2007 15:35 GMT

Hello

I have some problem with a prepared statement.. on a HSQLDB

String s =
"IF NOT EXISTS (SELECT 1 FROM downloadqueue WHERE tthroot = ? ) THEN "
+ "INSERT INTO downloadqueue (tthroot, date, path, priority, size)"
+ " VALUES ( ? , ? , ? , ? , ? ) "
+" ELSE "
+ "UPDATE downloadqueue SET date = ? , path = ? , priority = ? "
+ ", size = ?" + " WHERE tthroot = ? "
+ "END IF";

PreparedStatement addDQE = c.prepareStatement(s);

addDQE.setString(1, tth.toString()); //Where FAILS HERE!

addDQE.setString(2, tth.toString()); //Insert
addDQE.setLong(3, added.getTime());
addDQE.setString(4, target.getAbsolutePath());
addDQE.setInt(5, priority);
addDQE.setLong(6, size);
...

the setString fails with exception:
Invalid argument in JDBC call: parameter index out of range: 1

Why the hell not?
What can I do?

Reply to this Message

Lew - 01 Dec 2007 16:12 GMT

> Hello
>
[quoted text clipped - 25 lines]
> Why the hell not?
> What can I do?

What is the DDL for the table "downloadqueue"?

Signature

Lew
This post contained one request for information.

Reply to this Message

Christian - 01 Dec 2007 16:38 GMT

Lew schrieb:

>> Hello
>>
[quoted text clipped - 27 lines]
>
> What is the DDL for the table "downloadqueue"?

Here is the Create Statement for the downloadqueue table

"CREATE CACHED TABLE downloadqueue ("
+ "tthroot CHARACTER("+HashValue.serializedDigestLength+") PRIMARY KEY,"
+ "date BIGINT, "
+ "path VARCHAR, "
+ "priority INTEGER, "
+ "size BIGINT"
+ " )"

Reply to this Message

Lew - 01 Dec 2007 16:52 GMT

>> I have some problem with a prepared statement.. on a HSQLDB
>>
[quoted text clipped - 6 lines]
>> + ", size = ?" + " WHERE tthroot = ? "
>> + "END IF";

I don't know HSQLDB, but in just about all other RDBMSes you can't use "IF ...
THEN ... ELSE ... ENDIF" in a regular SQL query. So the PreparedStatement
perhaps isn't capable of receiving parameters, not being a valid SQL command
in the first place.

Signature

Lew

Reply to this Message

Christian - 01 Dec 2007 21:29 GMT

Lew schrieb:

>>> I have some problem with a prepared statement.. on a HSQLDB
>>>
[quoted text clipped - 11 lines]
> PreparedStatement perhaps isn't capable of receiving parameters, not
> being a valid SQL command in the first place.

And that was the solution ... the Error message just gave me the wrong
hint to look for my mistake.
Even if I would have liked to do this with one command. it is now split
in two.

Christian

Reply to this Message

H.L - 01 Dec 2007 16:16 GMT

> Hello
>
[quoted text clipped - 25 lines]
> Why the hell not?
> What can I do?

First of all, we don't know what the tth object is, either statically or
dynamically. The toString method might yield a bad value. You did not
indicate that to us.

Your SQL statement is really extremely complex. If the error can't be
solved, I would break it down and do it one part at a time. Use your
program to branch based on the results of each query. You will have to
add some code, but you may save some time in the end. You don't even
have to use prepared statements.

You will not get more answers by using those kind of words here.

Reply to this Message

Lew - 01 Dec 2007 16:19 GMT

Christian wrote:

>> Why the hell not?
>> What can I do?

> You will not get more answers by using those kind of words here.

I don't think they'll get fewer, either.

Signature

Lew

Reply to this Message

Christian - 01 Dec 2007 16:47 GMT

H.L schrieb:

>> Hello
>>
[quoted text clipped - 29 lines]
> dynamically. The toString method might yield a bad value. You did not
> indicate that to us.

tth object is a hashvalue with lentgth 192 bits.
toString() returns a base-32 representation of this hashvalue.

All values are checked.. they contain normal values.

> Your SQL statement is really extremely complex. If the error can't be
> solved, I would break it down and do it one part at a time. Use your
> program to branch based on the results of each query. You will have to
> add some code, but you may save some time in the end. You don't even
> have to use prepared statements.

I thought of the prepared statement as easiest method for escaping SQL
syntax in my strings .. that was the onlyth reason for choosing it.

Also I used such a complex statement because my reasoning was that one
statement would be faster that waiting for the result of a statement and
then executing the next one. May be bad reasoning. Still it has the
benefit of being atomic to the program.

> You will not get more answers by using those kind of words here.

I will try to swear less. Please believe me that I meant not to harm
anyone with my harsh words expressing the frustration resulting of my
own incompetence.

Reply to this Message

Lew - 01 Dec 2007 16:57 GMT

> Also I used such a complex statement because my reasoning was that one
> statement would be faster that waiting for the result of a statement and
> then executing the next one. May be bad reasoning. Still it has the
> benefit of being atomic to the program.

It may have the detriment of not being a legitimate SQL statement.

>> You will not get more answers by using those kind of words here.
>
> I will try to swear less. Please believe me that I meant not to harm
> anyone with my harsh words expressing the frustration resulting of my
> own incompetence.

I wouldn't get all nervous because some guy can't stand the hell of a
programmer's frustration. I think most of us get it, and if you have to spout
off a little, you go ahead and get those hellacious feelings out of your
system. We've all been little hellions our own selves, I think, but we're not
all about to drink hellsbane potions over it.

Your language was perfectly fine and no one should have given you trouble over
it. Certainly it was nothing to raise hell over.

Signature

Lew

Reply to this Message

H.L - 01 Dec 2007 17:21 GMT

> I wouldn't get all nervous because some guy can't stand the hell of a
> programmer's frustration. I think most of us get it, and if you have to spout
[quoted text clipped - 7 lines]
> --
> Lew

I didn't mean to give him trouble about his comment. Nor did I "raise
hell". I merely pointed out that it is a public newsgroup with civil
language. Many other otherwise respectable newsgroups have extreme
amounts of obscene language. I think that your comment about me is way
off line. Note that I wrote one short sentence about this after having
spent two full paragraphs trying to help with the original problem. My
comment was perfectly legit.

Reply to this Message

Lew - 01 Dec 2007 19:00 GMT

> I didn't mean to give him trouble about his comment. Nor did I "raise
> hell". I merely pointed out that it is a public newsgroup with civil
[quoted text clipped - 3 lines]
> spent two full paragraphs trying to help with the original problem. My
> comment was perfectly legit.

I agree completely, your comment was perfectly legit. I recall making no
comment about you. I spoke only generally, and certainly did not want to
point any arrows at any one person. I just wanted the OP to feel comfortable
about making their own comment, also one that was perfectly legit.

If you choose to take offense, please note that it is your choice, and that I
offer none to you.

Signature

Lew

Reply to this Message

SQL PreparedStatement

Free Magazines