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

Resolved reading from the registry to connect to the db

  1. How-to
0
Votes
Undo
  1. Albert van Bochove
    Albert van Bochove
  2. PowerBuilder
  3. Wednesday, 20 September 2023 19:08 PM UTC

Hello,

in my application I am connecting to the database using the connection string that is defined in a Registry key, this is my code and as I show you in the attached images, you can see that when I debug my code I am receiving my transaction all the information correctly, my question is, if the registry key is well defined, because when the app reaches the line that says connect, it shows a window dialog for the user to choose the odbc connection that is already defined in the registry key ? I don't want the user to have to choose again the odbc connection that the app is going to use, I just want them to enter their username and password and the app to open correctly, I have an unwanted step, and I don't know how to remove it from above.

How to make this connection without that dialog to choose the odbc connection?

Attachments (3)
Q3.1.PNG Q3.1.PNG
Q3.2.PNG Q3.2.PNG
Q3.3.PNG Q3.3.PNG
  1. 32-bit
  2. Authentication
  3. Database
datawindow button.Background.Color with themes: th...
Sql Server - Row changed between retrieve and upda...
Responses (6)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 20 September 2023 20:10 PM UTC
  2. PowerBuilder
  3. # 1

Hi Albert;

  Make sure that you have this in your SQLCA.DBParm field before your App issues the "Connect;" command, as follows ...

SQLCA.DBParm = "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

HTH

Regards .. Chris

Comment
Berka Frenfert
  1. Berka Frenfert
  2. Tuesday, 27 February 2024 13:45 PM UTC
just hard code dbparm and test it to remove the system odbc dialog . 99% chances are your dbparam is either not set or set wrong format.

Getting setting registry is secondary step.
  1. Helpful
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Thursday, 21 September 2023 07:07 AM UTC
  2. PowerBuilder
  3. # 2

Hi.

You are not setting a dsn on your dbparm...

This is how looks the dbparm to connect to "PB Demo DB V2022":

// Profile PB Demo DB V2022R2
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=PB Demo DB V2022R2;UID=dba;PWD=<******>'"

Your sqlca.dbparm seems to be empty... As Chris mentioned, are you setting correctly this information?

Are you setting it from registry? If yes, then that information doesn't show up in registry...

Andreas.

Comment
Load more comments
Albert van Bochove
  1. Albert van Bochove
  2. Friday, 16 February 2024 18:19 PM UTC
Maybe my code can helps reading it:

STRING ls_Section, ls_AppName

BOOLEAN lb_Loop = TRUE



SetPointer(HourGlass!)



THIS.of_MicroHelp('Connecting to database')



ls_Section = 'Connection'



THIS.of_GetRegistry(ls_Section, "dbms", sqlca.dbms)

THIS.of_GetRegistry(ls_Section, "database", sqlca.database)

THIS.of_GetRegistry(ls_Section, "servername", sqlca.servername)

THIS.of_GetRegistry(ls_Section, "dbparm", sqlca.dbparm)

THIS.of_GetRegistry(ls_Section, "impdirec", gnv_app.impdirec)

THIS.of_GetRegistry(ls_Section, "incodirec", gnv_app.incodirec)

THIS.of_GetRegistry(ls_Section, "procdirec", gnv_app.procdirec)

// Set application name for connection





// Get dbparm from registry, like everything else

// sqlca.dbparm = "AppName='" + THIS.is_AppName + "'"



// Set transaction object



THIS.itr_Sql = SQLCA



// Check parameters



IF sqlca.dbms = "" THEN



IF THIS.of_SetConParm() = -1 THEN RETURN -1



END IF



DO WHILE TRUE



IF THIS.of_Logon() = -1 THEN RETURN -1



THIS.of_SetRegistry(ls_Section, "dbms", sqlca.dbms)

THIS.of_SetRegistry(ls_Section, "database", sqlca.database)

THIS.of_SetRegistry(ls_Section, "servername", sqlca.servername)

