1. Aron Cox
  2. PowerBuilder
  3. Tuesday, 26 February 2019 12:08 PM UTC

PowerBuilder 2017 R2 Build 1769

SQL Server 2012

I am working with a fairly old application that has been using the SQL Server ODBC Driver (SQLSRV32.DLL) for many years. We wish to upgrade to a newer driver so we can utilise some of the features unsupported by this driver, e.g TLS encryption of data connection.

I have installed the ODBC Driver 17 for SQL Server (MSODBCSQL17.dll). Most things work just fine, but there is some embedded SQL that doesn't. it turns out it is due to the way the parameter datatypes are being calculated.

If I do a SQL Profiler trace with the old driver I can see FMTONLY is used and it calculates the variable is a VARCHAR(2), as evidenced in the next sp_prepexec call.

With the new driver FMTONLY isn't used, instead it uses sp_describe_undeclared_parameters which returns VARCHAR(8000) and then uses TEXT in the next sp_prepexec.

This causes things to blow up as TEXT can't be compared to VARCHAR in the SQL Statement.

I'm not sure what PowerBuilder is doing and what the ODBC driver is doing. I assume this is all because of the driver change, but I have a few questions someone may be able to answer:

* Am I right in assuming it is not PowerBuilder that decides whether a variable type in embedded SQL is varchar or text, but rather the ODBC driver itself? i.e. Is PowerBuilder calling sp_prepexec or is the driver?

* Anyone run across this kind of thing before and can give me some hints of moving between drivers?

* Is there any way of telling what is sent by PowerBuilder and what is sent by the driver?

 

Thanks for any help :)

 

Accepted Answer
Aron Cox Accepted Answer Pending Moderation
  1. Wednesday, 27 February 2019 11:51 AM UTC
  2. PowerBuilder
  3. # Permalink

Turns out there is a setting in the new ODBC Driver called "Use FMTONLY metadata discovery" which solves this issue!

Now all I have to do is see what other issues there are.

Comment
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Tuesday, 26 February 2019 14:26 PM UTC
  2. PowerBuilder
  3. # 1

Try using OLE DB connection and see if that works differently.

MSOLEDBSQL is the new provider, SQLOLEDB is the old one.

 

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 26 February 2019 19:13 PM UTC
  2. PowerBuilder
  3. # 2

Hi Aron;

   Did you try using a PB SQL trace ( SQLCA.DBMS = "TRACE ODBC" ) and then an ODBC SQL trace and then compare the two? The PB trace is what the PB ODBC driver is doing and the ODBC trace will show what the MS driver is doing.

Regards ... Chris

Comment
There are no comments made yet.
Aron Cox Accepted Answer Pending Moderation
  1. Wednesday, 27 February 2019 10:12 AM UTC
  2. PowerBuilder
  3. # 3

Thanks Chris. I haven't tried that, I will give it a go!

Comment
There are no comments made yet.
Aron Cox Accepted Answer Pending Moderation
  1. Wednesday, 27 February 2019 10:32 AM UTC
  2. PowerBuilder
  3. # 4

Thanks Roland, I'll take a look. I expect it to work in the same way as the new ODBC Driver, as FMTONLY has been deprecated, but it's worth a try :)

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.