How do I connect to SQL Server 2016 in PowerBuilder Classic 12.5.2?

3
votes

I am supporting a legacy application written in PB Classic that has a hardcoded connection string using SQL Server Native Client (SQLNCLI10). However, the Native Client is not supported beyond SQL Server 2012. It works in 2014, but it isn't supported. We have several databases that are now running SQL Server 2016 that cannot use SQLNCLI10 or SQLNCLI11.From what I understand, we can roll out an update to the native client as long as we don't intend to use any of the features in SQL Server 2016 but that seems like more of a stop-gap measure than a real solution. However, when I attempt to change the DBMS from native client to use the new ODBC drivers using a FileDNS entry in DBParm, the client always prompts for the location of the DSN file and other connection properties.

What is the best way to connect to SQL Server 2016 (preferably using the ODBC 13 for SQL Server 2016 drivers) in PowerBuilder Classic? This is what we're currently using (after scrubbing details):

sqlca.DBMS = 'SNC SQL Native Client(OLE DB)'
this.DBParm = "Database='" + as_database + "',Provider='SQLNCLI10',Identity='SCOPE_IDENTITY()',TrimSpaces=1,StaticBind=0,PBCatalogOwner='dbo', appname = 'My Application' , host =' " + lower(ls_machine_name) + "' "
1
sql-serverpowerbuilder
Here is my DBParm string. I have the exact same issue: SQLCA.DBParm = "ODBC;Driver=ODBC Driver 13 for SQL Server;DATABASE=" + ls_con_database + ";SERVER=" + ls_con_server + ";Trusted_Connection=YES;" The provider is msodbcsql13 - Matt Balent
@MattBalent I found this article that suggests placing single quotes after ConnectString=, but I haven't had the opportunity to test it yet and the article is a good 10 years old... nntp-archive.sybase.com/nntp-archive/action/article/… - Elsimer
Nope. The problem is all the information for a "DNS-less" connection string on the web is old and apparently does not apply to Sql Server 2016 - Matt Balent
Sorry, I mean "DSN-less" connections... - Matt Balent
seems like all the information for PowerBuilder on the web is old, it's not just DSN-less connections. According to the Help documentation, we're lucky the native client driver works beyond 2008R2! - Elsimer

1 Answers

2
votes

After some trial and error, I was able to connect using the following:

SQLCA.DBMS = "ODBC"
SQLCA.DBParm = "ConnectString='Driver={ODBC Driver 13 for SQL Server};QuotedId=No;TrustServerCertificate=Yes;Encrypt=Yes;Trusted_Connection=Yes;SERVER=" + SQLCA.ServerName + ";'"

I went a bit further and tried it with a SQL Authentication by adding UID= and PWD= into the DBParm replacing the Trusted_Connection portion of the string. I also added the Database parameter (the initial string was just making a connection to the default master database). Like this:

SQLCA.DBParm = "ConnectString='Driver={ODBC Driver 13 for SQL Server};QuotedId=No;TrustServerCertificate=Yes;Encrypt=Yes;UID="+SQLCA.Logid+";PWD="+SQLCA.LogPass+";SERVER=" + SQLCA.ServerName + ";Database="+SQLCA.Database+";'"