• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • SQL server connection and SQLCA.DBMS / SQLCA.DBParm

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

Resolved SQL server connection and SQLCA.DBMS / SQLCA.DBParm

  1. How-to
0
Votes
Undo
  1. Haakon Langballe
    Haakon Langballe
  2. PowerBuilder
  3. Wednesday, 18 November 2020 14:12 PM UTC

Hi.

I have struggled with getting the SQLCA properties set up correct to be able to have my users connect to data base with single sign-on/trusted connection. I'm on MS SQL Server.

The problem boiled down to using correct value for SQLCA.DBMS. Thank to a thread here i found that I could use SNC for SQL Native Client. Also ADO and ODB is working fine on my developer machine. (I also learned to add TRACE before the provider name. Very useful!)

I think what values are working relies on what providers are installed on the computer.

I failed to find any documentation that describes in detail the valid values for SQLCA.DBMS and what corresponding software/drivers/providers need to be installed on the users computers.

 

This is the code I use to connect to the database:

SQLCA.DBMS = 'SNC'
SQLCA.DBParm = "Namespace='System.Data.SqlClient'"
SQLCA.DBParm += ",DataSource='server_name'"
SQLCA.DBParm += ",Database='db_name'"
SQLCA.DBParm += ",TrustedConnection=1"

//I found that setting LogId and LogPass to empty string was not necessary, but maybe safer?
//SQLCA.LogId = ''
//SQLCA.LogPass = ''

 

 Are there any docs or tutorials around that goes into details on this subject?

 

Thanks

Haakon

  1. Database
  2. Database Driver
  3. SQL Server
XML RibbonBar
oleobject.Selection.typetext(sVal) function is get...
Responses (2)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 5 January 2021 22:54 PM UTC
  2. PowerBuilder
  3. # 1

Greetings, Haakon -

In addition to the excellent online publications resources Govinda has pointed out, I hope you will find the following information helpful:

The three-character value of the SQLCA.DBMS property corresponds to the PB database driver's runtime DLL, so "SNC" -> pbsnc190.dll

Look in PB Help (F1 from within the PB IDE) for topic "runtime files". At the top of this Help topic is a link to "Database Connections" topic. At the top of this topic is a link to "Native Database Drivers" topic.

For the MS SQL Native Client provider, this is a Microsoft-provide component and it must also be installed separately on any machine that connects to MS SQL Server using this provider.

We set up SQL Server to use Windows Authentication for our users so that they do not have a separate database ID/Password. In setting up a DB Profile in the PB IDE for SQL Server, here are a couple of things we use:

On the Connection tab: No User ID or Password values. Identify the server name and instance and the database, and then:

Here are the options we use on the Syntax tab (your needs/values may differ):

Once you have a DB Profile configuration that connects successfully, the Preview tab shows how you can set the SQLCA properties in the same manner.

HTH

Regards, John

Comment
There are no comments made yet.
Govinda Lopez @Appeon
Govinda Lopez @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 5 January 2021 21:25 PM UTC
  2. PowerBuilder
  3. # 2

Hi Haakon,

 

Following you may find a list of supported database interfaces: https://docs.appeon.com/pb2019r2/connection_reference/ch02s01.html

 

And here you can find a complete list of all the Database parameters (DBParm values): https://docs.appeon.com/pb2019r2/connection_reference/ch01s01.html

 

I hope this helps.

 

 

Regards,

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.