Handling Connection Error in your Application
byon 28-Apr-2010 at 09:00 AM (1821 Views)
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:
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.
- Set the Connectivity Kit to use silent login
- Handle error 12293 ("Login unsuccessful") in your application
This is the suggested code for DriverIndex:
The sDriver argument must be one of the valid driver name, such as: "ODBC_DRV", "DB2_DRV", "MSSQLDRV".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
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:
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!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