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

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(at)fastcrypt(dot)com> 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(dot)futrelle(at)gmail(dot)com>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
>>
>>
>