Problem creating remote server to generic ODBC datasource

[Former Member]

I am trying to create a generic remote server using an ODBC dsn as a connection. I can use this datasource no problem with Powerbuilder however I need to specify a parameter. The connection in PB looks like look like

SQLCA.DBParm = "ConnectString='DSN=A040_network;UID=system;PWD=<**>',Block=1" 

Hence my problem is trying to specify this "Block" parameter when creating the remote server in sqlanywhere. I have tried all the usual things and am beginning to wonder if I am able to specify the Block parameter when creating a generic remote server ? Doe anyone know if this is possible ?

The help from PB states "For those interfaces that support it, Block specifies the cursor blocking factor when connecting to a database. The blocking factor determines the number of rows that a DataWindow object can fetch from the database at one time" Setting it to one turns it off.

[Former Member]

You might have better luck if you do not use Sybase Central. SC tries to query a fair bit of metadata before proceeding with the creation of the proxy table. It is possible the ODBC driver does not support all of that metadata. Try using DBISQL instead and simply issue something like:

CREATE EXISTING TABLE myTable at 'myServer;;;myRemoteTableName'

[Former Member]

I have tried using ISQL executing the SQl you have suggested it creates the server but does not allow the creation of a proxy table. I get the message Could not execute statement. The remote table could not be found SQLCODE=-666, ODBC 3 State="HY000"

Regardless of what I call the table. When any other tool returns data from the table via ODBC DSN

[VolkerBarth]

I'd suggest to turn on "Remote Data Access debugging", aka setting the CIS_OPTION, and then look in the server console what statements get sent to the remote server. Cf. the docs...

[Former Member]

Now I even more confused following your suggestion Volker from the server console in response to CREATE existing TABLE p_aircinfo AT 'xdbc_test...AIRCINFO';

I got Executing SQLTables(, , AIRCINFO) ODBC error (HYC00). [TOD][ODBC Driver]NOTCAP: Driver not capable SQLTables() failed (HYC00). [TOD][ODBC Driver]NOTCAP: Driver not capable

However

FORWARD TO xdbc_test {SELECT * from aircinfo};

Returned all rows and columns as expected

[Former Member]

It would appear that the underlying ODBC driver is not implementing the variety of metadata routines that are needed. Supporting calls like SQLTables() is fairly fundamental but your ODBC driver seems to not handle such calls. Performing a forward to {select ...} simply executes the select query on the other side, there are no SQLTables calls made in that situation so forward to is not an indication of anything in this case. I would consider asking the ODBC driver vendor why their ODBC driver is so limited in functionality. I doubt there is anything that can be done on the SQL Anywhere side to resolve this issue.

[Former Member]

Fair enough it's just annoying it work's with PB without any hassles

[Former Member]

Even more annoying now sa16 shows the tables and columns and I still get the -666 message that the remote table could not be found !!!

[Former Member]

Can you set Cis_option again and see if the problem is still with the SQLTables call?

[MCMartin]

I don't think, that you will need this parameter for the definition of the remote server, as it is a very PB specific thing. SQLA don't need it and you will likely connect with PB to SQLA and can set the parameter there.

[Former Member]

I don't actually want to connect with PB just ISQL to run some queries but I am not able too. I can create the server but no connection is possible testing the connection work but I can not get anything returned

[VolkerBarth]

As Martin has explained, "Block" is no SQL Anywhere connection parameter, so when not using PB, you won't need that parameter, and the problem seems to lie elsewhere. Can you show us your connection string and the statements you try to execute via remote data access?

[Former Member]

I am using sybase central and this is the sql that is being executed

CREATE SERVER "test" CLASS 'ODBC' USING 'A040_xdbc_64net' READ ONLY; CREATE EXTERNLOGIN "DBA" TO "test" REMOTE LOGIN 'system' IDENTIFIED BY '***';

The results of a test connection give [Sybase][ODBC Driver][SQL Anywhere]Server 'test': [TOD][ODBC][GENESIS]NOTCAP: Driver not capable SQLCODE: -660 SQLSTATE: HY000 SQL Statement: SELECT FIRST table_name FROM dbo.sp_remote_tables( 'test', NULL, NULL, NULL, 1 ) ORDER BY 1

The results of trying to add a proxy table via sybase central give

[Sybase][ODBC Driver][SQL Anywhere]Server 'test': [TOD][ODBC][GENESIS]NOTCAP: Driver not capable SQLCODE: -660 SQLSTATE: HY000 SQL Statement: CALL dbo.sp_remote_tables( 'test', NULL, NULL, NULL, 1 ) This does not change if I specify a table I know exists

[Former Member]

ie Could not execute statement. The remote table 'test...EACHPART' could not be found SQLCODE=-666, ODBC 3 State="HY000" Line 2, column 1

CREATE EXISTING TABLE EACHPART AT 'test...EACHPART'

[VolkerBarth]

Does it work when you specify class 'SAODBC' instead of the generic ODBC server class?

[Former Member]

No it does not. It is not a SQLAnywhere Datasource though it is from another vendor which is not one of the other class types available hence why I chose the generic one.

[jeff_albion]

Which ODBC driver are you trying?

 [TOD][ODBC][GENESIS]NOTCAP: Driver not capable

Googling would suggest this is the 'AcuXDBC' driver - is that correct? Which version of this driver are you trying?

If you create an ODBC trace, can you see which ODBC commands are being sent to the driver to see what's causing the specific 'Driver Not Capable' message?

[Former Member]

That is the correct driver(AcuXDBC) it is version 9.01. The trace does not seem to work no matter what I try. There is no output and no file created when using isql or sybase central. If I execute a simple query via msquery using the same data source. Data is returned and an odbc log is produced.

The only clue as to what is not working is the outou t in the server messages window in sybase central

Executing SQLTables(EACHPART, , ) ODBC error (HYC00). [TOD][ODBC Driver]NOTCAP: Driver not capable SQLTables() failed (HYC00). [TOD][ODBC Driver]NOTCAP: Driver not capable

[Former Member]

1. Block is a Powerbuilder property / parameter, not an ODBC one. The part of your DBParm related to ODBC is the ConnectString property or rather íts value (the part in single quotes). So, the basic approach with the remote server as described above should work.
2. If you only want to connect to that datasource using Interactive SQL (I hope you're referring to dbisql heren, not isql), why don't you change the database type in the connection dialog to "Generic ODBC Database" and connect to the data source directly?

HTH

Volker