fylix0000
asked on
How to write prepare statement for MSSQL 2000?
String sql = "INSERT INTO boaeft "+
"VALUES(?,?,?,?,?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?) ";
PreparedStatement pstmt = con.prepareStatement(sql);
// Set the values
pstmt.setString(1, message_id);
pstmt.setInt(2, Integer.parseInt(Ordinal)) ;
pstmt.setInt(3, Integer.parseInt(MostRecen t));
pstmt.setString(4, Reference);
pstmt.setInt(5, Integer.parseInt(Queue));
pstmt.setString(6, Source_System);
pstmt.setString(7,Transmit _Time+"00" );
pstmt.setString(8,Transmit _Date);
pstmt.setString(9,Amount);
pstmt.setString(10,Amount_ Cdn);
pstmt.setString(11,Currenc y);
pstmt.setString(12,Exchang e_rate);
pstmt.setString(13,EFT_Typ e);
pstmt.setString(14,Custome r_id);
pstmt.setString(15,Custome r_GIC);
pstmt.setString(16,Correct ed);
pstmt.setString(17,Fintrac _rate);
pstmt.setString(18,Fintrac _ref);
pstmt.setString(19,OnFile) ;
pstmt.executeUpdate(sql);
Above was part of my code, I was able to get the connection fine but I do not know if the above syntax works with MS SQL, I know the values(?,?,?,?,?) syntax works for MYSQL though, the reason I ask this question here because I hope someone must have work with SQL 2000 using preparestatement.
"VALUES(?,?,?,?,?,?,?,?,?,
PreparedStatement pstmt = con.prepareStatement(sql);
// Set the values
pstmt.setString(1, message_id);
pstmt.setInt(2, Integer.parseInt(Ordinal))
pstmt.setInt(3, Integer.parseInt(MostRecen
pstmt.setString(4, Reference);
pstmt.setInt(5, Integer.parseInt(Queue));
pstmt.setString(6, Source_System);
pstmt.setString(7,Transmit
pstmt.setString(8,Transmit
pstmt.setString(9,Amount);
pstmt.setString(10,Amount_
pstmt.setString(11,Currenc
pstmt.setString(12,Exchang
pstmt.setString(13,EFT_Typ
pstmt.setString(14,Custome
pstmt.setString(15,Custome
pstmt.setString(16,Correct
pstmt.setString(17,Fintrac
pstmt.setString(18,Fintrac
pstmt.setString(19,OnFile)
pstmt.executeUpdate(sql);
Above was part of my code, I was able to get the connection fine but I do not know if the above syntax works with MS SQL, I know the values(?,?,?,?,?) syntax works for MYSQL though, the reason I ask this question here because I hope someone must have work with SQL 2000 using preparestatement.
That's looks fine. What problems are you having?
In my opinion, it should work.
David
David
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
import java.io.*;
import java.sql.*;
import java.util.StringTokenizer;
public class migrateMYSQL {
public static void main(String arg[]) throws IOException{
migrate();
}
public static void migrate() throws IOException{
BufferedReader fis = new BufferedReader(new FileReader("C:/Java/migrat e_boaeft.t xt"));
String[] element = new String[16];
String FileRecord = null;
String test = null;
migrate_boaeft(element);
}
public static void migrate_boaeft(String element[]){
String message_id = "0";//element[0];
String Ordinal ="0";// element[1];
String MostRecent ="0";// element[2];
String Reference ="0";// element[3];
String Queue ="0";// element[4];
String Source_System ="0";// element[5];
String Transmit_Time ="0";// element[6];
String Transmit_Date ="0";// element[7];
String Amount = "0";//element[8];
String Amount_Cdn ="0";// element[9];
String Currency = "0";//element[10];
String Exchange_rate = "0";//element[11];
String EFT_Type = "0";//element[12];
String Customer_id ="0";// element[13];
String Customer_GIC = "0";//element[14];
String Corrected = "0";//element[15];
String Fintrac_rate = "0";//element[16];
String Fintrac_ref = "0"; //element[17];
String OnFile = "0"; //element[18];
try{
Class.forName("sun.jdbc.od bc.JdbcOdb cDriver");
}
catch(java.lang.ClassNotFo undExcepti on e){
System.err.print("Class error");
System.err.println(e.getMe ssage());
}
try {
Connection con = DriverManager.getConnectio n("jdbc:od bc:BOAMSSQ L");
// Prepare a statement to insert a record
String sql = "INSERT INTO boaeft "+
"VALUES(?,?,?,?,?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?) ";
PreparedStatement pstmt = con.prepareStatement(sql);
// Set the values
pstmt.setString(1, message_id);
pstmt.setInt(2, Integer.parseInt(Ordinal)) ;
pstmt.setInt(3, Integer.parseInt(MostRecen t));
pstmt.setString(4, Reference);
pstmt.setInt(5, Integer.parseInt(Queue));
pstmt.setString(6, Source_System);
pstmt.setString(7,Transmit _Time+"00" );
pstmt.setString(8,Transmit _Date);
pstmt.setString(9,Amount);
pstmt.setString(10,Amount_ Cdn);
pstmt.setString(11,Currenc y);
pstmt.setString(12,Exchang e_rate);
pstmt.setString(13,EFT_Typ e);
pstmt.setString(14,Custome r_id);
pstmt.setString(15,Custome r_GIC);
pstmt.setString(16,Correct ed);
pstmt.setString(17,Fintrac _rate);
pstmt.setString(18,Fintrac _ref);
pstmt.setString(19,OnFile) ;
pstmt.executeUpdate(sql);
} catch (SQLException e) {
}
}
}
This is a code I use to test, pretty much just insert a bunch of 0 into my database, the class run but I never see the insert.
My table looks like this:
Message_Id varchar
Ordinal smallint
MostRecent tinyint
Reference varchar
Queue tinyint
Source_System varchar
Transmit_Time varchar
Transmit_Date varchar
Amount varchar
Amount_Cdn varchar
Currency vachar
Exchange_Rate varchar
EFT_Type varchar
Customer_Id varchar
Customer_Gic varchar
Corrected tinyint
Fintrac_Rate varchar
Fintrac_ref varchar
OnFile varchar
import java.sql.*;
import java.util.StringTokenizer;
public class migrateMYSQL {
public static void main(String arg[]) throws IOException{
migrate();
}
public static void migrate() throws IOException{
BufferedReader fis = new BufferedReader(new FileReader("C:/Java/migrat
String[] element = new String[16];
String FileRecord = null;
String test = null;
migrate_boaeft(element);
}
public static void migrate_boaeft(String element[]){
String message_id = "0";//element[0];
String Ordinal ="0";// element[1];
String MostRecent ="0";// element[2];
String Reference ="0";// element[3];
String Queue ="0";// element[4];
String Source_System ="0";// element[5];
String Transmit_Time ="0";// element[6];
String Transmit_Date ="0";// element[7];
String Amount = "0";//element[8];
String Amount_Cdn ="0";// element[9];
String Currency = "0";//element[10];
String Exchange_rate = "0";//element[11];
String EFT_Type = "0";//element[12];
String Customer_id ="0";// element[13];
String Customer_GIC = "0";//element[14];
String Corrected = "0";//element[15];
String Fintrac_rate = "0";//element[16];
String Fintrac_ref = "0"; //element[17];
String OnFile = "0"; //element[18];
try{
Class.forName("sun.jdbc.od
}
catch(java.lang.ClassNotFo
System.err.print("Class error");
System.err.println(e.getMe
}
try {
Connection con = DriverManager.getConnectio
// Prepare a statement to insert a record
String sql = "INSERT INTO boaeft "+
"VALUES(?,?,?,?,?,?,?,?,?,
PreparedStatement pstmt = con.prepareStatement(sql);
// Set the values
pstmt.setString(1, message_id);
pstmt.setInt(2, Integer.parseInt(Ordinal))
pstmt.setInt(3, Integer.parseInt(MostRecen
pstmt.setString(4, Reference);
pstmt.setInt(5, Integer.parseInt(Queue));
pstmt.setString(6, Source_System);
pstmt.setString(7,Transmit
pstmt.setString(8,Transmit
pstmt.setString(9,Amount);
pstmt.setString(10,Amount_
pstmt.setString(11,Currenc
pstmt.setString(12,Exchang
pstmt.setString(13,EFT_Typ
pstmt.setString(14,Custome
pstmt.setString(15,Custome
pstmt.setString(16,Correct
pstmt.setString(17,Fintrac
pstmt.setString(18,Fintrac
pstmt.setString(19,OnFile)
pstmt.executeUpdate(sql);
} catch (SQLException e) {
}
}
}
This is a code I use to test, pretty much just insert a bunch of 0 into my database, the class run but I never see the insert.
My table looks like this:
Message_Id varchar
Ordinal smallint
MostRecent tinyint
Reference varchar
Queue tinyint
Source_System varchar
Transmit_Time varchar
Transmit_Date varchar
Amount varchar
Amount_Cdn varchar
Currency vachar
Exchange_Rate varchar
EFT_Type varchar
Customer_Id varchar
Customer_Gic varchar
Corrected tinyint
Fintrac_Rate varchar
Fintrac_ref varchar
OnFile varchar
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you :) not sure if that was it but I got it working after awhile.
:-)
It was ;-)
It was ;-)