Thread: create tables and prepared statements with params

create tables and prepared statements with params

From

"Enrique Rodríguez Lasterra"

Date:

13 September 2006, 17:10:30

 get an error when i create a table using preparedstatement with some
parameters "?".

Is this a non standard function?

With JDBC 7.4 it runs prefectly but with 8.x no. With 8.x if i don't
use params the table is created without errors

Here is all the info.

Regards, Enrique

Code

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

/**
* DB
*
* @version 1.0
* @since 13-sep-2006
*/
public class DB {
   public static void main(String[] args) {
       Connection connection = null;
       try {
           Class.forName("org.postgresql.Driver");
           connection =
DriverManager.getConnection("jdbc[tongue]ostgresql://192.168.4.221/canyamo-test",
"canyamo-test", "canyamo-test");
           PreparedStatement stmt =
connection.prepareStatement("create table orders ( id integer not
null, client character varying(15) default ? not null)");
           stmt.setObject(1, "without-name");

           stmt.executeUpdate();//stmt.execute();
       } catch (Exception e) {
           e.printStackTrace();
       }
   }
}

Exception

org.postgresql.util.PSQLException: ERROR: there is no parameter $1
       at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1527)
       at org.postgresql.core.v3.QueryExecutorImpl.processResults
(QueryExecutorImpl.java:1311)
       at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:190)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:308)
       at test.DB.main(DB.java:23)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)


Versions
JDBC: 8.1.404
Database: 8.1.4



--
_______________________________
Enrique Rodriguez Lasterra
lasterra AT javahispano DOT org
http://www.javahispano.org
Asociación sin ánimo de lucro sobre java
Spanish non profit association about java

Re: create tables and prepared statements with params

From

Mark Lewis

Date:

13 September 2006, 17:21:05

In 8.x the driver uses "real" server-side prepared statements.  In 7.4.x
the driver did string replacements before submitting statements to the
server.

There are pros and cons to server-side prepared statements.  They're
mostly a very good thing, but you're running into one of the
limitations; the driver can only put variables in places where the
server allows variables.  CREATE TABLE isn't one of those places.

Your best bet with the 8.x driver is to perform the string replacement
yourself.

[Question to the list: this should be on the FAQ.  How could I go about
submitting patches to the FAQ page?]

-- Mark Lewis

On Wed, 2006-09-13 at 19:10 +0200, Enrique Rodríguez Lasterra wrote:
>  get an error when i create a table using preparedstatement with some
> parameters "?".
>
> Is this a non standard function?
>
> With JDBC 7.4 it runs prefectly but with 8.x no. With 8.x if i don't
> use params the table is created without errors
>
> Here is all the info.
>
> Regards, Enrique
>
> Code
>
> package test;
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
>
> /**
> * DB
> *
> * @version 1.0
> * @since 13-sep-2006
> */
> public class DB {
>    public static void main(String[] args) {
>        Connection connection = null;
>        try {
>            Class.forName("org.postgresql.Driver");
>            connection =
> DriverManager.getConnection("jdbc[tongue]ostgresql://192.168.4.221/canyamo-test",
> "canyamo-test", "canyamo-test");
>            PreparedStatement stmt =
> connection.prepareStatement("create table orders ( id integer not
> null, client character varying(15) default ? not null)");
>            stmt.setObject(1, "without-name");
>
>            stmt.executeUpdate();//stmt.execute();
>        } catch (Exception e) {
>            e.printStackTrace();
>        }
>    }
> }
>
> Exception
>
> org.postgresql.util.PSQLException: ERROR: there is no parameter $1
>        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1527)
>        at org.postgresql.core.v3.QueryExecutorImpl.processResults
> (QueryExecutorImpl.java:1311)
>        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:190)
>        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
>        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
>        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:308)
>        at test.DB.main(DB.java:23)
>        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>        at sun.reflect.DelegatingMethodAccessorImpl.invoke
> (DelegatingMethodAccessorImpl.java:25)
>        at java.lang.reflect.Method.invoke(Method.java:324)
>        at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
>
>
> Versions
> JDBC: 8.1.404
> Database: 8.1.4
>
>
>

