Solved

How to write prepare statement for MSSQL 2000?

Posted on 2006-06-27
7
299 Views
Last Modified: 2012-05-05
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(MostRecent));
              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,Currency);
              pstmt.setString(12,Exchange_rate);
              pstmt.setString(13,EFT_Type);
              pstmt.setString(14,Customer_id);
              pstmt.setString(15,Customer_GIC);
              pstmt.setString(16,Corrected);
              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.
0
Comment
Question by:fylix0000
  • 3
  • 2
  • 2
7 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 16994369
That's looks fine. What problems are you having?
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 16994371
In my opinion, it should work.

David
0
 
LVL 16

Assisted Solution

by:suprapto45
suprapto45 earned 150 total points
ID: 16994416
Same time CEHJ ;).

Got to sleep now. Leave it to you if the asker has any problem

David
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:fylix0000
ID: 16994821
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/migrate_boaeft.txt"));
            
            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.odbc.JdbcOdbcDriver");
             }
               catch(java.lang.ClassNotFoundException e){

                System.err.print("Class error");
                System.err.println(e.getMessage());
               
               }

          try {
                
                 Connection con = DriverManager.getConnection("jdbc:odbc:BOAMSSQL");
          
              // 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(MostRecent));
              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,Currency);
              pstmt.setString(12,Exchange_rate);
              pstmt.setString(13,EFT_Type);
              pstmt.setString(14,Customer_id);
              pstmt.setString(15,Customer_GIC);
              pstmt.setString(16,Corrected);
              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      
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 350 total points
ID: 16996041
You need to close the statement and connection
0
 

Author Comment

by:fylix0000
ID: 16996637
Thank you :) not sure if that was it but I got it working after awhile.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16999017
:-)

It was ;-)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tomcat not starting 6 68
Android development question 2 54
How do I remove an object from a 3 40
Is Applet the way to go for my drag and drop system? 8 25
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This video teaches viewers about errors in exception handling.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question