• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem while inserting data into MS SQL table

 
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Whatever. Here's a tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic