Proxy table

[Former Member]

I have testing SQLAnywhere12(3457) & 16(1324) by Windows 7 Build 7601 Service Pack 1 on X86_64 I am connecting Oracle10g & Oracle11g by ODBC

I am able to create Remote Server & Proxy tables and read data from proxy table. but problem is if I put condition to retrieve data, database die while I am using SQLAnywhere12(3840) & 16(1324) but if I use SQLAnywhere12(3457) I don't encounter this problem

CREATE EXISTING TABLE "DBA"."INWARD"(
  "OWNER_CODE" VARCHAR(12) NOT NULL,
  "ASN_NO" DECIMAL(8,0) NOT NULL,
  "WHS_CODE" VARCHAR(8) NOT NULL,
  "ASN_DATE" TIMESTAMP NULL,
  "ETA_DATE" TIMESTAMP NULL,
  "PO_NO" VARCHAR(24) NULL,
  "STATUS_CODE" VARCHAR(1) NOT NULL)
 AT 'wms;;WMS_DATA;INWARD';

[Breck_Carter]

Please show us the exact query that causes the problem, and the exact message you get ("database die" doesn't tell us much).

[Former Member]

select * from inward working fine. but select * from inward where owner_code='NAGASE' doesn't work, while issuing SQL, I am opening SQL DBSRV in screen, all sudden disappear, not able to find in Task Manager as well, if I connect from SQLCentral, not able to connect.

[Former Member]

I'm sorry. I just tried a few simple tests but failed to reproduce the problem that you are seeing. Could you please provide a full repro including information on exactly what statement was used to create the table in Oracle. We do not need the exact data, but the Oracle schema/definition may be important here.

Another thing we need to know is which Oracle driver are you using? Are you using the iAnywhere Oracle ODBC Driver or one provided by Microsoft or some other vendor? Keep in mind that the server will crash if the underlying driver crashes so this may be a driver bug not a server bug. The fact that you did not see the problem with build 3457 could simply be due to the fact that we have made many fixes in the Remote Data Access layer to ensure VARCHAR2 data gets handled correctly. Maybe the driver does not handle that very well. If you are not using the iAnywhere Oracle ODBC Driver, then it might be worthwhile to switch to the iAnywhere driver and see if that resolves the issue.

Either way, we need this extra information before we can proceed.

[Former Member]

Thank you for your reply To isolate problem(previously I connected Oracle10g using Oracle ODBC 11g driver), I have connected Oracle 11g using Oracle ODBC, ODBC Data Source administrator Company Oracle Corporation, Version 11.02.00.01, Name Oracle in OraClient 11g

I have created database I have created Remote Server which refer to correct ODBC Datasource I have created Proxy Table JOB Proxy table creation success from dbisql or SQLCentral view JOB table select * from job

die in a second without condition

Oracle schema is UNID NUMBER(18,0) BIZTYPE VARCHAR2(2 BYTE) JOBNO VARCHAR2(20 BYTE) JOBDATE DATE PARTYID_CUST VARCHAR2(10 BYTE)

Proxy table definition from GUI CREATE EXISTING TABLE "DBA"."JOB" ( "UNID" DECIMAL(18,0) NOT NULL, "BIZTYPE" VARCHAR(2) NULL, "JOBNO" VARCHAR(20) NULL, "JOBDATE" TIMESTAMP NULL, "PARTYID_CUST" VARCHAR(10) NULL ) AT 'kff;;ALSM;JOB';

[Former Member]

Sorry, I use SQLAnywhere16 1324 Core it-2500 CPU 3.3GHz 4.00GB Memory 64bit Operating System Windows 7 Enterprise

Right now I define only 1 Remote Server, 1 Proxy Table no other definition in the database

[Former Member]

I created an Oracle table with exactly the same schema as what you detailed above, populated the table with some sample data, created a proxy table using SA 16 with exactly the same schema as what you detailed above, and then was able to successfully query the proxy table with and without conditions. I do not have the specific Oracle ODBC Driver that you are using and have not had a chance to install that driver and give the test a try. However, I am wondering if you tried using the iAnywhere Oracle ODBC Driver? If you did not try the iAnywhere Oracle ODBC Driver, then please give that driver a try and let us know if the crash persists.

[Former Member]

iAnywhere Oracle ODBC Driver While I am installing SQLAnywhere 12 or 16, do I need to choose (I chose select all to the PC) I am not able to see iAnywhere driver from ODBC Admin From where I am able to get it ?

[reimer_pods]

If you're using 64 bit Windows, there are 2 versions of the ODBC admin tool: in %SystemRoot%\\System32\\odbcad32.exe (which is the 64 bit version in spite of all the 32 stuff) and %SystemRoot%\\SysWOW64\\odbcad32.exe (the 32 bit version). Depending on the bitness of the installed driver, it will show only in one of them.

[Former Member]

Apologies. The names of the drivers are a little different from what I said in previous posts. For SA 12, the driver appears as "iAnywhere Solutions 12 - Oracle" and for SA 16, the driver appears as "SQL Anywhere 16 - Oracle".

[thomas_duemesnil]

I had some problems in regard to numeric columns with Oracle. Can you try to use a integer for ASN_NO ? Does this make a difference ? Only a guess.

[Former Member]

I'm sorry, I did unsigned integer, timestamp change to datetime as well I have applied latest EBF but dbsrv disappear after I execute SQL program with condition.

Regarding SQLAny16, I have done 2 test 1. using existing DB to migrate to 16. 2. I have created database by SQLCentral using 16, create remote server, proxy table (only one) to isolate problem but still same result.

Right now I am not able to apply EBF to SQLAnywhere 12(3457 is using) 3457 never die so far.

Thank you for your advice