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:
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 |
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. |
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? Regarding We're going to need a bit more detail. For problem 1: Table DIRprcom(Provider Comments) 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: // Start PowerScript code snippet // Adjudicate each line item & calculate line item payable amount. For problem 3:
(30 Mar '12, 08:50)
KevinG
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;
(03 Apr '12, 14:20)
Chris Keating
|
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.
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.