wood burning stoves 2.0*
The moose likes JDBC and the fly likes Problem while inserting data into MS SQL table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem while inserting data into MS SQL table" Watch "Problem while inserting data into MS SQL table" New topic
Author

Problem while inserting data into MS SQL table

Rishi Tyagi
Ranch Hand

Joined: Feb 14, 2002
Posts: 100
Dear All,
I am facing a strange problem with jdbc-odbc bridge database driver. Problem is as follows:
I wrote a small script to read data from one database table at my local MS SQL server and after that inserting a record in another database.

Now problem is that everything works fine, even executeUpdate() method gives me 1 after running insert query but when i check into database there is nothing in the destination database. Could not understand why is it happening, in fact it happened first time with me.

All java classes being used are as follows:
To get Database connection : DatabaseConnection.java

package com.xmedia.sms.db;


import java.util.Properties;


public class DatabaseConnection {
public static java.util.Properties props;
private String dbName;
private String dbUser;
private String dbPassword;
private String driverName;
private String dbUrl;
static{
try{
java.io.InputStream is = Class.forName("com.xmedia.sms.db.DatabaseConnection").getResourceAsStream("/properties/db.properties");
props = new java.util.Properties();
props.load(is);
}catch(Exception e){
props=null;
System.out.println("Either Database property '/properties/db.properties' file does not exists or corrupted");
}
}
public DatabaseConnection() {
}

public Properties getProps() {
return props;
}

public void setDbName(String dbName) {
this.dbName = dbName;
try{
setDbUrl((String)props.getProperty((dbName+".url")));
setDbUser((String)props.getProperty((dbName+".user")));
setDbPassword((String)props.getProperty((dbName+".password")));
}catch(Exception e){}
}

public void setDbUser(String dbUser) {
this.dbUser = dbUser;
}

public void setDbPassword(String dbPassword) {
this.dbPassword = dbPassword;
}

public void setDriverName(String driverName) {
this.driverName = driverName;
}

public void setDbUrl(String dbUrl) {
this.dbUrl = dbUrl;
}

public String getDbName() {
return dbName;
}

public String getDbUser() {
return dbUser;
}

public String getDbPassword() {
return dbPassword;
}

public String getDriverName() {
return driverName;
}

public String getDbUrl() {
return dbUrl;
}

public java.sql.Connection connect(){
java.sql.Connection conn=null;
try{
setDriverName((String)props.getProperty("drivers"));
setDbUrl((String)props.getProperty((dbName+".url")));
setDbUser((String)props.getProperty((dbName+".user")));
setDbPassword((String)props.getProperty((dbName+".password")));
Class.forName(getDriverName().trim());
conn=java.sql.DriverManager.getConnection(dbUrl.trim(),dbUser.trim(),dbPassword.trim());

}catch(Exception e){
conn=null;
System.out.println("Error while connecting with database :"+e);
}
return conn;
}
}

-------------------------

To pick records from the source database i am using following code:
public List getRecordsFromSource()
{
List list=new ArrayList();
Connection conn=null;

dbConn.setDbName(sourceDBName);
conn=dbConn.connect();
if(conn!=null)
{
try{
Statement st=conn.createStatement();
if(debug==true)
System.out.println("Executing SELECT QUERY :"+sourceSQLQuery);
ResultSet rs=st.executeQuery(sourceSQLQuery);
while(rs.next())
{

String[] strData=new String[22];
strData[0]=rs.getString("ID");
strData[1]=rs.getString("MessageRaw");
strData[2]=rs.getString("MessageDataType");
strData[3]=rs.getString("Message");
strData[4]=rs.getString("SourceMSISDN");
strData[5]=rs.getString("SourceType");
strData[6]=rs.getString("SourceDate");
strData[7]=rs.getString("SourceCountry");
strData[8]=rs.getString("SourceOperator");
strData[9]=rs.getString("DestinationNumber");
strData[10]=rs.getString("ImageId");
strData[11]=rs.getString("Nickname");
strData[12]=rs.getString("Status");
strData[13]=rs.getString("Archive");
strData[14]=rs.getString("ModeratorId");
strData[15]=rs.getString("TrackModeratorId");
strData[16]=rs.getString("ModeratorTimeStamp");
strData[17]=rs.getString("ModeratedDate");
strData[18]=rs.getString("Rank");
strData[19]=rs.getString("ServiceId");
strData[20]=rs.getString("ServiceSectionId");
strData[21]=rs.getString("ConnectionId");
if(debug==true)
{
System.out.print("ID :"+strData[0]+",");
//System.out.println("MsgRaw :"+strData[1]+",");
}
list.add(strData);
strData=null;
}

rs.close();
rs=null;
st.close();
st=null;
}catch(Exception e){
list=new ArrayList();
}
}

try{
if(conn!=null)
conn.close();
}catch(Exception e){
}
conn=null;


return list;

}
-----------------
To insert data into destination database i am using following code:

