Обсуждение: JDBC parameter binding not working for PL/PGSQL block JDBC3 Version 9.0-801

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

Had a slight problem in the Java that I fixed but the error is still the same.

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:

/mnt/ae_files/users/brady/postgres> 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:35)

        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.prepareCall(
                " DO LANGUAGE plpgsql "
                + " $$\n"
                + " DECLARE\n"
                + " BEGIN\n"
                + " INSERT INTO soo (zoo) values (?);\n"
                + " END $$;");
            pmd = pStmt.getParameterMetaData();
            System.out.println("Bound Vars = " + pmd.getParameterCount());
LINE 35 ->      pStmt.setString(1, "Barf");
            pStmt.executeUpdate();
            pStmt.close();
            pgConn.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }


I'm guessing that anonymous plpgsql blocks cannot take input parameters.

--
Brady Edwards
(720) 684-2984
Seagate Technology

On 28 April 2011 07:35, Brady S Edwards <brady.s.edwards@seagate.com> wrote:

> When trying to setString(1,?) where the ? is in a pl/pgsql block I get an
> error.

? is not considered a parameter marker if it occurs inside a literal string.
The pl/pgsql code in your case is one big literal string (delimited by $$)

It's just the same case as "SELECT 'should this be a parameter?'",
which is a query that takes no parameters despite happening to contain
a ? character.

Oliver

Brady S Edwards wrote:
>         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 (?)");

This code makes me do a doubletake, and not just because of the ridiculously
wide TAB-based indentation.  Why reload the driver class over and over again?
  Only the first time does anything.

One might wish to take the Sun/Oracle advice:
"The DriverManager methods getConnection and getDrivers have been enhanced to
support the Java Standard Edition Service Provider mechanism. JDBC 4.0 Drivers
must include the file META-INF/services/java.sql.Driver. This file contains
the name of the JDBC drivers implementation of java.sql.Driver. For example,
to load the my.sql.Driver class, the META-INF/services/java.sql.Driver file
would contain the entry:

  my.sql.Driver

  [org.postgresql.Driver]

"Applications no longer need to explictly load JDBC drivers using
Class.forName(). Existing programs which currently load JDBC drivers using
Class.forName() will continue to work without modification."

You still don't need 'Class.forName()' more than once per driver if you stick
to the old way.

Personally I prefer JNDI-based approaches to loading DB drivers.  I also
usually prefer JPA to raw JDBC, but there are times when JDBC is
unquestionably the better tool.

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

So, I was just going for a simple quick illustration of the problem
that I was running into. I had a meeting and spent 5 minutes typing up
a very simple example.

The actual code uses tomcat's JNDI based connection pooling which works fine.
For the indent, my tabstop is set to 3 in my editor instead of 8, I
should have expanded the tab's I guess.
The insert statement works.
The trying to prepare an anonymous plpgsql block with a ? (in between
the $$ $$) does not work, the reason for which was helpfully pointed
out by someone else.

Your response does have anything to do with the question I was asking.

I was hoping to find out how (if it's possible) to execute something
like in postgres:
PreparedStatement pStmt = conn.prepareStatement("DECLARE oh
varchar(10); BEGIN oh := ?; END;");
pStmt.setString(1, "Blah");


On Wed, Apr 27, 2011 at 3:39 PM, Lew <noone@lewscanon.com> wrote:
> Brady S Edwards wrote:
>>
>>                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 (?)");
>
> This code makes me do a doubletake, and not just because of the ridiculously
> wide TAB-based indentation.  Why reload the driver class over and over
> again?  Only the first time does anything.
>
> One might wish to take the Sun/Oracle advice:
> "The DriverManager methods getConnection and getDrivers have been enhanced
> to support the Java Standard Edition Service Provider mechanism. JDBC 4.0
> Drivers must include the file META-INF/services/java.sql.Driver. This file
> contains the name of the JDBC drivers implementation of java.sql.Driver. For
> example, to load the my.sql.Driver class, the
> META-INF/services/java.sql.Driver file would contain the entry:
>
>  my.sql.Driver
>
>  [org.postgresql.Driver]
>
> "Applications no longer need to explictly load JDBC drivers using
> Class.forName(). Existing programs which currently load JDBC drivers using
> Class.forName() will continue to work without modification."
>
> You still don't need 'Class.forName()' more than once per driver if you
> stick to the old way.
>
> Personally I prefer JNDI-based approaches to loading DB drivers.  I also
> usually prefer JPA to raw JDBC, but there are times when JDBC is
> unquestionably the better tool.
>
> --
> Lew
> Honi soit qui mal y pense.
> http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>



--
Brady Edwards
(720) 684-2984
Seagate Technology

On 28 April 2011 08:40, Brady S Edwards <brady.s.edwards@seagate.com> wrote:
> Thank you for your reply Oliver.
>
> Is it possible to call an anonymous plpgsql block with bind variables?
> With Oracle I would just prepare a statement like:
> declare
> ...
> begin
>  xx = ?;
>  ...
> end;
>
> These are created on the fly, so I can't really create stored
> procedures for them.

Looking at the description of DO, I don't see a way to provide parameters:

> The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed
andexecuted a single time. 

If the body of the block is used just once, then it may be simplest to
just interpolate the parameter values into the block body yourself
while generating it.
If you're going to reuse the block with different values, though, it's
probably worthwhile creating a proper function, even if you turn
around and drop it later. (You could perhaps create it in the pg_temp
schema)
If you don't have explicit reuse of these statements but the same
generated block is likely to be generated again later, you could do
something like a per-connection cache of function text to temporary
function definition.

Oliver

Brady S Edwards wrote:
> Your response does have anything to do with the question I was asking.

I had noted that your question was already answered.  This is a discussion
group, not a helpdesk.  I was discussing.  Oh, please, please pardon me.  I
guess I'm not your little slave lackey-boy.

I notice that your response didn't have anything to do with the points I
raised, which were on topic for a JDBC newsgroup.

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

Thank you again Oliver for your reply.

I ended up parsing the parameters into the string block and then
preparing the statement for the short term.
I'm getting good performance currently.

For the long term I'll use your advice and create functions where appropriate.

Thanks again

On Wed, Apr 27, 2011 at 5:17 PM, Oliver Jowett <oliver@opencloud.com> wrote:
> On 28 April 2011 08:40, Brady S Edwards <brady.s.edwards@seagate.com> wrote:
>> Thank you for your reply Oliver.
>>
>> Is it possible to call an anonymous plpgsql block with bind variables?
>> With Oracle I would just prepare a statement like:
>> declare
>> ...
>> begin
>>  xx = ?;
>>  ...
>> end;
>>
>> These are created on the fly, so I can't really create stored
>> procedures for them.
>
> Looking at the description of DO, I don't see a way to provide parameters:
>
>> The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed
andexecuted a single time. 
>
> If the body of the block is used just once, then it may be simplest to
> just interpolate the parameter values into the block body yourself
> while generating it.
> If you're going to reuse the block with different values, though, it's
> probably worthwhile creating a proper function, even if you turn
> around and drop it later. (You could perhaps create it in the pg_temp
> schema)
> If you don't have explicit reuse of these statements but the same
> generated block is likely to be generated again later, you could do
> something like a per-connection cache of function text to temporary
> function definition.
>
> Oliver
>



--
Brady Edwards
(720) 684-2984
Seagate Technology