Bob,
Ok, have a look at PreparedStatement
Essentially the same
PreparedStatement pstmt= db.prepareStatement("insert into hello values ?")
pstmt.setInt(1,intVar)
pstmt.execute()
On Mon, Jan 28, 2013 at 1:50 PM, Bob Futrelle <bob.futrelle@xxxxxxxxx> wrote:
Here's a small, but complete code example - Bobpackage 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 blocke.printStackTrace();}}}
On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer <pg@xxxxxxxxxxxxx> wrote:Bob,Can you provide a snippet of code so I can understand what you mean by declare ?On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle <bob.futrelle@xxxxxxxxx> 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