Re: getGeneratedKeys( ) Problem in JSF
Mongoose wrote:
I'm trying to use a Prepared Statement instead. However, I'm having a
little trouble getting the exact syntax. Again, I'm used to SQL
Server and ASP.NET. Can someone show me an example of Inserting into
an Oracle 10g Database with a PreparedStatement in Java?
See the code below for .NET to Java equivalence examples
(the Java example is MySQL, but the code would look very similar
for Oracle).
Is it
supposed to be something like that shown below???
PreparedStatement insertPurchase=conn.prepareStatement("INSERT INTO
Purchase (PurchaseID, CarModelD) VALUES PurchaseID_Seq.nextval, ?)");
insertPurchase.setInt(1, ModID);
insertPurchase.executeUpdate();
Yes. Except that there is a missing ( after VALUES.
Arne
======================================================
Bad .NET code:
using System;
using System.Data;
using System.Data.SqlClient;
namespace E
{
public class SqlServer2
{
public static void Main(string[] args)
{
SqlConnection con = new
SqlConnection("server=ARNEPC3\\SQLEXPRESS;Integrated
Security=SSPI;database=Test");
con.Open();
SqlCommand cre = new SqlCommand("CREATE TABLE j(id INTEGER
NOT NULL, txt VARCHAR(50), PRIMARY KEY(id))", con);
cre.ExecuteNonQuery();
SqlCommand ins = new SqlCommand("INSERT INTO j VALUES(@id,
@txt)", con);
ins.Parameters.Add("@id", SqlDbType.Int);
ins.Parameters.Add("@txt", SqlDbType.VarChar, 50);
for(int i = 0; i < 5; i++)
{
ins.Parameters["@id"].Value = i+1;
ins.Parameters["@txt"].Value = "Test #" + (i+1);
ins.ExecuteNonQuery();
}
SqlCommand sel = new SqlCommand("SELECT id,txt FROM j WHERE
id > @lim", con);
sel.Parameters.Add("@lim", SqlDbType.Int);
sel.Parameters["@lim"].Value = 3;
SqlDataReader rdr = sel.ExecuteReader();
while(rdr.Read())
{
int id = rdr.GetInt32(0);
String txt = rdr.GetString(1);
Console.WriteLine(id + " : " + txt);
}
rdr.Close();
SqlCommand drp = new SqlCommand("DROP TABLE j", con);
drp.ExecuteNonQuery();
con.Close();
}
}
}
Good .NET code:
using System;
using System.Data;
using System.Data.Common;
namespace E
{
public class SqlServer4
{
public static void Main(string[] args)
{
DbProviderFactory dbf =
DbProviderFactories.GetFactory("System.Data.SqlClient");
IDbConnection con = dbf.CreateConnection();
con.ConnectionString =
"server=ARNEPC3\\SQLEXPRESS;Integrated Security=SSPI;database=Test";
con.Open();
IDbCommand cre = con.CreateCommand();
cre.CommandText = "CREATE TABLE j(id INTEGER NOT NULL, txt
VARCHAR(50), PRIMARY KEY(id))";
cre.Connection = con;
cre.ExecuteNonQuery();
IDbCommand ins = con.CreateCommand();
ins.CommandText = "INSERT INTO j VALUES(@id,@txt)";
ins.Connection = con;
IDbDataParameter p1 = ins.CreateParameter();
p1.ParameterName = "@id";
p1.DbType = DbType.Int32;
ins.Parameters.Add(p1);
IDbDataParameter p2 = ins.CreateParameter();
p2.ParameterName = "@txt";
p2.DbType = DbType.String;
p2.Size = 50;
ins.Parameters.Add(p2);
for(int i = 0; i < 5; i++)
{
((IDbDataParameter)ins.Parameters["@id"]).Value = i+1;
((IDbDataParameter)ins.Parameters["@txt"]).Value =
"Test #" + (i+1);
ins.ExecuteNonQuery();
}
IDbCommand sel = con.CreateCommand();
sel.CommandText = "SELECT id,txt FROM j WHERE id > @lim";
sel.Connection = con;
IDbDataParameter p = ins.CreateParameter();
p.ParameterName = "@lim";
p.DbType = DbType.Int32;
p.Value = 3;
sel.Parameters.Add(p);
IDataReader rdr = sel.ExecuteReader();
while(rdr.Read())
{
int id = rdr.GetInt32(0);
String txt = rdr.GetString(1);
Console.WriteLine(id + " : " + txt);
}
rdr.Close();
IDbCommand drp = con.CreateCommand();
drp.CommandText = "DROP TABLE j";
drp.Connection = con;
drp.ExecuteNonQuery();
con.Close();
}
}
}
Java code:
import java.sql.*;
public class MySql4 {
public static void main(String[] args) throws
ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost/Test", "", "");
Statement cre = con.createStatement();
cre.executeUpdate("CREATE TABLE j(id INTEGER NOT NULL, txt
VARCHAR(50), PRIMARY KEY(id))");
PreparedStatement ins = con.prepareStatement("INSERT INTO j
VALUES(?,?)");
for(int i = 0; i < 5; i++) {
ins.setInt(1, i+1);
ins.setString(2, "Test #" + (i+1));
ins.executeUpdate();
}
PreparedStatement sel = con.prepareStatement("SELECT id,txt
FROM j WHERE id > ?");
sel.setInt(1, 3);
ResultSet rs = sel.executeQuery();
while(rs.next()) {
int id = rs.getInt(1);
String txt = rs.getString(2);
System.out.println(id + " : " + txt);
}
rs.close();
Statement drp = con.createStatement();
drp.executeUpdate("DROP TABLE j");
con.close();
}
}
(and please do not complain over poor exception handling - that
is not the point)