The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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.

asked 08 Nov '12, 19:01

Dieter%20S's gravatar image

Dieter S
31226
accept rate: 0%

edited 06 Dec '12, 20:18


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.

permanent link

answered 09 Nov '12, 06:06

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

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
showing 5 of 8 show all flat view

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'

permanent link

answered 10 Dec '12, 10:52

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

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
showing 1 of 7 show all flat view
  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

permanent link

answered 21 Mar '13, 05:17

Volker%20Stoeffler's gravatar image

Volker Stoef...
106127
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×124
×46

question asked: 08 Nov '12, 19:01

question was seen: 2,266 times

last updated: 21 Mar '13, 08:08