Row insertion causes corruption in data

Row insertion causes corruption in data

Post by sar.. » Sat, 06 Nov 1999 04:00:00



Hi,

I am having problem with my JDBC program on MS SQL server 7.0/NT SP4.

Please help.

Thanks,
Rinku.

Problem description
--------------------
Please refer to the attached source code. It seems stringified object
reference is causing corruption in offerId column.

If I comment out this step and instead insert a synthetic string of
equal or more length, insertion goes through without any corruption.
Here is the block of code that causes problem. Please keep in mind
that I need to retain this code in final version:

// object reference is converted to string, inserted at column 2
String objref_string = new String(orb.object_to_string(reference));
System.out.println("objref_string.length() = " +
objref_string.length());
pstmt.setString(++index, objref_string);

Here is the screen output:

G:\Rinku\ENGR264\Trader\Register>java RegisterImpl
Initializing the ORB
insertSQLCommand = INSERT INTO Mortgage (
offerId,reference,product_id,rate,pct_
down,years ) VALUES (  ?, ?, ?, ?, ?, ? )
offerId = Mortgage
GUID_ntse/137.134.108.101_1999/11/03_07:38:53_-47619683462487
43927
objref_string.length() = 262
LOAN A
Inserted 1 offer in table Mortgage
offerId = Mortgage
GUID_ntse/137.134.108.101_1999/11/03_07:38:53_-47619683462487
43927
deleteSQLCommand = DELETE FROM Mortgage WHERE offerId = ?
Could not delete offer in table Mortgage

Some key observations;