Re: create tables and prepared statements with params

From

Dave Cramer

Date:

13 September 2006, 17:38:19

On 13-Sep-06, at 1:20 PM, Mark Lewis wrote:

> In 8.x the driver uses "real" server-side prepared statements.  In
> 7.4.x
> the driver did string replacements before submitting statements to the
> server.
>
> There are pros and cons to server-side prepared statements.  They're
> mostly a very good thing, but you're running into one of the
> limitations; the driver can only put variables in places where the
> server allows variables.  CREATE TABLE isn't one of those places.
>
> Your best bet with the 8.x driver is to perform the string replacement
> yourself.
Or, you can create the connection using the old protocol see http://
jdbc.postgresql.org/documentation/head/connect.html#connection-
parameters

for protocolVersion
>
> [Question to the list: this should be on the FAQ.  How could I go
> about
> submitting patches to the FAQ page?]

The web page is part of the project, and can be downloaded via CVS,
send a patch

--dc--
>
> -- Mark Lewis
>
> On Wed, 2006-09-13 at 19:10 +0200, Enrique Rodríguez Lasterra wrote:
>>  get an error when i create a table using preparedstatement with some
>> parameters "?".
>>
>> Is this a non standard function?
>>
>> With JDBC 7.4 it runs prefectly but with 8.x no. With 8.x if i don't
>> use params the table is created without errors
>>
>> Here is all the info.
>>
>> Regards, Enrique
>>
>> Code
>>
>> package test;
>>
>> import java.sql.Connection;
>> import java.sql.DriverManager;
>> import java.sql.PreparedStatement;
>>
>> /**
>> * DB
>> *
>> * @version 1.0
>> * @since 13-sep-2006
>> */
>> public class DB {
>>    public static void main(String[] args) {
>>        Connection connection = null;
>>        try {
>>            Class.forName("org.postgresql.Driver");
>>            connection =
>> DriverManager.getConnection("jdbc[tongue]ostgresql://192.168.4.221/
>> canyamo-test",
>> "canyamo-test", "canyamo-test");
>>            PreparedStatement stmt =
>> connection.prepareStatement("create table orders ( id integer not
>> null, client character varying(15) default ? not null)");
>>            stmt.setObject(1, "without-name");
>>
>>            stmt.executeUpdate();//stmt.execute();
>>        } catch (Exception e) {
>>            e.printStackTrace();
>>        }
>>    }
>> }
>>
>> Exception
>>
>> org.postgresql.util.PSQLException: ERROR: there is no parameter $1
>>        at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse
>> (QueryExecutorImpl.java:1527)
>>        at org.postgresql.core.v3.QueryExecutorImpl.processResults
>> (QueryExecutorImpl.java:1311)
>>        at org.postgresql.core.v3.QueryExecutorImpl.execute
>> (QueryExecutorImpl.java:190)
>>        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute
>> (AbstractJdbc2Statement.java:452)
>>        at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags
>> (AbstractJdbc2Statement.java:354)
>>        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate
>> (AbstractJdbc2Statement.java:308)
>>        at test.DB.main(DB.java:23)
>>        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>        at sun.reflect.NativeMethodAccessorImpl.invoke
>> (NativeMethodAccessorImpl.java:39)
>>        at sun.reflect.DelegatingMethodAccessorImpl.invoke
>> (DelegatingMethodAccessorImpl.java:25)
>>        at java.lang.reflect.Method.invoke(Method.java:324)
>>        at com.intellij.rt.execution.application.AppMain.main
>> (AppMain.java:90)
>>
>>
>> Versions
>> JDBC: 8.1.404
>> Database: 8.1.4
>>
>>
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>