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 »

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

read more »

I think that is probably the problem.

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