stringified object reference is 246 chars long whereas same database
column is VARCHAR(4096). The offerId is, as expected, garbled:
`: gage
GUID_ntse/137.134.108.101_1999/11/03_07:52:36_-4048922650262408166

Now let us take a look at the code block that is working. Please keep
in mind that this dummy code block is of no use in final version.

          String objref_string = new String("IOR:");
          for(int i = 0; i < 512; ++i)
              objref_string += "X";
          pstmt.setString(++index, objref_string);

As you could see the objref_string is at least 512 bytes long. The
insertion/deletion goes fine. This is where the row insertion is
happening with corruption in offerId column.

Java/JDBC source code
-----------------------
Please pay attention to export meyhod:
import java.sql.*;
import java.net.URL;
import java.net.URL;
import java.sql.*;
import org.omg.CORBA.*;
import java.util.*;

public class RegisterImpl extends CosTrading._RegisterImplBase
{
    private Connection con = null;
    private PreparedStatement pstmt = null;
    GUID_gen gen = null;

    public static void main(String args[])
    {
        // Initialize the ORB
        System.out.println("Initializing the ORB");
        ORB orb = org.omg.CORBA.ORB.init(args, null);

        CosTrading.Property[] properties = new CosTrading.Property[4];
        Any value;

        value = orb.create_any();
        value.insert_string("LOAN A");
        properties[0] = new CosTrading.Property("product_id", value);

        value = orb.create_any();
        value.insert_float((float)13.4);
        properties[1] = new CosTrading.Property("rate", value);

        value = orb.create_any();
        value.insert_float((float)5.67);
        properties[2] = new CosTrading.Property("pct_down", value);

        value = orb.create_any();
        value.insert_short((short)25);
        properties[3] = new CosTrading.Property("years", value);

        RegisterImpl reg = new RegisterImpl("xxx");
        String offerId;
        String serviceType;
        try
        {
            serviceType = new String("Mortgage");
            offerId = reg.export(new RegisterImpl("YYYY"), serviceType,
properties);
            reg.withdraw(offerId);
        }
        catch(Exception e)
        {
        }
    }

    private static GUID_gen GUID_generator = new
GUID_gen("ServiceOffer");

    public RegisterImpl(java.lang.String name)
    {
        super(name);
    }

    public RegisterImpl()
    {
        super();
    }

    public java.lang.String export
    (
        org.omg.CORBA.Object reference,
        java.lang.String type,
        CosTrading.Property[] properties
    ) throws
    CosTrading.RegisterPackage.InvalidObjectRef,
    CosTrading.IllegalServiceType,
    CosTrading.UnknownServiceType,
    CosTrading.RegisterPackage.InterfaceTypeMismatch,
    CosTrading.IllegalPropertyName,
    CosTrading.PropertyTypeMismatch,
    CosTrading.ReadonlyDynamicProperty,
    CosTrading.MissingMandatoryProperty,
    CosTrading.DuplicatePropertyName
    {
        // Design Note:
        // This method either return a Globally Unique ID(GUID) or
raises an exception
        // type is used as a prefix -- this is important since this is
how database
        // table name is figured out in withdraw

        gen = new GUID_gen(type);

        String retVal = gen.getGUID();

        try
        {
          // Load the jdbc-odbc bridge driver
          Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

          // get connection to trader database
          String url   =
"jdbc:odbc:LocalServer;database=Trader_Rinku_Huiju_Paramjit";
          con = DriverManager.getConnection(url, "sa", "");
        }
        catch(Exception e)
        {
          System.err.println(e);
          return null;
        }

        // insert the offer row in service specific database

        String insertSQLCommand = new String();
        insertSQLCommand += "INSERT INTO " + type + " (
offerId,reference";

        for(int i = 0; i < properties.length; ++i)
        {
            insertSQLCommand += "," + properties[i].name;
        }

        insertSQLCommand += " ) VALUES ( ";
        insertSQLCommand += " ?, ?";
        for(int i = 0; i < properties.length; ++i)
            insertSQLCommand += ", ?";

        insertSQLCommand += " )" ;

        ORB orb = null;
        try
        {
          orb = ORB.init();
        }
        catch(org.omg.CORBA.SystemException e)
        {
          System.err.println(e);
        }

        System.out.println("insertSQLCommand = " + insertSQLCommand);

        try
        {
          // Get a prepared statement for achieving insertion of offer
row
          pstmt = con.prepareStatement(insertSQLCommand);

          // column index(starts from 1)
          int index = 0;

          // offerId is string, inserted at column 1
          System.out.println("offerId = " + retVal);
          pstmt.setString(++index, retVal);

          // object reference is converted to string, inserted at column
2
//          String objref_string = new
String(orb.object_to_string(reference));
//          System.out.println("objref_string.length() = " +
objref_string.length());
//          pstmt.setString(++index, objref_string);

          String objref_string = new String("IOR:");
          for(int i = 0; i < 512; ++i)
              objref_string += "X";
          pstmt.setString(++index, objref_string);

          // the following loop finds out type of property value and
based on
          // type calls appropriate method of prepared statement
          for(int i = 0; i < properties.length; ++i)
          {
            Any value = properties[i].value;
            TypeCode any_type = value.type();
            TCKind any_kind = any_type.kind();

            if(any_kind.equals(TCKind.tk_short))
            {
                short typedValue = value.extract_short();
                pstmt.setShort(++index, typedValue);
            }
            else if(any_kind.equals(TCKind.tk_float))
            {
                float typedValue = value.extract_float();
                pstmt.setFloat(++index, typedValue);
            }
            else if(any_kind.equals(TCKind.tk_string))
            {
                String typedValue = value.extract_string();
                pstmt.setString(++index, typedValue);
                System.out.println(typedValue);
            }
            else if(any_kind.equals(TCKind.tk_double))
            {
                double typedValue = value.extract_double();
                pstmt.setDouble(++index, typedValue);
            }
          }

          int rowsAffected = pstmt.executeUpdate();
          pstmt.close();
          con.commit();

          if(rowsAffected == 0)
          {
            System.out.println("Could not insert offer in table " +
type);
          }
          else
          {
            System.out.println("Inserted " + rowsAffected + " offer in
table " + type);
          }
        }
        catch( Exception e )
        {
          System.err.println(e);
        }

        return retVal;
    }

  public void withdraw
  (
      java.lang.String id
  ) throws
  CosTrading.IllegalOfferId,
  CosTrading.UnknownOfferId,
  CosTrading.RegisterPackage.ProxyOfferId
  {
    // IMPLEMENT: Operation

    // Design Note:
    // A client application or the Trading Service administrator can
remove individual offers
    // one at a time using the unique identifier associated with each
offer. Exporters that
    // will eventually need to clean up obsolete service offers are
responsible for
    // remembering the identifiers assigned to their service offers.

    System.out.println("offerId = " + id);

    // Design note: We find out which table row to delete from the
offerId string itself.
    StringTokenizer st = new StringTokenizer(id);
    String tableName = st.nextToken();
    String deleteSQLCommand = "DELETE FROM " + tableName + " WHERE
offerId = ?";
    System.out.println("deleteSQLCommand = " + deleteSQLCommand);

    try
    {
      // Load the jdbc-odbc bridge driver
      Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

      // get connection to trader database
      String url   =
"jdbc:odbc:LocalServer;database=Trader_Rinku_Huiju_Paramjit";
      con = DriverManager.getConnection(url, "sa", "");
    }
    catch(Exception e)
    {
      System.err.println(e);
      return;
    }

    try
    {
      pstmt = con.prepareStatement(deleteSQLCommand);
      pstmt.setString(1, id);
      int rowsAffected = pstmt.executeUpdate();
      pstmt.close();
      con.commit();

      if(rowsAffected == 0)
      {
...

read more »

 
 
 

Row insertion causes corruption in data

Post by Joseph Weinstei » Sat, 06 Nov 1999 04:00:00


Hi.
If you can duplicate this with a simple standalone JDBC program
which just starts with the string you want, it will be much easier to
help. Just a main() which creates a temp table with the varchar
column, inserts your string, then queries the table to see what was
inserted and compares it to the original. I think I'll be able to
help you then. I am also concerned that your string may have
characters that may not be printable or storable in the DBMS.
If any are 16-bit characters, you should at least use nvarchar.
Joe
sar...@tcsi.com wrote:
> Hi,
> I am having problem with my JDBC program on MS SQL server 7.0/NT SP4.
> Please help. Thanks, Rinku.

> Problem description
> --------------------
> Please refer to the attached source code. It seems stringified object
> reference is causing corruption in offerId column.

> If I comment out this step and instead insert a synthetic string of
> equal or more length, insertion goes through without any corruption.
> Here is the block of code that causes problem. Please keep in mind
> that I need to retain this code in final version:

> // object reference is converted to string, inserted at column 2
> String objref_string = new String(orb.object_to_string(reference));
> System.out.println("objref_string.length() = " +
> objref_string.length());
> pstmt.setString(++index, objref_string);

> Here is the screen output:

> G:\Rinku\ENGR264\Trader\Register>java RegisterImpl
> Initializing the ORB
> insertSQLCommand = INSERT INTO Mortgage (
> offerId,reference,product_id,rate,pct_
> down,years ) VALUES (  ?, ?, ?, ?, ?, ? )
> offerId = Mortgage
> GUID_ntse/137.134.108.101_1999/11/03_07:38:53_-47619683462487
> 43927
> objref_string.length() = 262
> LOAN A
> Inserted 1 offer in table Mortgage
> offerId = Mortgage
> GUID_ntse/137.134.108.101_1999/11/03_07:38:53_-47619683462487
> 43927
> deleteSQLCommand = DELETE FROM Mortgage WHERE offerId = ?
> Could not delete offer in table Mortgage

> Some key observations;

> stringified object reference is 246 chars long whereas same database
> column is VARCHAR(4096). The offerId is, as expected, garbled:
> `: gage
> GUID_ntse/137.134.108.101_1999/11/03_07:52:36_-4048922650262408166

