Re: [GENERAL] JDBC - Need to declare variables for values in insert statement
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 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
> 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", "");
>> 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 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 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: [GENERAL] JDBC - Need to declare variables for values in insert statement
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
mailto:edsonrich...@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", "");
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 mailto:p...@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
mailto:bob.futre...@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: [GENERAL] JDBC - Need to declare variables for values in insert statement
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 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", "");
> 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 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 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: [GENERAL] JDBC - Need to declare variables for values in insert statement
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 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", "");
> 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 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 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: [GENERAL] JDBC - Need to declare variables for values in insert statement
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", "");
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 > 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
mailto:bob.futre...@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: [GENERAL] 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", "");
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 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 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: [GENERAL] JDBC - Need to declare variables for values in insert statement
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 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 > >
[GENERAL] 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