THIS.of_SetRegistry(ls_Section, "dbparm", sqlca.dbparm)

THIS.of_SetRegistry(ls_Section, "impdirec", gnv_app.impdirec)

THIS.of_SetRegistry(ls_Section, "incodirec", gnv_app.incodirec)

THIS.of_SetRegistry(ls_Section, "procdirec", gnv_app.procdirec)



SQLCA.AutoCommit = TRUE



SetPointer(HourGlass!)



CONNECT;



IF sqlca.sqlcode <> 0 THEN



THIS.of_MicroHelp('Connection failed')



THIS.of_SqlError('Login failed',SQLCA)



ELSE



THIS.of_MicroHelp('Connected')



RETURN 1



END IF



LOOP



// Logon failed, return error



RETURN -1
  1. Helpful
John Fauss
  1. John Fauss
  2. Friday, 16 February 2024 18:50 PM UTC
Have you verified that the calls to of_GetRegistry are indeed successful and that the SQLCA properties have been set correctly prior to issuing the CONNECT command?
  1. Helpful 1
Albert van Bochove
  1. Albert van Bochove
  2. Monday, 19 February 2024 13:50 PM UTC
is true, my calls to of_GetRegistry are not being successful, and now my question is how to make it work then? if in my registry I have the values.
  1. Helpful
There are no comments made yet.
Albert van Bochove
Albert van Bochove Accepted Answer Pending Moderation
  1. Thursday, 21 September 2023 16:01 PM UTC
  2. PowerBuilder
  3. # 3

Hello all,

thanks for your support,  I have placed the  config parameter as you indicated

SQLCA.DBParm = "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'", I only did it in the registry, because the PowerBuilder IDE, does not respond when I want to save my code with that line.

 It is mandatory mset it from the source code of my app?

Now I am getting the following message after trying to login into my app, Error message; "SQLSTATE = IM002 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

 

I guess this error message corresponds to what is reading from the registry. 

Does someone know hoe to fix this?

 

Comment
There are no comments made yet.
Albert van Bochove
Albert van Bochove Accepted Answer Pending Moderation
  1. Wednesday, 21 February 2024 13:08 PM UTC
  2. PowerBuilder
  3. # 4

Hello all, 

Thanks for your responses, still I am facing the issue that my calls to of_GetRegistry are not being successful, and in my registry I have the values.

 

This are my registry values:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Receiver\Irs_mgr\Connection]
"dbms"="ODBC"
"database"="DB_NAME"
"servername"="SERVER_NAME"
"dbparm"="ConnectString='DSN=irs_mgr;UID=username;PWD=password'"
"impdirec"="i:\\"
"incodirec"="i:\\onjuiste files"
"procdirec"="i:\\processed files"
"expDateformaat"="dd/mm/yyyy"
"expdir"="\\\\userdata-01\\recacn$\\export"
"userid"="username"

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

and here below is my code where I am expecting  the data being read from the registry:

STRING ls_Section, ls_AppName
BOOLEAN lb_Loop = TRUE
SetPointer(HourGlass!)
THIS.of_MicroHelp('Connecting to database')
ls_Section = 'Connection'

THIS.of_GetRegistry(ls_Section, "dbms", sqlca.dbms)
THIS.of_GetRegistry(ls_Section, "database", sqlca.database)
THIS.of_GetRegistry(ls_Section, "servername", sqlca.servername)
THIS.of_GetRegistry(ls_Section, "dbparm", sqlca.dbparm)
THIS.of_GetRegistry(ls_Section, "impdirec", gnv_app.impdirec)
THIS.of_GetRegistry(ls_Section, "incodirec", gnv_app.incodirec)
THIS.of_GetRegistry(ls_Section, "procdirec", gnv_app.procdirec)

// Set application name for connection
// Get dbparm from registry, like everything else
// sqlca.dbparm = "AppName='" + THIS.is_AppName + "'"

