Development Team Blog

When using one of the database Connectivity Kits, how can you make your program display its own error message when a connection string is not correct? By default, an error is displayed and the database will pop up a login dialog for users to enter their correct user and password. Or the application may display an unhandled error. Hmm... not what you want to see when using either the login command or SQLConnect method.


In order to have your application better handle those errors, you will need to:

1. Set the Connectivity Kit to use silent login
2. Handle error 12293 ("Login unsuccessful") in your application

Before you can implement item 1, it would be helpful if you created a funtion like the DriverIndex below. DriverIndex is documented in the Visual DataFlex and Connectivity Kit Help as part of the examples in many of the driver and database attributes. For those attributes, like DF_DRIVER_SILENT_LOGIN, the index of the loaded driver we want to use is a required argument we need to pass when setting or getting the attribute value. DriverIndex will check if the driver name you pass as an argument is loaded and return the index of that driver for you to use.

This is the suggested code for DriverIndex:

Code:

Function DriverIndex String sDriver Returns Integer
  String sCurrentDriver
  Integer iDriver
  Integer iNumDrivers
 
  Get_Attribute DF_NUMBER_DRIVERS to iNumDrivers
  For iDriver from 1 to iNumDrivers
     Get_Attribute DF_DRIVER_NAME of iDriver to sCurrentDriver
     If (Uppercase(sDriver) = Uppercase(sCurrentDriver)) Begin
         Function_Return iDriver
     End
  Loop
  Function_Return 0
End_Function // DriverIndex

The sDriver argument must be one of the valid driver name, such as: "ODBC_DRV", "DB2_DRV", "MSSQLDRV".

After having DriverIndex in place, we can create a method to adjust the environment where the application is running so failed logins can be controlled at the application level. First, we need to set DF_DRIVER_SILENT_LOGIN to False.

DF_DRIVER_SILENT_LOGIN indicates whether or not the database client login pop-up dialog should be displayed when the login information (connection string) passed to the database is not correct or not enough for the login/connection to succeed.

But setting DF_DRIVER_SILENT_LOGIN to False will only prevent the database login dialog to pop-up; the error generated when a login is unsuccessful will still occur. That's why we need to set Visual DataFlex to ignore that error and let the application handle it whichever way necessary.

The following routine does all that:

Code:

Procedure SetUpMSSQLEnvironment
  Integer iDriverID
 
  Load_Driver "MSSQLDRV" 
  Get DriverIndex "MSSQLDRV" to iDriverID
  Set_Attribute DF_DRIVER_SILENT_LOGIN of iDriverID to True
 
  // error 12293 is "Login unsuccessful"
  Send Ignore_Error of Error_Object_Id 12293 
End_Procedure

And finally, your application will be able to handle the error like this:

Code:

Procedure MainRoutine
  Handle hoConnection hoStatement
  Integer iFetchResult 
  String sName sState
 
  Send SetUpMSSQLEnvironment
 
  // Bad connection string
  Get SQLConnect of hoSQLMngr "MSSQLDRV"   "SERVER=NOSERVER;Trusted_Connection=yes;DATABASE=VDF15xExamples" to hoConnection
  If (hoConnection) Begin
     Get SQLOpen of hoConnection to hoStatement
     If (hoStatement) Begin
         Send SQLExecDirect to hoStatement "Select * from Customer"
         Repeat
             Get SQLFetch of hoStatement to iFetchResult
             If (iFetchResult <> 0) Begin
                 Get SQLColumnValue of hoStatement 2 to sName
                 Get SQLColumnValue of hoStatement 5 to sState
                 Showln sName ", " sState
             End
         Until (iFetchResult = 0)
 
         Send SQLClose to hoStatement
     End
     Send SQLDisconnect to hoConnection
  End
  Else Begin
     // error 12293 is "Login unsuccessful"
     If (LastErr=12293) Begin
         Send Stop_Box "Login Unsuccessful" "Error"
     End    
     Send Info_Box "Connection not good..." "Testing Connection"
  End
End_Procedure

The attached example can be used in the Order Entry sample and shows the entire code used to demonstrate and explain this technique. Have fun!