Home > mailing lists

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

JDBC - Need to declare variables for values in insert statement

From

Bob Futrelle

Date:

28 January 2013, 15:11:38

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

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

From

Dave Cramer

Date:

28 January 2013, 16:40:04

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

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

From

Bob Futrelle

Date:

28 January 2013, 21:50:25

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

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

From

Edson Richter

Date:

28 January 2013, 22:00:00

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

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

From

Dave Cramer

Date:

28 January 2013, 22:00:33

Bob,

Ok, have a look at PreparedStatement

Essentially the same

PreparedStatement pstmt= db.prepareStatement("insert into hello values ?")

pstmt.setInt(1,intVar)

pstmt.execute()

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Mon, Jan 28, 2013 at 1:50 PM, Bob Futrelle <bob.futrelle@gmail.com> wrote:

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

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

From

Bob Futrelle

Date:

28 January 2013, 22:09:42

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

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

From

Edson Richter

Date:

28 January 2013, 22:22:45

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

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

From

Bob Futrelle

Date:

29 January 2013, 01:42:06

I had read 'through' the JDBC material,

but now reading more deeply with more insight.

The API is useful too.

Anyhoo, PreparedStatement works like a charm, viz.,

PreparedStatement pstmt= db.prepareStatement("insert into hello values ('cKey', ?)");
pstmt.setInt(1,intVar);
pstmt.execute();

This also worked, include the function directly, skip the java field. Nice.

pstmt.setInt(1,f1());

Makes sense, because the setInt() API says that the value resides in the Java world.

"Sets the designated parameter to the given Java int value."

I have about 250 source files on my machine that use PreparedStatements.

I wrote a few of them, but usually by grabbing code from the web.

The rest are demos or parts of systems I used for some of my work.

Used embedded Derby = JavaDB back then - history now.

The fact that the code contained a few PreparedStatements was not what I focused on.

The code just worked, doing what I needed.

My new understanding now allows me to do a ton of good NLP stuff.

Back to the future.

Thanks all,

- Bob

On Mon, Jan 28, 2013 at 2:22 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

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