PreparedStatement Troubles !

[Arcadius A.]

Hello!
I have such a code in my JSP code, all the parameters are strings:
<>
String[][] keyValue={
{"firstname",firstname}, {"lastname", lastname},
{"gender", gender}, {"email", email},
{"location",location},{"website",website},
{"message",book.cleanHTML(message)},{"host",host},
{"digest",MD5.getMD5(concat)},{"userdate",userdate}
};

db.addEntry(keyValue);
</>

The method addEntry() looks like this:

<>
public int addEntry( String[][] keyValue ) throws Exception {
...
...
int len= keyValue.length;
PreparedStatement ps =
conn.prepareStatement("INSERT INTO gbuser(?,?,?,?,?,?,?,?,?,?)
VALUES(?,?,?,?,?,?,?,?,?,?)");
for(int i=0; i<len; i++) {
ps.setString(i+1, keyValue[i][0]);
ps.setString(i+1+len, keyValue[i][1]);
}

int i = ps.executeUpdate(); // This is line# 90 where the error happens
conn.close();
return i;
}
}
</>

But I get the following error:
<>
An error occured in the bean. Error Message is: java.sql.SQLException:
ERROR: parser: parse error at or near "'" Stack Trace is :
java.sql.SQLException: ERROR: parser: parse error at or near "'"

at org.postgresql.Connection.ExecSQL(Connection.java:533)
at org.postgresql.jdbc2.Statement.execute(Statement.java:294)
at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:78)
at
org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:
122)
at net.arcadnet.aajguestbook.DBBean.addEntry(DBBean.java:90)
at
_0002fexec_0002ejspexec_jsp_29._jspService(_0002fexec_0002ejspexec_jsp_29.ja
va:124)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
....
....
</>

Help please...
Thanks.

Arcadius.

[Avi Abrami]

Hi Arcadius,
As far as I know, you can't use question marks ("?") as parameters
for the column names in a "PreparedStatement" -- only for the
values. So I suggest maybe building up that part of the INSERT
statement first. Example:

StringBuffer sqlStr = new StringBuffer("INSERT INTO gbuser(");
for (int i = 0; i < len; i++) {
sqlStr.append(keyValue[i][0]);
sqlStr.append(",");
}
sqlStr.append(") VALUES (?,?,?,?,?,?,?,?,?,?)");
PreparedStatement ps = conn.prepareStatement(sqlStr.toString());
for (int i = 0; i < len; i++) {

ps.setString(i+1+len, keyValue[i][1]);
}

Note, the above is only partial. I'm assuming you can figure out
how to integrate it into your code (or that it will give you an
idea about how to rewrite your code).

Good Luck,
Avi.

[Lee Fesperman]

Avi Abrami wrote:
>
> > ...
> > ...
> > int len= keyValue.length;
> > PreparedStatement ps =
> > conn.prepareStatement("INSERT INTO gbuser(?,?,?,?,?,?,?,?,?,?)
> > VALUES(?,?,?,?,?,?,?,?,?,?)");
>

> As far as I know, you can't use question marks ("?") as parameters
> for the column names in a "PreparedStatement" -- only for the
> values. So I suggest maybe building up that part of the INSERT
> statement first. Example:

That's correct. ? parameters can only be used for scalar values in DML and not for
names. ? parameters can't be used in DDL at all.

--
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

[Arcadius A.]

Hello!

> That's correct. ? parameters can only be used for scalar values in DML and
not for
> names. ? parameters can't be used in DDL at all.
>

I thank both of you for the help!!! :-)

Have a nice weekend! :-)

Arcadius