Solved

How to write prepare statement for MSSQL 2000?

Posted on 2006-06-27
7
300 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Apache Camel - Unable to propagate JMS Header Properties between Request - Response 3 18
map related example 6 59
couple of eclipse 5 52
spring jars download 1 53
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

733 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