?
Solved

How to write prepare statement for MSSQL 2000?

Posted on 2006-06-27
7
Medium Priority
?
302 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 600 total points
ID: 16994416
Same time CEHJ ;).

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

David
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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 1400 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
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…
This video teaches viewers about errors in exception handling.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month10 days, 5 hours left to enroll

762 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