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