|
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. |
|
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. 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
(03 Dec '12, 23:34)
Dieter S
Replies hidden
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?
(04 Dec '12, 03:42)
Volker Barth
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
(04 Dec '12, 19:48)
Dieter S
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'
(04 Dec '12, 20:00)
Dieter S
Does it work when you specify class 'SAODBC' instead of the generic ODBC server class?
(06 Dec '12, 04:55)
Volker Barth
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.
(06 Dec '12, 20:16)
Dieter S
1
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?
(07 Dec '12, 13:57)
Jeff Albion
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
(09 Dec '12, 23:14)
Dieter S
More comments hidden
|
|
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' 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
(10 Dec '12, 18:40)
Dieter S
Replies hidden
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...
(11 Dec '12, 03:48)
Volker Barth
Comment Text Removed
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
(11 Dec '12, 19:29)
Dieter S
1
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.
(12 Dec '12, 10:18)
Karim Khamis
Fair enough it's just annoying it work's with PB without any hassles
(12 Dec '12, 18:24)
Dieter S
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 !!!
(21 Mar '13, 02:38)
Dieter S
Can you set Cis_option again and see if the problem is still with the SQLTables call?
(21 Mar '13, 08:08)
Karim Khamis
|
HTH Volker |
