SetTransobject Fails

[Former Member]

Hello,

I am trying to setup a db connection in runtime to SQL Server 2014 database that reside on a different Server. The connection is successful but when I try to set the transaction object for a datastore, it Fails.

I am using PB Classic 12.6.3506. Also, I am able to set up this DB connection in the PB IDE connections and can create dataobject against it and/or run queries. Also, I can successfully issue Direct SQL against this transaction object in runtime.

Any ideas?

Thanks in advance!

Shai

[Former Member]

Double check the dbparm string. You may need to use semi colons (;) in place of some of the commas (,) you show.

[ricardojasso]

So try using the same format when populating the properties of your transaction object SqlProd to see if there's any difference.

SqlProd.DBMS ="SNC SQL Native Client(OLE DB)"

SqlProd.servername ='ServerName'

SqlProd.logid ="SomeUser"

SqlProd.logpass ="Password"

SqlProd.autocommit = True

SqlProd.dbparm ="Database='dba',Provider='SQLNCLI10',Release='15', DisableBind = 1, AppName = '" + gnv_myapp.of_getappname( ) + "'" + ", Host = '" + gnv_myapp.of_getcomputername() + "'"

Also, I think you missed a last apostrophe after the computer name. I included it here.

[Former Member]

SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
SQLCA.LogPass = <***********>
SQLCA.ServerName = "My Server"
SQLCA.LogId = "GenericUser"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Database='dba',Provider='SQLNCLI10'"

This connect successfully.

[ricardojasso]

Can you show us the database connection syntax from the Preview tab for the PB IDE connection?

When using this driver I always set the database parameter inside the DBParm property:

SQLCA.DBParm = "Database='mydb',Provider='SQLNCLI10'"

[Former Member]

Did you assign the DataObject to the DataStore variable first?

DataStore lds_test

lds_test = Create DataStore

lds_test.DataObject = "d_test"

li_rc = lds_test.SetTransObject(SqlProd)

[Former Member]

Yes I did.

[Former Member]

Here is the code where I setup the connection Transaction variables:

SqlProd.DBMS         =  "SNC SQL Native Client(OLE DB)" 
SqlProd.database = "dba"
SqlProd.logid = "SomeUser"
SqlProd.logpass = "Password"
SqlProd.autocommit = True
SqlProd.dbparm = "Release='15', DisableBind = 1, AppName = '" + gnv_myapp.of_getappname( ) + "'" + ", Host = '" + gnv_myapp.of_getcomputername() 

SqlProd.servername = 'ServerName'

// Connect to the database using the transaction object we just initialized.

CONNECT USING SqlProd; (this returns 1)

[Former Member]

I forgot to mention....I am still testing some changes. It fails in debug.

Not much to show....return code is -1.

li_rc = lds_test.SetTransObject(SqlProd)

Shai

[Former Member]

If SetTransObject fails, then Roland's suggestion is likely. The other thing to check is that you have created and connected SqlProd BEFORE you assign it to your datastore. Did you also declare SqlProd as a global variable?

And the values you set in the transaction object look highly suspicious. The value "Release='15' is not familiar to me but I think it is Sybase-specific. I wasn't aware that you could use SNC to connect to anything other than MS SQL Server. The values you posted as that in your code don't match the values you posted as the ones in the preview tab of the IDE. Using SNC you have to specify the database in dbparm; the database attribute is not used. And you need to set the servername attribute - just like the preview tab.

[Former Member]

Show some code and define exactly what "fails" means. If the application crashes, then that implies your datastore reference is not valid.

[Former Member]

Are you sure that the DataWindow object was included in the executable? You might have to use a PBR file to force it's inclusion.