ODBC direct connection is nice, but you have to specify the driver name.
The problem is, it can have different names on different machines (for the same database) because of different operating systems or languages.
Example of Oracle ODBC drivers : "Microsoft ODBC pour Oracle", "Oracle73 Ver 2.5", "Microsoft ODBC for Oracle"...
I use the ODBC_FindDriver() function to get the driver name for a database.
Exemple : ODBC_FindDriver("Oracle")
I wonder if there is a better way ?
Code: Select all
;
; Direct ODBC Connection
;
#ODBC_ADD_DSN = 1 ; Add Data source
#ODBC_REMOVE_DSN = 3 ; Remove Data source
Procedure.s ODBC_FindDriver(SourceOrBaseName.s) ; Find ODBC driver name for Oracle, SQL Server, Excel, etc ... [ex: ODBC_FindDriver("Oracle")]
Protected phKey.i, DriverIndex.i, DriverName.s, *Buffer, BufferSize.i, *BufferV, BufferVSize.i, ReturnValue.s
SourceOrBaseName = UCase(SourceOrBaseName)
BufferSize = 1024
BufferVSize = 1024
*Buffer = AllocateMemory(BufferSize)
If *Buffer
*BufferV = AllocateMemory(BufferVSize)
If *BufferV
If RegOpenKeyEx_(#HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", 0, #KEY_READ, @phKey) = #ERROR_SUCCESS
Result = RegEnumValue_(phKey, DriverIndex, *Buffer, @BufferSize, #NULL, #NULL, *BufferV, @BufferVSize)
If Result = #ERROR_SUCCESS
DriverIndex = DriverIndex + 1
DriverName = PeekS(*Buffer)
If FindString(UCase(DriverName), SourceOrBaseName, 1)
ReturnValue = DriverName
Else
Repeat
BufferSize = 1024
BufferVSize = 1024
Result = RegEnumValue_(phKey, DriverIndex, *Buffer, @BufferSize, #NULL, #NULL, *BufferV, @BufferVSize)
If Result = #ERROR_SUCCESS
DriverName = PeekS(*Buffer)
If FindString(UCase(DriverName), SourceOrBaseName, 1)
ReturnValue = DriverName
Debug "ODBC_FindDriver = '" + DriverName + "'"
EndIf
EndIf
DriverIndex = DriverIndex + 1
Until Result = #ERROR_NO_MORE_ITEMS
EndIf
EndIf
RegCloseKey_(phKey)
EndIf
FreeMemory(*BufferV)
EndIf
FreeMemory(*Buffer)
EndIf
ProcedureReturn ReturnValue
EndProcedure
Procedure.i ODBC_ConfigDataSource(Driver.s, ConnectString.s)
Protected Result, Base, *mem.Character, *ptr.Character, Len, i
If Driver And ConnectString
Len = Len(ConnectString)
Base = AllocateMemory((Len + 2) << (SizeOf(Character) - 1))
If Base
*mem = Base
*ptr = @ConnectString
For i = 1 To Len
If *ptr\c <> ';'
*mem\c = *ptr\c
EndIf
*mem + SizeOf(Character)
*ptr + SizeOf(Character)
Next
Result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, Driver, Base)
FreeMemory(Base)
EndIf
EndIf
ProcedureReturn Result
EndProcedure
Procedure.i ODBC_MakeConnection(Driver.s, Server.s, Database.s, Description.s, DSN.s)
Protected Library.i, *Memory, Result.i, Attributes.s, AlreadyExists.i
Static DatabaseInit.i
If DatabaseInit = #False
If UseODBCDatabase() = 0
MessageRequester("ERREUR", "ODBC version 3 ou supérieure est nécessaire !", #MB_ICONERROR)
Else
DatabaseInit = #True
EndIf
EndIf
If DatabaseInit
If ExamineDatabaseDrivers()
While NextDatabaseDriver()
If DatabaseDriverName() = DSN
AlreadyExists = #True
EndIf
Wend
EndIf
If AlreadyExists
Result = #True
Debug "MakeConnection : Already Exists"
Else
Attributes = "Server=" + Server + ";Description=" + Description + ";DSN=" + DSN
If Database
Attributes = Attributes + ";Database=" + Database
EndIf
Result = ODBC_ConfigDataSource(Driver, Attributes)
Debug "MakeConnection : ConfigDataSource = " + Str(Result)
EndIf
EndIf
ProcedureReturn Result
EndProcedure
Procedure.i ODBC_DeleteConnection(Driver.s, DSN.s)
Protected Library.i, Result.i
DSN = "DSN=" + DSN
Result = SQLConfigDataSource_(0, #ODBC_REMOVE_DSN, @Driver, @DSN)
Debug "ODBC_DeleteConnection : SQLConfigDataSource = " + Str(Result)
ProcedureReturn Result
EndProcedure
;
; ---
Driver.s = ODBC_FindDriver("Oracle")
Server.s = "MyORACLEDatabase"
DSN.s = "ORACLE"
Database.s = ""
User.s = "xxxxx"
Password.s = "xxxxxxx"
; ---
; Driver.s = ODBC_FindDriver("SQL Server")
; Server.s = "MySQLServerSERVER"
; DSN.s = "SQLS"
; Database.s = "SQLS"
; User.s = "xxx"
; Password.s = "xxx"
; ---
Description.s = "Purebasic-ODBC"
;
If ODBC_MakeConnection(Driver, Server, Database, Description, DSN)
If OpenDatabase(0, DSN, User, Password)
MessageRequester("OPENDATABASE SUCCESS", "Server : " + Server + Chr(10) + "Driver : " + Driver, #MB_ICONINFORMATION)
CloseDatabase(0)
Else
MessageRequester("OPENDATABASE FAILED", "Server : " + Server + Chr(10) + "Driver : " + Driver, #MB_ICONERROR)
EndIf
ODBC_DeleteConnection(Driver, DSN)
Else
MessageRequester("MAKECONNECTION FAILED", "Server : " + Server + Chr(10) + "Driver : (" + Driver + ")", #MB_ICONERROR)
EndIf
