• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Connect to MS SQL Server Database in the application runtime

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Connect to MS SQL Server Database in the application runtime

  1. Issue
0
Votes
Undo
  1. Jon Stoller
    Jon Stoller
  2. PowerBuilder
  3. Wednesday, 1 November 2017 18:24 PM UTC

I am upgrading my application from PowerBuilder v7 to PowerBuilder 2017.  The PBLs are in the new version and the application compiles/deploys successfully.  I should mention that I get a message at the end of the compiling that says "Invalid Executable File Name", but it creates a new executable and new PBDs.  Besides that message it seems OK. 

My problem now is the runtime doesn't connect to the database.  When I run the application it does not connect to the database.  

The database is a Microsoft SQL Server database.  I have version 2016 here.  My clients all use version 2008R2. 

The application code is as follows:

pt_sqlca.dbparm = "PROVIDER='SQLNCLI11SQL',DATASOURCE='" + pt_sqlca.Servername + "',PROVIDERSTRING='DATABASE="+pt_sqlca.Database+"'"
connect using pt_sqlca;

The actual content of pt_sqlca.dbparm is:
PROVIDER='SQLNCLI11SQL',DATASOURCE='aka1001',PROVIDERSTRING='DATABASE=popss'

I tried changing the Provider to SQLNCLI and SQLNCLI11.  It did not work with either one.
In PBv7 it says  PROVIDER='SQLOLEDB'.  I tried SQLOLEDB in PowerBuilder 2017 also, but that also didn't connect. 

The other parameters are:
pt_sqlca.LogID      = "aka"
pt_sqlca.LogPass    = "aka"
pt_sqlca.UserID     = "aka"
pt_sqlca.DBPass     = "aka"
pt_sqlca.Database   = "popss"
pt_sqlca.Servername = "aka1001"
pt_sqlca.Dbms       = "SQLNCLI11SQL"
pt_sqlca.lock     = "RU"

After the attempted connect, the return code, pt_sqlca.sqlcode = -1 

What am I doing wrong?  What do I have to do to connect?

--------------------------------------------------------------------------------------------

The application connects to the database from inside PowerBuilder.  The DB Profile Preview is:

SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
SQLCA.LogPass = <***>
SQLCA.ServerName = "aka1001"
SQLCA.LogId = "aka"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Database='popss',Provider='SQLNCLI11SQL'"

If you need other information from me, let me know and I will send it. 

 

 

 

  1. ASE
Problems with Source Control
Use PowerBuilder from .Net project?
Responses (3)
Shenn Sellers
Shenn Sellers Accepted Answer Pending Moderation
  1. Wednesday, 1 November 2017 18:38 PM UTC
  2. PowerBuilder
  3. # 1

Why not copy the connection information from the DB Profile and use that as your code?  Just change it to use your Transaction object.

Comment
Jon Stoller
  1. Jon Stoller
  2. Thursday, 2 November 2017 00:10 AM UTC
Thank you for your help with this.  The first thing I tried was using the parameters from the DB profile.  It didn't work.  The DB Profile Preview contains a line as follows:



SQLCA.DBParm = "Database='popss',Provider='SQLNCLI11SQL'". 



So, I tried using Provider='SQLNCLI11SQL' but it wasn't valid in the application run time.  I got a return error with the sqlca.sqlerrtext ="PBSNC only supports Provider SQLNCLI or SQLNCLI10 or SQLNCLI11".  I tried those also but they did not work either. 



It seems like it should work, but it doesn't connect.  At least I haven't gotten it to connect yet. 



Jon Stoller

  1. Helpful
There are no comments made yet.
Matthew Balent
Matthew Balent Accepted Answer Pending Moderation
  1. Wednesday, 1 November 2017 18:51 PM UTC
  2. PowerBuilder
  3. # 2

This is what I have used (using Windows Authentication)

DBMS: SNC SQL Native Client(OLE DB)

Server Name: PMPC\SQLEXP2012

AutoCommit: F

DB Parm: Provider='SQLNCLI11',TrustedConnection=1,Database='MyDB'

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 1 November 2017 19:26 PM UTC
  2. PowerBuilder
  3. # 3

Hi Jon;

  The reason for the SS connectivity issue is ...

Wrong:  pt_sqlca.Dbms       = "SQLNCLI11SQL"

Correct: pt_sqlca.Dbms       = "SNC"

 

HTH

Regards ... Chris

 

Comment
Load more comments
Roland Smith
  1. Roland Smith
  2. Thursday, 2 November 2017 12:57 PM UTC
You mispelled SQLNCLI11 for one thing. You should copy the settings from your db profile to the code.



The first 3 characters of sqlca.DBMS correspond to positions 3-5 of the interface dll. In your case, PBSNC170.DLL.

  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 2 November 2017 13:39 PM UTC
Hi Jon;



  Sounds like the SQL Server client is not installed on the PC maybe? All that PBSNC170.dll(SS Interface driver in PB) does is call the actual SS client driver. If it cannot find the SS Client - that is the type of error you would get.



Regards ... Chris

  1. Helpful
Jon Stoller
  1. Jon Stoller
  2. Friday, 3 November 2017 15:56 PM UTC
I got my application connecting to the database.  I haven't re-compiled yet, but it connects when I run it from PowerBuilder.  I'm writing to tell you not to spend any time on this for now. 



This is what I did.  For one, and I don't know if this matters, I changed the DB Profile.  I deleted the Interface from SNC SQL Native Client and set it up using OLE Microsoft OLE DB.  By the way, it connects with either profile from the connect button in the DB Profile. 



Then I changed the pt_sql.Dbms parameter to OLE DB.  And I changed the connect string to the following:



 pt_sqlca.dbparm = "PROVIDER='SQLOLEDB',DATASOURCE='" + pt_sqlca.Servername + "',PROVIDERSTRING='DATABASE="+pt_sqlca.Database+"'"



The change is the provider is SQLOLEDB instead of SNC or SQLNCLI11, or the other variations of SQLNCLI I had been trying without success.



Now it is connecting. 



I don't understand the problem I was having with SNC SQL Native Client but for now I am just happy it is working with OLE DB.



Thanks for your previous help and the time you spent on it.



Jon Stoller - AKA Software Services

  1. Helpful
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.