public int insertRecordsToDestination(String[] str)
{
int rep=0;
Connection conn=null;

dbConn.setDbName(destDBName);
conn=dbConn.connect();
if(conn!=null)
{
try{
//Statement st=conn.createStatement();
for(int i=0;i<str.length;i++)
{if(str[i]!=null && str[i].length()>0)
str[i]=str[i].trim();
else
{
if(i==0 || i==2 || i==3 || i==5 || i==6 || i==7 || i==8 || i==10 || i==15 || i==16)
str[i]="";
else
str[i]="0";
}
}
String sqlQuery="Insert Into SMS2Air_Messages(MessageRaw, MessageDataType, Message, SourceMSISDN,SourceType, SourceDate,SourceCountry,SourceOperator,DestinationNumber,ImageId,Nickname,Status,Archive,ModeratorId,TrackModeratorId,ModeratorTimeStamp,ModeratedDate,Rank,ServiceId,ServiceSectionId,ConnectionId) Values ('" + str[1] + "'," + str[2] + ",N'" + str[3] + "','" + str[4] + "'," + str[5] + ",'" + str[6] + "','" + str[7] + "','" + str[8] + "','" + str[9] + "'," + str[10] + ",N'" + str[11] + "'," + str[12] + "," + str[13] + "," + str[14] + "," + str[15] + ",'" + str[16] + "','" + str[17] + "'," + str[18] + "," + str[19] + "," + str[20] + "," + str[21] + ")";
if(debug==true)
System.out.println("Executing INSERT QUERY :"+sqlQuery);
com.xmedia.java.RUtil.saveToFile(""+str[0].trim()+".txt",sqlQuery);
Statement st=conn.createStatement();
rep=st.executeUpdate(sqlQuery);
conn.commit();
if(debug==true)
System.out.println("RESULT INSERT QUERY :"+rep);
sqlQuery=null;

st.close();
st=null;
}catch(Exception e){
rep=-1;
if(debug==true)
System.out.println("Error :"+e);
}
}

try{
if(conn!=null)
conn.close();
}catch(Exception e){
}
conn=null;


return rep;

}

-----------------------

Following is the method invoking both of above methods:

public void startServer()
{

int ctr=0;
setDebug(true);
while(isProcessFlag())
{
if(ctr==0)
System.out.println("Started at......"+new java.sql.Timestamp(System.currentTimeMillis()));

List sourceList=getRecordsFromSource();
int n1=sourceList.size();
System.out.println("sourceList Length :"+n1);
System.out.println("Inserting Data..");
ListIterator listItr=sourceList.listIterator();
String idList="";
int n=0;
while(listItr.hasNext())
{
String[] strData=(String[])listItr.next();
System.out.println("strData[0]:"+strData[0]);
int rep=insertRecordsToDestination(strData);
if(rep>0)
{
idList= idList.trim() +","+strData[0];
n += rep;
}
System.out.print(".."+strData[0]);
strData=null;
}
System.out.println("idList :"+idList);
System.out.println(""+n+" Out of "+n1+" Records Inserted successfully");
System.out.println("Last Time Invoked at..."+new java.sql.Timestamp(System.currentTimeMillis())+", Counter:"+ctr);
ctr++;
Wait(30);
}

}



----------------------

Kindly note that Database Collation is same at both source and destination database and in jdbc -odbc bridge i am using default language.

Records also contains some unicode data specially in Message and NickName database fields.

Is there anybody who has ever faced the same type problem and can help my why it is happening.
Best Regs,
Rishi Tyagi

[ May 17, 2006: Message edited by: Rishi Tyagi ]
[ May 17, 2006: Message edited by: Rishi Tyagi ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Since this is SQL Server, have you tried using the profiler to watch the actual statements being executed? My suspicion would be that you are doing something like performing both operations on the same database.

Also - why the JDBC-ODBC bridge? Is there some reason you don't use a type 4 driver?
[ May 17, 2006: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem while inserting data into MS SQL table
 
Similar Threads
Converting servlet to a WebSphere JDBC datasource
Get Database connection
DBUtility for closing connections
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters
Accessing session or application attributes from within a JavaBean