JDBC parameter binding not working for PL/PGSQL block JDBC3 Version 9.0-801
От | Brady S Edwards |
---|---|
Тема | JDBC parameter binding not working for PL/PGSQL block JDBC3 Version 9.0-801 |
Дата | |
Msg-id | BANLkTi=vNEroPFa159HUw9DcxWuj=yHSwA@mail.gmail.com обсуждение исходный текст |
Ответы |
JDBC parameter binding not working for PL/PGSQL block JDBC3 Version 9.0-801
Re: JDBC parameter binding not working for PL/PGSQL block JDBC3 Version 9.0-801 |
Список | pgsql-jdbc |
Database postgresql90-server-9.0.2-1PGDG.rhel4
CentOS 4.7
JDBC3 Version 9.0-901.
I'm working on a project converting a number of applications from Oracle to Postgres and am running into a problem with the Postgres JDBC driver or the database.
When trying to setString(1,?) where the ? is in a pl/pgsql block I get an error.
I included the error and portion of the code.
> java WhatTheF
Trying to connect
Looks like connections succeeded
Bound Vars = 1
Bound Vars = 0
org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:53)
at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:118)
at org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2184)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1303)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1289)
at WhatTheF.main(WhatTheF.java:37)
try {
System.out.println("Trying to connect");
Class.forName ("org.postgresql.Driver");
Properties props = new Properties();
props.setProperty("user", PG_USER);
props.setProperty("password", PG_PASS);
Connection pgConn = DriverManager.getConnection(PG_CONN_PROP, props);
System.out.println("Looks like connections succeeded\n");
PreparedStatement pStmt = pgConn.prepareStatement(
"INSERT INTO soo (zoo) values (?)");
ParameterMetaData pmd = pStmt.getParameterMetaData();
System.out.println("Bound Vars = " + pmd.getParameterCount());
pStmt.setString(1, "Monkey");
pStmt.executeUpdate();
pStmt.close();
pStmt = pgConn.prepareStatement(
" DO LANGUAGE plpgsql "
+ " $$\n"
+ " DECLARE\n"
+ " booger varchar(20);\n"
+ " BEGIN\n"
+ " booger := $?OTTER$?BOOG$?;\n"
+ " INSERT INTO soo (zoo) values (booger);\n"
+ " END $$;");
pmd = pStmt.getParameterMetaData();
System.out.println("Bound Vars = " + pmd.getParameterCount());
LINE 37 -> pStmt.setString(1, "Barf");
pStmt.executeUpdate();
pStmt.close();
pgConn.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
--
Brady Edwards
(720) 684-2984
Seagate Technology
CentOS 4.7
JDBC3 Version 9.0-901.
I'm working on a project converting a number of applications from Oracle to Postgres and am running into a problem with the Postgres JDBC driver or the database.
When trying to setString(1,?) where the ? is in a pl/pgsql block I get an error.
I included the error and portion of the code.
> java WhatTheF
Trying to connect
Looks like connections succeeded
Bound Vars = 1
Bound Vars = 0
org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:53)
at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:118)
at org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2184)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1303)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1289)
at WhatTheF.main(WhatTheF.java:37)
try {
System.out.println("Trying to connect");
Class.forName ("org.postgresql.Driver");
Properties props = new Properties();
props.setProperty("user", PG_USER);
props.setProperty("password", PG_PASS);
Connection pgConn = DriverManager.getConnection(PG_CONN_PROP, props);
System.out.println("Looks like connections succeeded\n");
PreparedStatement pStmt = pgConn.prepareStatement(
"INSERT INTO soo (zoo) values (?)");
ParameterMetaData pmd = pStmt.getParameterMetaData();
System.out.println("Bound Vars = " + pmd.getParameterCount());
pStmt.setString(1, "Monkey");
pStmt.executeUpdate();
pStmt.close();
pStmt = pgConn.prepareStatement(
" DO LANGUAGE plpgsql "
+ " $$\n"
+ " DECLARE\n"
+ " booger varchar(20);\n"
+ " BEGIN\n"
+ " booger := $?OTTER$?BOOG$?;\n"
+ " INSERT INTO soo (zoo) values (booger);\n"
+ " END $$;");
pmd = pStmt.getParameterMetaData();
System.out.println("Bound Vars = " + pmd.getParameterCount());
LINE 37 -> pStmt.setString(1, "Barf");
pStmt.executeUpdate();
pStmt.close();
pgConn.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
--
Brady Edwards
(720) 684-2984
Seagate Technology
В списке pgsql-jdbc по дате отправления: