Connecting with f90SQLDriverConnect

 

f90SQLDriverConnect is used to connect to a data source using a connection string. f90SQLDriverConnect is used instead of f90SQLConnect for the following reasons:

 

 

f90SQLConnect assumes that a data source name, user ID, and password are sufficient to connect to a data source, and that all other connection information can be stored on the system. This is often not the case. For example, a driver might need one user ID and password to log into a server and a different user ID and password to log into a DBMS. Because f90SQLConnect accepts a single user ID and password, this means that the other user ID and password must be stored with the data source information on the system if f90SQLConnect is to be used. This is a potential breach of security and should be avoided unless the password is encrypted.

 

f90SQLDriverConnect allows the driver to define an arbitrary amount of connection information in the keyword-value pairs of the connection string. For example, suppose a driver requires a data source name, a user ID and password for the server, and a user ID and password for the DBMS. A custom program that always uses the XYZ Corp data source might prompt the user for IDs and passwords and build the following set of keyword-value pairs to for f90SQLDriverConnect:

 

DSN=XYZ Corp;UID=Gomez;PWD=Sesame;UIDDBMS=JGomez;PWDDBMS=Shazam;

 

The DSN (Data Source Name) keyword names the data source. The UID and PWD keywords specify the user ID and password for the server. The UIDDBMS and PWDDBMS keywords specify the user ID and password for the DBMS. Note that the final semicolon is optional. f90SQLDriverConnect parses this string; uses the XYZ Corp data source name to retrieve additional connection information from the system, such as the server address; and logs on to the server and DBMS using the specified user IDs and passwords.

 

Keyword-value pairs in f90SQLDriverConnect must follow certain syntax rules. The keywords and their values should not contain the []{}(),;?*=!@ characters. The value of the DSN keyword cannot consist only of blanks, and should not contain leading blanks. Because of the registry grammar, keywords and data source names cannot contain the backslash (\) character. Spaces are not allowed around the equal sign in the keyword-value pair.

 

The FILEDSN keyword can be used in a call to f90SQLDriverConnect to specify the name of a file containing data source information (see "Connecting Using File Data Sources" later in this section). The SAVEFILE keyword can be used to specify the name of a .dsn file. This file can store the keyword-value pairs of a successful connection made by a call to f90SQLDriverConnect.

 

The short program in Example 5.1 establishes two connections to data sources selected by the user from a list of available data sources. The example illustrates how to use f90SQLDriverConnect in two different forms. To connect to the first data source, we call f90SQLDriverConnect with the option SQL_DRIVER_COMPLETE. This forces the driver to request connection information from the user. Note that this is done by the driver, no by the application. To connect to the second data source, the application requests the user ID and password and builds a connection string. In this second case, we use the option SQL_DRIVER_NOPROMPT with f90SQLDriverConnect. If the user enters an invalid user ID or password, the connection is not established.

 

Example 5.1

program Example51
 
!lists available datasources and makes
!a connection to the one selected by the use
 
!load f90SQL modules
use f90SQLConstants
use f90SQL
 
implicit none
integer, parameter::MaxNDrivers=30
integer(SQLHENV_KIND):: EnvHndl
integer(SQLHDBC_KIND):: ConnHndl1,ConnHndl2
integer(SQLRETURN_KIND)::iRet
character*100 ServerName(MaxNDrivers),ConnStr, UsrName, Passwrd
integer(SQLSMALLINT_KIND) ServerNameLength(MaxNDrivers)
integer i, j,iDSN1,iDSN2
 
!allocate an environment handle
call f90SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, EnvHndl, iRet)
 
!Set ODBC version we will be using (3.x in this case)
call f90SQLSetEnvAttr(EnvHndl, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, iRet)
 
i=1
do while (i.le.MaxNDrivers)
 
call f90SQLDataSources(EnvHndl, SQL_FETCH_NEXT, ServerName(i), ServerNameLength(i), '', int(0,SQLSMALLINT_KIND), iRet)
 
!exit loop if an error condition is returned
if (iRet.ne.SQL_SUCCESS .and. iRet.ne.SQL_SUCCESS_WITH_INFO) exit
i=i+1
enddo
 
print *, 'Drivers registered in your system:'
write(*,'(2(i3,1x,a30))') (j,trim(ServerName(j)),j=1,i-1)
print *,'Select one data source. If additional information is needed'
print *,'to open a connection, the driver will request it from you'
read (*,*) iDSN1
 
!Allocate a connection handle
call f90SQLAllocHandle(SQL_HANDLE_DBC,EnvHndl, ConnHndl1, iRet)
 
!makes connection to selected data source
call f90SQLDriverConnect(ConnHndl1, SQL_NULL_HANDLE, 'DSN='//trim(ServerName(iDSN1)), ConnStr, int(0,SQLSMALLINT_KIND), SQL_DRIVER_COMPLETE, iRet)
 
if (iRet.ne.SQL_SUCCESS .and. iRet.ne.SQL_SUCCESS_WITH_INFO) then
print *,'Error connecting'
else
print *,'A successful connection was established'
print *,'The complete connection string was:'
print *,trim(ConnStr)
endif
 
print *, 'Drivers registered in your system:'
write(*,'(2(i3,1x,a30))') (j,trim(ServerName(j)),j=1,i-1)
print *,'Select another data source'
read (*,*) iDSN2
print *,'Enter your user name'
read (*,*) UsrName
print *,'Enter your password (WARNING: Password will show on screen)'
read (*,*) Passwrd
 
!Allocate a connection handle
call f90SQLAllocHandle(SQL_HANDLE_DBC,EnvHndl, ConnHndl2, iRet)
 
!Create connection string on the flight
ConnStr='DSN='//trim(ServerName(iDSN2))//';UID='//trim(UsrName)// ';PWD='//trim(Passwrd)
 
!makes connection to selected data source
call f90SQLDriverConnect(ConnHndl2, SQL_NULL_HANDLE, ConnStr, ConnStr, int(0,SQLSMALLINT_KIND), SQL_DRIVER_NOPROMPT, iRet)
 
if (iRet.ne.SQL_SUCCESS .and. iRet.ne.SQL_SUCCESS_WITH_INFO) then
print *,'Error connecting'
print *,'You may have entered an invalid user name or password'
else
print *,'A successful connection was established'
print *,'The complete connection string was:'
print *,trim(ConnStr)
endif
 
!disconnect
call f90SQLDisconnect(ConnHndl1, iRet)
call f90SQLDisconnect(ConnHndl2, iRet)
 
!release connection handles
call f90SQLFreeHandle(SQL_HANDLE_DBC, ConnHndl1, iRet)
call f90SQLFreeHandle(SQL_HANDLE_DBC, ConnHndl2, iRet)
 
!release environment handle
call f90SQLFreeHandle(SQL_HANDLE_ENV, EnvHndl, iRet)
 
stop
end

 

Some applications may not want to use a data source at all. Instead, they may want to connect directly to a driver. f90SQLDriverConnect provides a way for the application to connect directly to a driver without specifying a data source. Conceptually, a temporary data source is created at run time.

 

To connect directly to a driver, the application specifies the DRIVER keyword in the connection string instead of the DSN keyword. The value of the DRIVER keyword is the description of the driver as returned by f90SQLDrivers. For example, suppose a driver has the description "Paradox Driver", and requires the name of a directory containing the data files. To connect to this driver, the application might use either of the following connection strings:

 

DRIVER={Paradox Driver};Directory=C:\PARADOX;

DRIVER={Paradox Driver};

 

With the first string, the driver would not need any additional information. With the second string, the driver would need to prompt for the name of the directory containing the data files.