User DSN & System DSN - How to choose System???

Question

Scenario: Our install package used to add User DSN. We changed this to install System DSNs due to Terminal Servers and Citrix.

The problem is that some of these old User DSNs are floating around, and our application automatically chooses the User before the System and the connection info has changed as we upgraded server software.

I guess my question is, is there a way to force the application to use the System DSN over the User DSN if duplicates exist?

Our application is written in PowerBuilder 10.5 and we use SQL Anywhere 10.0.1.

Thanks, Calvin

Answer 1

I doubt you can change the order.

Would it possibly be easier to release a new version which uses a differently named System DSN?

Answer 2

The information about user DSN(s) are stored in the registry. Have you considered simply doing a couple registry reads and then either deleting/renaming the invalid DSN, or warning the user about the conflict and giving the user the option to repair the issue?

Answer 3

See RegistryValues() in the PowerBuilder Help. The following isn't exactly what you want but it does show a couple of working calls...

integer li_RC
string  ls_odbc_ini[]

li_RC = RegistryValues ( &
   "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources", &
    ls_odbc_ini )

IF li_RC <> 1 THEN
    MessageBox ( 'Error', &
     'RegistryValues failed in website.open' )
    RETURN
END IF

MessageBox ( 'A user DSN...', ls_odbc_ini[1] )

li_RC = RegistryValues ( &
   "HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\ODBC Data Sources", &
    ls_odbc_ini )

IF li_RC <> 1 THEN
    MessageBox ( 'Error', &
     'RegistryValues failed in website.open' )
    RETURN
END IF

MessageBox ( 'A system DSN...', ls_odbc_ini[1] )

Another suggestion is to read the connection information from whichever DSN you want, and then use a "DSN-less" connection, to avoid "picking the wrong DSN".

SQLCA.DBMS = 'ODB'

SQLCA.DBParm &
    = "ConnectString='Driver=SQL Anywhere 10;" &
    + "UID=dba;PWD=sql;DatabaseName=ruralfinds_local;EngineName=ruralfinds_local'," &
    + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

CONNECT USING SQLCA;

IF SQLCA.SQLCODE <> 0 THEN
    MessageBox ( 'Error', &
     'CONNECT failed in open:' &
     + '~r~nSQLCode = ' &
     + String ( SQLCA.SQLCode ) &
     + '~r~nSQLDBCode = ' &
     + String ( SQLCA.SQLDBCode ) &
     + '~r~n' &
     + SQLCA.SQLErrText )
    RETURN
END IF

Breck

Answer 4

Since you're using SQL Anywhere, take a look at the dbdsn utility that comes with it. It will allow you to list, describe, create, and delete both user and system DSNs. You can use it, for example, to see if user and system DSNs both exist and delete the one you don't want.