Question Re: Proxy table in Technology Q&A

Proxy table

Former Member — Wed, 06 Mar 2013 22:28:30 GMT

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';

Re: Proxy table

thomas_duemesnil — Thu, 07 Mar 2013 05:17:19 GMT

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.

Re: Proxy table

Former Member — Thu, 07 Mar 2013 06:37:24 GMT

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

Re: Proxy table

Breck_Carter — Thu, 07 Mar 2013 08:38:13 GMT

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

Re: Proxy table

Former Member — Thu, 07 Mar 2013 09:08:42 GMT

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.

Re: Proxy table

Former Member — Thu, 07 Mar 2013 13:49:09 GMT

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.

Re: Proxy table

Former Member — Thu, 07 Mar 2013 20:39:02 GMT

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';

Re: Proxy table

Former Member — Thu, 07 Mar 2013 20:46:10 GMT

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

Re: Proxy table

Former Member — Mon, 11 Mar 2013 11:05:01 GMT

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.

Re: Proxy table

Former Member — Tue, 12 Mar 2013 08:09:37 GMT

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 ?

Re: Proxy table

reimer_pods — Tue, 12 Mar 2013 09:03:14 GMT

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.

Re: Proxy table

Former Member — Tue, 12 Mar 2013 09:50:17 GMT

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