Re: JDBC - Need to declare variables for values in insert statement

Bob Futrelle <bob.futrelle@xxxxxxxxx> · Mon, 28 Jan 2013 14:09:37 -0500

Thanks to Edson and Dave for lightning responses.I'm confident that your answers will do the job.
I'll follow up on the advice AFTER I get my coffee ;-)

I'm really focused on the NLP content of my research,
but I need a DB infrastructure to do it right.
(Not a bunch of files as in ancient times.)

 --- Bob

On Mon, Jan 28, 2013 at 1:59 PM, Edson Richter <edsonrichter@xxxxxxxxxxx> wrote:

    You have two options:

      st.execute("insert into hello values ('bKey', "+f1()+")");

      or

      PreparedStatement st = db.prepareStatement("insert into hello
      values ('bKey', ?)");

      st.setInteger(1, f1());

      where 1 is the first parameter, 2 is the second parameter, and so
      on.

      Regards,

      Edson Richter

      Em 28/01/2013 16:50, Bob Futrelle escreveu:

      Here's a small, but complete code example - Bob

          package us.tsos.dbs.pg;

          import java.sql.*;

          /**
           * This is an effort to get a computed
              value from a Java function
           * (or data object) included properly in
              the VALUES entries.
           * So, how can I declare an SQL variable
              and set its value to some Java value?
           * Results viewed in pgAdmin3  with query 'select * from
              public.hello'.
           *
           * Jar in classpath is
              postgresql-9.2-1002.jdbc4.jar
           * 
           * @version 0.1  Mon Jan 28 EST 2013
           * @author Bob Futrelle
           */

          public class JDBCVariableTest {

           Connection
              db;
           Statement
              st;
           Boolean
              boo;

           public
              static void main(String[] args) throws SQLException {

           JDBCVariableTest
              testIt = new JDBCVariableTest();
           testIt.helloVariables();
           }

           public
              int f1() { return 3;}

           public
              void helloVariables() throws SQLException  {

           int
              intVar = f1();  // OK in Java, but SQL/JDBC?

           try
              {
           db
              = DriverManager.getConnection("jdbc:postgresql:Articles",
              "robertfutrelle", "<my pw>");
           st
              = db.createStatement();
           boo
               = st.execute("CREATE TABLE IF NOT EXISTS hello ( Name
              VARCHAR NOT NULL PRIMARY KEY, value int)");

           //
              Declare ....  ??

           //
              INSTEAD OF THE LITERAL 4 VALUE (which works)
           //
              how do I declare a variable and assign the f1() return
              value to it
           //
              and then include it so the value 3 appears in the inserted
              record?

           //st.execute("insert
              into hello values('aKey',4)");
           st.execute("insert
              into hello values('bKey',4)");
           }
              catch (SQLException e) {
           //
              TODO Auto-generated catch block
           e.printStackTrace();

             }
           }
          }

          On Mon, Jan 28, 2013 at 8:39 AM, Dave
            Cramer <pg@xxxxxxxxxxxxx>
            wrote:

              Bob,

                Can you provide a snippet of code so I can
                  understand what you mean by declare ?

                Dave Cramer

                  dave.cramer(at)credativ(dot)ca

                  http://www.credativ.ca

                    On Mon, Jan 28, 2013 at
                      7:11 AM, Bob Futrelle <bob.futrelle@xxxxxxxxx>
                      wrote:

                        Do the declare statements and
                          insert all have to be done in one statement
                          execute()?
                          That is, what is the scope of variables I
                            declare?
                          I see a variety of syntax examples, some
                            for older versions?
                          I'm using pg 9.2.2, so what are the
                            rules/syntax for declaring and using
                            variables?

                          Use case: I collect metadata from XML
                            articles such as title, journalName, volume,
                            year.  For each article I need to create a
                            pg table record and insert the values for
                            the various metadata items in the article.

                          This is my first post to this list.

                          Thanks,

                           - Bob