Sybase ASA 17 2053 - ADO no longer returns parameter list

[Former Member]

Hi,

I have some very old code that has worked for years (originally written for Sybase ASA 7) but has stopped working since I upgraded from ASA 17 (build 1358) to ASA 17 (build 2053).

We use ADO 2.5 and the Sybase ASA 17 ODBC driver and this is VB6.

Set cmdStoredProc = New ADODB.Command cmdStoredProc.CommandType = adCmdStoredProc

'Open a connection Set cmdStoredProc.ActiveConnection = dePNUIT.conApollo 'linked to s sybase 17 asa ODBC dsn cmdStoredProc.CommandTimeout = 0

'We set the stored procedure we are going to use cmdStoredProc.CommandText = "sp_insert_table_A"

'And try and loop through the parameters For intParam = 0 To cmdStoredProc.Parameters.Count - 1

...But this now fails and it returns cmdStoredProc.Parameters.Count = 0

It does not return a parameter list and any attempt to assign to a parameter fails as it says the parameter cannot be found.

I tried upgrading to ADO 2.7 as per the documentation, but this failed too.

Is this still supported?

Thanks Sully

[Former Member]

Can I assume you haven't tried with the the original version 17 ODBC driver and the newer database and server software to see if is just a driver version difference of a server side phenomena?

It does sound like you may have not change anything in your application code. Does it also fail with the original database used with verions 17 when using the newer software? (assuming you rebuilt or upgraded the database as well)

This could be due to either permissions or an issue in the database metadata for that procedure. If the latter, dropping and recreating that procedure should resolve this. {if not we may need more details about the procedure definition and upgrade history}

If none of the above changes this, I would start by checking the output from these operations:

 1- In DBISQL.exe:  DESCRIBE PROCEDURE sp_insert_table_A;  //connected with same login as app.
 2- SELECT procp.* FROM SYS.SYSPROCEDURE proc join SYS.SYSPROCPARM procp 
           on proc.proc_id=procp.proc_id 
           where proc.proc_name='sp_insert_table_A'
 3- Does calling it explicitly work in dbisql?   call sp_insert_table_A(  . . . . )

Good luck and let us know what you find out

[Former Member]

Hi Nick.

Thanks for the reply.

I am working through those suggestions, I am not sure I can use the other version of the driver as I am not the main software using that driver and the main product will need to patch it.

I also tested it using SAOLEDB rather than the ODBC OLE driver and it works. Unfortunately I think this is Sybase version specific and so if they changed to Sybase 18 it would cause another change.

Thanks Michael

[Former Member]

The fact that SAOLEDB works is indeed good news. But that would indicate there could be an issue with MSDASQL either not recognizing the newer driver or a change in the ODBC driver itself.

I could well imagine the Microsoft OLE DB Provider for ODBC failing this way if it believes there is no support for stored procedures. The could be a problem in SQLGetInfo( ) or an edge-case insideof the generic provide suppport or a new API issue I don't recognize.

For these kinds of issues one would normally need 2 ODBC traces. One with the ODBC driver that works. One with the one that does not work. BUT it sounds like you have a way to mock this up. It may be best to work directly with SAP Product Support if possible.

[Former Member]

Hi Nick,

I can provide the 2 traces tomorrow. We would hope to not make changes to the application if possible, but it has been running for 15years so it was bound to happen at some point. What is the best way to contact support, I am new to the website.

Thanks Michael

[Former Member]

http://support.sap.com is the portal for support. You can try switch between the new launchpad and the older system whichever you find easier tow work with. You are required to launch a search attempt first but then can switch into creating a new incident. https://support.sap.com/support-programs-services/about/help-index/report-incident-launchpad.html

[jack_schueler]

There is a regression in one of the schema queries that the 17.0.4 ODBC driver issues. "PROCEDURE_OWNER" is a column name used when running in ODBC 2.0 mode. This column name conflicts with the PROCEDURE_OWNER special value that was introduced in 17.0.0. The quoting of the column name was inadvertently removed in 17.0.4 (17.0 build 2000).

A fix will be forthcoming.

A work-around is to revert to the older 17.0.0 ODBC driver.

[jack_schueler]

The correction will appear in 17.0 build 2088 (and later).

[former_SQLA_member1694883]

When will 17.0.4.2088 or higher be available to download as an EBF? We need this fix ASAP.

[chris_keating]

There is not a currently scheduled date for an SP containing this fix. You should consider the workaround in the interim.

[former_SQLA_member1694883]

Please would you confirm that to revert to the older 7.0.0.1358 driver, it is simply a case of swapping out the dbodbc17.dll file with the older build, there is nothing else required and/or no possible side effects from using mismatched builds. Many thanks.

[jack_schueler]

Back from vacation... Yes, replacing the driver DLL(s) for 64-bit (bin64) and/or 32-bit (bin32) will do it.

[former_SQLA_member1694883]

Are you able to offer any assurance that mixing an older ODBC driver build (1359) with the newer Bin32 folder build (2053) will not have any repercussions or other unforeseen side effects for anything else? Running mismatched builds seems like a recipe for unforeseen consequences to me and this combination will clearly not have been tested in any way whatsoever.

[VolkerBarth]

Robert, I guess another way to use both 17.0.4.2053 and the ODBC drivers from 17.0.0.1359 on the same box would be to make a custom install (possibly created with the Deployment Wizard) for the latter (i.e. just a client install with ODBC) and choose a different product name for the latter, such as "My SA 17 ODBC Install" and using a custom driver name. You would then create your ODBC DSN with that particular driver name...

Cf. that FAQ: What product name am I supposed to use in the Deployment Wizard?.

[jack_schueler]

Considering that folks have used ODBC drivers from different major releases against database servers from other releases with success, I don't think you are taking a big risk in using an ODBC driver that is a few builds earlier than the one containing the problem. When you use an ODBC driver from an earlier major release (say 16.0) against a server from a newer release (like 17.0), you do risk not be able to access some of the latest software features but since you are staying within version 17 releases, I can't think of any issue you might run into.

[former_SQLA_member1694883]

Thanks. That is probably as good assurance as is possible and, in combination with a being able to run both on versions on the same machine, is the best we have for now. Looking forward to the release of the proper fix in the next EBF, so we can remove this workaround.

[former_SQLA_member1694883]

For reference, this bug is now fixed in the recently released version 7.0.4.2100.

Cf. the CR #799815 in the according readme.