Re: 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 <edsonrichter(at)hotmail(dot)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(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
>>>
>>>
>>
>
>