SQL PreparedStatement

[Christian]

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?

 

[Lew]

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?

What is the DDL for the table "downloadqueue"?

 

[Lew]

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

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

 

[H.L]

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?

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.

 

[Christian]

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"
+ " )"

 

[Christian]

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.

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.

 

[Lew]

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.

 

[Lew]

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.

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.

 

[H.L]

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.

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.

 

[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.

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.

 

[Christian]

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.

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