Solved

How to write prepare statement for MSSQL 2000?

Posted on 2006-06-27
7
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

717 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