// Set transaction object
THIS.itr_Sql = SQLCA

    // Check parameters
    IF sqlca.dbms = "" THEN
        IF THIS.of_SetConParm() = -1 THEN RETURN -1
    END IF

    DO WHILE TRUE
        IF THIS.of_Logon() = -1 THEN RETURN -1
        THIS.of_SetRegistry(ls_Section, "dbms", sqlca.dbms)
        THIS.of_SetRegistry(ls_Section, "database", sqlca.database)
        THIS.of_SetRegistry(ls_Section, "servername", sqlca.servername)
        THIS.of_SetRegistry(ls_Section, "dbparm", sqlca.dbparm)
        THIS.of_SetRegistry(ls_Section, "impdirec", gnv_app.impdirec)
        THIS.of_SetRegistry(ls_Section, "incodirec", gnv_app.incodirec)
        THIS.of_SetRegistry(ls_Section, "procdirec", gnv_app.procdirec)

        SQLCA.AutoCommit = TRUE
        SetPointer(HourGlass!)
        CONNECT;

            IF sqlca.sqlcode <> 0 THEN
                    THIS.of_MicroHelp('Connection failed')
                    THIS.of_SqlError('Login failed',SQLCA)
            ELSE
                    THIS.of_MicroHelp('Connected')
                RETURN 1
            END IF
    LOOP

// Logon failed, return error
RETURN -1
 
My question is why is not reading my values from the registry ?
Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 21 February 2024 16:30 PM UTC
Did you launch your app using "Run as Administrator". You need admin rights to touch the registry.
  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 21 February 2024 14:14 PM UTC
  2. PowerBuilder
  3. # 5

Hi, Albert -

The first argument to the GetRegistry/SetRegistry PowerScript functions is the complete Registry key.

Your code specifies only the last subkey "Connection". Try setting the value of ls_connection to:

    "HKEY_LOCAL_MACHINE\SOFTWARE\Receiver\Irs_mgr\Connection"

Please be aware that the local machine hive of the Windows Registry typically requires Administrator privileges to set/modify.

Best regards, John

Comment
Albert van Bochove
  1. Albert van Bochove
  2. Friday, 23 February 2024 14:32 PM UTC
Hello John Faus. What do you mean with Set the value of ls_connection? Because I do not have an ls_connection variable... I gues you meant ls_section rigth?
  1. Helpful
John Fauss
  1. John Fauss
  2. Friday, 23 February 2024 14:48 PM UTC
My apologies. I erroneously was referring to the line in the code you posted that reads:

ls_section = 'Connection'
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 21 February 2024 18:55 PM UTC
  2. PowerBuilder
  3. # 6

Hi Albert;

  FWIW: Just my $0.02 here bit I would suggest abandoning the use of the MS Windows Registry. Here are some considerations to think about ...

  • The Registry has a 32 bit and a 64 bit side. Data stored on the 64 bit side is not available to 32 but PB Apps. Also vise versa, 32 bit registry entries are not available to 64 bit Apps.
  • MS Windows Policy settings can block any user App exe from accessing the registry
  • Anti-virus software can block any user App exe from accessing the registry
  • The PB App exe might need to be run in Admin Mode to get access to the registry
  • All of the above can vary by user by machine.
  • etc

Personally, I would use an alternative like an a good old fashioned INI control file (or JSON or XML) to grab these settings. Also for security, I would encrypt these type of files connection information. Food for thought.  HTH  ;-)

Regards ... Chris

Comment
Albert van Bochove
  1. Albert van Bochove
  2. Tuesday, 27 February 2024 17:55 PM UTC
Ok... good your feedback @Chris.

Point 1. - The Registry has a 32 bit and a 64 bit side. Data stored on the 64 bit side is not available to 32 but PB Apps. Also vise versa, 32 bit registry entries are not available to 64 bit Apps.... This is not the case because my key is "HKEY_LOCAL_MACHINE\SOFTWARE\Receiver\Irs_mgr\Connection]" and here we can see that according this path then belongs to the shared section for both 32 and 64 bits.
  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.