1. Olan Knight
  2. PowerBuilder
  3. Tuesday, 15 June 2021 20:35 PM UTC

Resolution:

   Mark Goldsmith noticed that I was storing double quotes with the connection string information.
   Removing the stored double-quotes solved the problem!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PB2019R3

Dear Appeon:
   Internally in PB2019R3, what is the difference between a hard-coded string in the application, and a string variable that is created in script?


Example:
1)   I have a hard-coded string in gnv_appmgr.pfc_logon_env. When the code looks like this, a connection is made to the PostgreSQL database without prompting me for a DSN. This is the behavior I expect. Note the hardcoded value is set to sqlca.dbparms at the bottom of the snippet.

    SQLCA.AutoCommit   = False
    SQLCA.Database       = ""
    SQLCA.DBMS            = ls_dbms
    SQLCA.DBParm         = ""
    SQLCA.LogId            = as_userid
    SQLCA.LogPass         = as_password
    SQLCA.ServerName   = ls_ip_addr 

SQLCA.DBParm    = "ConnectString='Driver=PostgreSQL Unicode;Database=nttest;Server=10.50.68.244;Port=5444;UID=cabdevusr;PWD=cabdevusr;',DisableBind=1,UseServerSidePrepare=1,PBCatalogOwner='chadba',StripParmNames='Yes'"
   

2) When the DBPARM string is constructed (or read into the app) and CONNECT is called, I am prompted for a DSN.

// Get the connect string to be updated
    ls_DBParm = "ConnectString='Driver=<driver>;Database=<db>;Server=<ip>;Port=<port>;UID=<userid>;PWD=<password>;'

 Each token encased in angle brackets is then replaced, item by item. The resulting string is identical to the hard-coded string, but it prompts for a DSN when the CONNECT is called.


3) I put the hard-coded connection string into the Registry, read it into the app, and used that string in SQLCA.DBPARMS, and I was prompted for a DSN.


    ls_key = "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\CSGI\Logon\DBMS\PostgreSQL\nttest_PG"
    RegistryGet (ls_key, "sqlca_dbparms", RegString!, ls_DBParm)
    
    SQLCA.AutoCommit    = False
    SQLCA.Database        = ""
    SQLCA.DBMS             = ls_dbms
    SQLCA.DBParm          = ls_DBParm
    SQLCA.LogId            = as_userid
    SQLCA.LogPass         = as_password
    SQLCA.ServerName   = ls_ip_addr 


//----------  DEBUG -----------------------
ls_DBParm = "ConnectString='Driver=PostgreSQL Unicode;Database=nttest;Server=10.50.68.244;Port=5444;UID=cabdevusr;PWD=cabdevusr;',DisableBind=1,UseServerSidePrepare=1,PBCatalogOwner='chadba',StripParmNames='Yes'" 


Here's the actual code. This is the ONLY way I have been able to connect without being prompted for a DSN.

Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 16 June 2021 08:13 AM UTC
  2. PowerBuilder
  3. # 1

Hi Olan,

There shouldn't be any difference. If the resulting strings are exactly the same, you should connect.

Are you sure you aren't missing any single quote somewhere?
Is there an enter withing your string or is that just to show the contents in debug of the obtained string value?

regards.

Comment
  1. Olan Knight
  2. Friday, 18 June 2021 20:37 PM UTC
I have checked and double-checked and triple-checked, that the constructed string is EXACTLY the same as the hardcoded string.



I put the two strings into Notepad++ and used the VIEW ALL CHARACTERS option in case there were invisible characters messing me up. Nope...all the same.

  1. Helpful
  1. Olan Knight
  2. Friday, 18 June 2021 20:39 PM UTC
This has GOT to be an environment issues on my side.

  1. Helpful
There are no comments made yet.
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 16 June 2021 09:00 AM UTC
  2. PowerBuilder
  3. # 2

Hi Olan,

 

This issue seems to be caused by the incorrect key in Registry. I suggest you try the following:

 

Test 01: Use the variable ls_dbparm to assign value to sqlca.dbparm. Then test and see if it works.

ls_ DBParm = "ConnectString='Driver=PostgreSQL Unicode;Database=nttest;Server=10.50.68.244;Port=5444;UID=cabdevusr;PWD=cabdevusr;',DisableBind=1,UseServerSidePrepare=1,PBCatalogOwner='chadba',StripParmNames='Yes'"

SQLCA.DBParm    = ls_ DBParm

Connect using SQLCA;

 

 

Test 02: Get the key from Registry and compare it with ls_dbparm to see if they are consistent.

ls_ DBParm = "ConnectString='Driver=PostgreSQL Unicode;Database=nttest;Server=10.50.68.244;Port=5444;UID=cabdevusr;PWD=cabdevusr;',DisableBind=1,UseServerSidePrepare=1,PBCatalogOwner='chadba',StripParmNames='Yes'"

RegistryGet (ls_key, "sqlca_dbparms", RegString!, ls_ DBParm)

If ls_key <> ls_DBParm Then

Messagebox(‘’,’’)

End If

 

If they are inconsistent, reset the registry key and make it consistent with ls_dbparm.

 

 

Regards,

Ken

 

Comment
  1. Olan Knight
  2. Friday, 18 June 2021 20:39 PM UTC
Thanks, Ken,. See below - I have checked and re-checked, and even examined the invisible characters in the string - and the two are always the same - but the hard-coded string works and the others do not. *Sigh*



This has got to be caused by an environmental issue. Now to figure out WHAT !?!
  1. Helpful
There are no comments made yet.
Mark Goldsmith Accepted Answer Pending Moderation
  1. Saturday, 19 June 2021 14:20 PM UTC
  2. PowerBuilder
  3. # 3

Hi Olan,

I noticed in your screenshot, presumably that's coming direct from your Registry, that you have double-quotes at the beginning of the string (and while not seen, likely at the end too)...

...shouldn't that be stored without beginning and ending quotes? Otherwise, instead of just delimiting your string with quotes you are actually including double-quotes as part of sqlca_dbparms' value. Have I got that right?!

Regards,

Mark

Comment
  1. Olan Knight
  2. Tuesday, 22 June 2021 15:04 PM UTC
THAT WAS IT!!!!! THANK YOU!!!!

Too many double quotes were spoiling the string. Getting rid of them solved the problem!



THANK YOU!
  1. Helpful
  1. Mark Goldsmith
  2. Tuesday, 22 June 2021 19:35 PM UTC
You're welcome Olan and great to hear that resolved it! Thanks for the update!
  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.