Row insertion causes corruption in data 
Author Message
 Row insertion causes corruption in data

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 »



Tue, 23 Apr 2002 03:00:00 GMT
 Row insertion causes corruption in data

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
Quote:

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



Tue, 23 Apr 2002 03:00:00 GMT
 Row insertion causes corruption in data

Quote:

> .....  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{*filter*}an, FFE Software, Inc. ( http://www.***.com/ )



Tue, 23 Apr 2002 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Row insertion causes data corruption

2. PRINT statement causing data corruption

3. Archive logs causing data block corruption !!??

4. Question - Cause of DATA BLOCK Corruption?

5. accessing row data that caused insert trigger

6. Difficulty in data insertion of text data type

7. ADO With Win2000 causes Heap corruption?

8. SCO superblock corruption--Caused by Unidata??

9. NULL passed into function causes memory corruption

10. Error 605: What causes the corruption exactly?

11. Connection::Close causes heap corruption?

12. Update causing corruption


 
Powered by phpBB® Forum Software