JDBC errors not experienced under ODBC

[Former Member]

I am able to connect my classic PB12.5 app to my SQLAnywhere 12.1 db using using either ODBC or JDBC. I do not want to go back to ODBC, but, I'm getting concerned that I will have to if I cannot get JDBC to work properly. I have 3 problems under JDBC that I do not have under ODBC. Under JDBC:

1. TRAILING SPACES - I get "row changed between retrieve and update" message on long strings frequently with trailing spaces. Not as likely to happen on short strings with trailing spaces. ( I have not found a work around for this and even if I did, I'm concerned that I would have to code it so many places. )

2. DECIMAL ROUNDING - Decimals returned from stored procs are rounded to nearest whole number. I can get around this if I initialize my decimal values to 0.00 instead of 0.

3. CREATE NEW USERS: This code does works fine under ODBC, does not work under JDBC.

CODE

// Grant connect, resource, and membership privileges to the new user
// while we have dba privileges.

ls_runstring1 = "grant connect to " + ls_userid + " identified by " + ls_pw
ls_runstring2 = "grant resource to " + ls_userid
ls_runstring3 = "grant membership in group " + ls_group + " to " + ls_userid

execute immediate :ls_runstring1 using itrans_temp_dba;
execute immediate :ls_runstring2 using itrans_temp_dba;
execute immediate :ls_runstring3 using itrans_temp_dba;

// Disconnect from the dba connection and destroy the temporary
// transaction object instance.95

I am using Driver='sybase.jdbc4.sqlanywhere.IDriver'.

SQLCA.dbparm = "URL='jdbc:sqlanywhere:DBN=" + ls_server + ";UID=" + trim(sle_userid.text) + ";PWD=" + string(ls_dbpassword)  +  ";ServerName=" + string(ls_ServerName)  +  ";ASTART=NO;Host=" + string(ls_Host)  +  "',Driver='sybase.jdbc4.sqlanywhere.IDriver'"

Any help would be much appreciated, I don't want to go back to ODBC, but, the app must work consistently.

3.3.4.6.3.95

[Former Member]

I've converted a second PB app to PB12.5 with SQLanywhere 12.1 DB. In this app, we are also experiencing the "row changed between retrieve and update" error when text with trailing spaces is input using JDBC connection. We get no datawindow error when updating connecting via ODBC. I can work around the other issues reported, but this one is a real problem.

Anyone able to duplicate this problem? I could supply a small PB app and a DB to demonstrate the problem.

[chris_keating]

If you have a repro, please either open a Case-Express "Submit a bug report" case or drop me an email at <last_name>@sybase.com). I am just starting to look in detail at the problems you have described.

[chris_keating]

Problem 1 Trailing Spaces

PB controls this with the TrimSpaces dbparm. It appears that the defaults differ between ODBC and JDBC . From the PB documentation: "For JDBC, the default values depend on the PBTrimCharColumns value in the registry. For ODBC, the default values depend on the PBTrimCharColumns value in the PBODBnnn.INI file. (If the PBTrimCharColumns keyword is missing for a particular database connection, the default value for the ODBC interface is TrimSpaces=0.)"

To resolve this issue, change your dbparm to include TrimSpaces as in:

"Driver='sybase.jdbc4.sqlanywhere.IDriver',
     URL='jdbc:sqlanywhere:eng=myDB;uid=dba;pwd=sql',TrimSpaces='No'"

Problem 2: Decimal Rounding

From the PowerBuilder documentation: A number, enclosed in braces, specifying the number of digits after the decimal point. If you do not specify a precision, the variable takes the precision assigned to it in the script.

The code sample showed that the variable was declared as

decimal d = 0

so if was being treated as Decimal{0}. You can see this effect when you change the value from 0 to 0.0 0.00 0.000 0.0000 0.00000

the return values adjust to reflect this.

I am not sure why with ODBC the behaviour is inconsistent with the documentation. Either way, the issue does not appear to be in the SAJDBC driver. I tested outside of PB in a Java application and the value is returned from the procedure without any modification.

[Former Member]

We're going to need a bit more detail.

For problem 1:

What does the schema look like? SQL Anywhere stores precisely what you give it, regardless of interface and regardless of whether or not the database is blank-padded or if the field is CHAR or VARCHAR. Unlike ASE, SQL Anywhere will also store trailing blanks and these are an inherent part of the value.

For problem 2:

What is the data type used for the column? How are you fetching it in your Java application?

For problem 3:

When you say "Does not work", what do you mean? Do you get an error?

[Former Member]

Regarding We're going to need a bit more detail.

For problem 1:
Q: What does the schema look like?
A: So far, all reported problems have been with fields that are varchar in the database. The smallest 500varchar, the largest 2000varchar. In trying to recreate the problems in our test environment, I get the problem when I use strings with 40+ characters and end the string with 1 or more spaces. When I use short stings ending in space, it often works (“A “ doesn’t cause a problem.)

Table DIRprcom(Provider Comments)

pr_id   1   varchar(9) /Key
pr_id_suf   2   varchar(6)
pr_comment  3   varchar(2000)  /Problem field.

pr_comuid   4   varchar(8)
pr_comudt   5   timestamp

SQL Anywhere stores precisely what you give it, regardless of interface and regardless of whether or not the database is blank-padded or if the field is CHAR or VARCHAR. Unlike ASE, SQL Anywhere will also store trailing blanks and these are an inherent part of the value.

For problem 2:
Q: What is the data type used for the column?
A: I declare it as decimal in my Powerscript and in my db stored proc.

Q:How are you fetching it in your Java application?
A: I initialize it to 0 then I call the stored proc from my Powerscript.

// Start PowerScript code snippet
// Adjudicate each line item & calculate line item payable amount.

decimal     lc_line_payable_amt…

//lc_line_payable_amt = 0.00   this line will get around the problem if the following line is commented out.  
lc_line_payable_amt = 0
    …
// call stored proc
SQLCA.adj_line( gs_usertype,    &…
lc_line_payable_amt, &…
)…
// End PowerScript code snippet
// When using ODBC, returns accurate payable amount if payable amount contains cents.  If switched to JDBC, lc_line_payable_amt rounds to nearest whole dollar.

For problem 3:
Q: When you say "Does not work", what do you mean? Do you get an error?
A: I will need to collect the specific info. //We can work around this.

[chris_keating]

Problem 2: Can you expand this a bit more? I am unable to see this problem with a datawindow or the following powerscript code:

decimal{2} v;

declare foo_proc procedure for foo using sqlca; execute foo_proc; fetch foo_proc into :v;

calling a procedure defined as

CREATE PROCEDURE "DBA"."foo"( ) BEGIN declare v decimal (5,2); set v = 1.23; select v from dummy; END

Problem 3: I am able to get this code to work with both JDBC and ODBC connections. What specifically do you mean by "not work"? Can you check for errors ( trans_obj.SQLErrText ) after each statement? Here is the code that I am testing:

// Grant connect, resource, and membership privileges to the new user // while we have dba privileges. string sql string userid = "u" string pwd = "p" string grp = "g"

if( rb_1.checked ) then userid += "_jdbc" else userid += "_odbc" end if

sql = "grant connect to " + userid + " identified by " + pwd execute immediate :sql using sqlca; if( sqlca.SQLCode <> 0 ) then MessageBox( "User creation error", sqlca.SQLErrText ) end if

sql = "grant resource to " + userid execute immediate :sql using sqlca;

sql = "grant membership in group " + grp + " to " + userid execute immediate :sql using sqlca;