> Now let us take a look at the code block that is working. Please keep
> in mind that this dummy code block is of no use in final version.

>           String objref_string = new String("IOR:");
>           for(int i = 0; i < 512; ++i)
>               objref_string += "X";
>           pstmt.setString(++index, objref_string);

> As you could see the objref_string is at least 512 bytes long. The
> insertion/deletion goes fine. This is where the row insertion is
> happening with corruption in offerId column.

> Java/JDBC source code
> -----------------------
> Please pay attention to export meyhod:
> import java.sql.*;
> import java.net.URL;
> import java.net.URL;
> import java.sql.*;
> import org.omg.CORBA.*;
> import java.util.*;

> public class RegisterImpl extends CosTrading._RegisterImplBase
> {
>     private Connection con = null;
>     private PreparedStatement pstmt = null;
>     GUID_gen gen = null;

>     public static void main(String args[])
>     {
>         // Initialize the ORB
>         System.out.println("Initializing the ORB");
>         ORB orb = org.omg.CORBA.ORB.init(args, null);

>         CosTrading.Property[] properties = new CosTrading.Property[4];
>         Any value;

>         value = orb.create_any();
>         value.insert_string("LOAN A");
>         properties[0] = new CosTrading.Property("product_id", value);

>         value = orb.create_any();
>         value.insert_float((float)13.4);
>         properties[1] = new CosTrading.Property("rate", value);

>         value = orb.create_any();
>         value.insert_float((float)5.67);
>         properties[2] = new CosTrading.Property("pct_down", value);

>         value = orb.create_any();
>         value.insert_short((short)25);
>         properties[3] = new CosTrading.Property("years", value);

>         RegisterImpl reg = new RegisterImpl("xxx");
>         String offerId;
>         String serviceType;
>         try
>         {
>             serviceType = new String("Mortgage");
>             offerId = reg.export(new RegisterImpl("YYYY"), serviceType,
> properties);
>             reg.withdraw(offerId);
>         }
>         catch(Exception e)
>         {
>         }
>     }

>     private static GUID_gen GUID_generator = new
> GUID_gen("ServiceOffer");

>     public RegisterImpl(java.lang.String name)
>     {
>         super(name);
>     }

>     public RegisterImpl()
>     {
>         super();
>     }

>     public java.lang.String export
>     (
>         org.omg.CORBA.Object reference,
>         java.lang.String type,
>         CosTrading.Property[] properties
>     ) throws
>     CosTrading.RegisterPackage.InvalidObjectRef,
>     CosTrading.IllegalServiceType,
>     CosTrading.UnknownServiceType,
>     CosTrading.RegisterPackage.InterfaceTypeMismatch,
>     CosTrading.IllegalPropertyName,
>     CosTrading.PropertyTypeMismatch,
>     CosTrading.ReadonlyDynamicProperty,
>     CosTrading.MissingMandatoryProperty,
>     CosTrading.DuplicatePropertyName
>     {
>         // Design Note:
>         // This method either return a Globally Unique ID(GUID) or
> raises an exception
>         // type is used as a prefix -- this is important since this is
> how database
>         // table name is figured out in withdraw

>         gen = new GUID_gen(type);

>         String retVal = gen.getGUID();

>         try
>         {
>           // Load the jdbc-odbc bridge driver
>           Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

>           // get connection to trader database
>           String url   =
> "jdbc:odbc:LocalServer;database=Trader_Rinku_Huiju_Paramjit";
>           con = DriverManager.getConnection(url, "sa", "");
>         }
>         catch(Exception e)
>         {
>           System.err.println(e);
>           return null;
>         }

>         // insert the offer row in service specific database

>         String insertSQLCommand = new String();
>         insertSQLCommand += "INSERT INTO " + type + " (
> offerId,reference";

>         for(int i = 0; i < properties.length; ++i)
>         {
>             insertSQLCommand += "," + properties[i].name;
>         }

>         insertSQLCommand += " ) VALUES ( ";
>         insertSQLCommand += " ?, ?";
>         for(int i = 0; i < properties.length; ++i)
>             insertSQLCommand += ", ?";

>         insertSQLCommand += " )" ;

>         ORB orb = null;
>         try
>         {
>           orb = ORB.init();
>         }
>         catch(org.omg.CORBA.SystemException e)
>         {
>           System.err.println(e);
>         }

>         System.out.println("insertSQLCommand = " + insertSQLCommand);

>         try
>         {
>           // Get a prepared statement for achieving insertion of offer
> row
>           pstmt = con.prepareStatement(insertSQLCommand);

>           // column index(starts from 1)
>           int index = 0;

>           // offerId is string, inserted at column 1
>           System.out.println("offerId = " + retVal);
>           pstmt.setString(++index, retVal);

>           // object reference is converted to string, inserted at column
> 2
> //          String objref_string = new
> String(orb.object_to_string(reference));
> //          System.out.println("objref_string.length() = " +
> objref_string.length());
> //          pstmt.setString(++index, objref_string);

>           String objref_string = new String("IOR:");
>           for(int i = 0; i < 512; ++i)
>               objref_string += "X";
>           pstmt.setString(++index, objref_string);

>           // the following loop finds out type of property value and
> based on
>           // type calls appropriate method of prepared statement
>           for(int i = 0; i < properties.length; ++i)
>           {
>             Any value = properties[i].value;
>             TypeCode any_type = value.type();
>             TCKind any_kind = any_type.kind();

>             if(any_kind.equals(TCKind.tk_short))
>             {
>                 short typedValue = value.extract_short();
>                 pstmt.setShort(++index, typedValue);
>             }
>             else if(any_kind.equals(TCKind.tk_float))
>             {
>                 float typedValue = value.extract_float();
>                 pstmt.setFloat(++index, typedValue);
>             }
>             else if(any_kind.equals(TCKind.tk_string))
>             {
>                 String typedValue = value.extract_string();
>                 pstmt.setString(++index, typedValue);
>                 System.out.println(typedValue);
>             }
>             else if(any_kind.equals(TCKind.tk_double))
>             {
>                 double typedValue = value.extract_double();
>                 pstmt.setDouble(++index, typedValue);
>             }
>           }

>           int rowsAffected = pstmt.executeUpdate();
>           pstmt.close();
>           con.commit();

>           if(rowsAffected == 0)
>           {
>             System.out.println("Could not insert offer in table " +
> type);
>           }
>           else
>           {
>             System.out.println("Inserted " + rowsAffected + " offer in
> table " + type);
>           }
>         }
>         catch( Exception e )
>         {
>           System.err.println(e);
>         }

>         return retVal;
>     }

>   public void withdraw
>   (
>       java.lang.String id
>   ) throws
>   CosTrading.IllegalOfferId,
>   CosTrading.UnknownOfferId,
>   CosTrading.RegisterPackage.ProxyOfferId
>   {
>     // IMPLEMENT: Operation

>     // Design Note:
>     // A client application or the Trading Service administrator can
> remove individual offers
>     // one at a time using the unique identifier associated with each
> offer. Exporters that
>     // will eventually need to clean up

...

read more »

 
 
 

Row insertion causes corruption in data

Post by Lee Fesperma » Sat, 06 Nov 1999 04:00:00



> .....  I am also concerned that your string may have
> characters that may not be printable or storable in the DBMS.
> If any are 16-bit characters, you should at least use nvarchar.

I think that is probably the problem.

--
Lee Fe*an, FFE Software, Inc. (http://www.veryComputer.com/)