JDBC - Need to declare variables for values in insert statement

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

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@gmail.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

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

package us.tsos.dbs.pg <http://us.tsos.dbs.pg&gt;;

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@fastcrypt.com
<mailto:pg@fastcrypt.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.futrelle@gmail.com <mailto:bob.futrelle@gmail.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

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@fastcrypt.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.futrelle@gmail.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

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@fastcrypt.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.futrelle@gmail.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

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@hotmail.com <mailto:edsonrichter@hotmail.com>> 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 <http://us.tsos.dbs.pg&gt;;

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@fastcrypt.com
<mailto:pg@fastcrypt.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.futrelle@gmail.com <mailto:bob.futrelle@gmail.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

I would recommend the reading of the excellent The Java Tutorial, that
has a very well explained section about JDBC:
http://docs.oracle.com/javase/tutorial/jdbc/index.html

and the chapter about PreparedStatements:
http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Regards,

Edson Richter

Em 28/01/2013 17:09, Bob Futrelle escreveu:

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@hotmail.com>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@fastcrypt.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.futrelle@gmail.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