The brunt of this was written by ABBKlaus, and I have referenced the links.
Also my own connection, end connection routines (pretty simple). Hopefully this will make it easier to find if someone else is looking for the same information.
This works (for me) with PB 4.50 Beta 2, and 4.41 final.
x86 windows on Windows 7
Code: Select all
;-{ ABBKlaus' ODBC connection routines (windows only)
;URL: http://www.purebasic.fr/english/viewtopic.php?p=197128#197128
; and http://www.purebasic.fr/english/viewtopic.php?f=13&t=28430
;{ constants
#ODBC_ADD_DSN = 1 ; Add Data source
#ODBC_ADD_SYS_DSN = 4 ; Add SYSTEM Data source
#ODBC_CONFIG_DSN = 2 ; Configure (edit) Data source
#ODBC_REMOVE_DSN = 3 ; Remove Data source
#ODBC_REMOVE_SYS_DSN = 6 ; Remove SYSTEM Data source
#SQL_SUCCESS = 0
#SQL_SUCCESS_WITH_INFO = 1
#SQL_ERROR = -1
#SQL_NO_DATA = 100
#SQL_MAX_MESSAGE_LENGTH = 512
#ODBC_ERROR_GENERAL_ERR = 1
#ODBC_ERROR_INVALID_BUFF_LEN = 2
#ODBC_ERROR_INVALID_HWND = 3
#ODBC_ERROR_INVALID_STR = 4
#ODBC_ERROR_INVALID_REQUEST_TYPE = 5
#ODBC_ERROR_COMPONENT_NOT_FOUND = 6
#ODBC_ERROR_INVALID_NAME = 7
#ODBC_ERROR_INVALID_KEYWORD_VALUE = 8
#ODBC_ERROR_INVALID_DSN = 9
#ODBC_ERROR_INVALID_INF = 10
#ODBC_ERROR_REQUEST_FAILED = 11
#ODBC_ERROR_INVALID_PATH = 12
#ODBC_ERROR_LOAD_LIB_FAILED = 13
#ODBC_ERROR_INVALID_PARAM_SEQUENCE = 14
#ODBC_ERROR_INVALID_LOG_FILE = 15
#ODBC_ERROR_USER_CANCELED = 16
#ODBC_ERROR_USAGE_UPDATE_FAILED = 17
#ODBC_ERROR_CREATE_DSN_FAILED = 18
#ODBC_ERROR_WRITING_SYSINFO_FAILED = 19
#ODBC_ERROR_REMOVE_DSN_FAILED = 20
#ODBC_ERROR_OUT_OF_MEM = 21
#ODBC_ERROR_OUTPUT_STRING_TRUNCATED = 22
;}
Procedure.i odbc_MakeKeywordValuePairs(Attributes$)
; ConfigDSN Function from M$
; http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcconfigdsn.asp
; Each pair is terminated with a null byte, and the entire list is terminated with a null byte.
; (That is, two null bytes mark the End of the list.)
Protected *LPAttribMem, L
While Right(Attributes$,2)<>";;"
Attributes$+";"
Wend
; Allocate enough memory in both Ascii and Unicode mode + space for the terminating zero character
*LPAttribMem=AllocateMemory(Len(Attributes$)*SizeOf(character)+SizeOf(character))
; Copy string to memory
PokeS(*LPAttribMem,Attributes$,Len(Attributes$))
; Replace each ';' with zero character
For L=1 To Len(Attributes$)
CompilerIf #PB_Compiler_Unicode
If PeekW(*LPAttribMem + (l-1) * SizeOf(character))=Asc(";")
PokeW(*LPAttribMem + (l-1) * SizeOf(character),0)
EndIf
CompilerElse
If PeekB(*LPAttribMem + l -1)=Asc(";")
PokeB(*LPAttribMem + l -1,0)
EndIf
CompilerEndIf
Next
ProcedureReturn *LPAttribMem
EndProcedure
Procedure.i odbc_MakeConnection(Driver$="SQL Server",Attributes$="DSN=DSN_TEMP")
Protected *KVPBuffer, Result
*KVPBuffer=odbc_MakeKeywordValuePairs(Attributes$)
Result=SQLConfigDataSource_(0,#ODBC_ADD_DSN,Driver$,*KVPBuffer)
FreeMemory(*KVPBuffer)
ProcedureReturn Result
EndProcedure
Procedure.i odbc_DeleteConnection(Driver$,DSN$)
Protected *KVPBuffer, Result
DSN$="DSN="+DSN$
*KVPBuffer=odbc_MakeKeywordValuePairs(DSN$)
Result=SQLConfigDataSource_(0,#ODBC_REMOVE_DSN,@Driver$,*KVPBuffer)
FreeMemory(*KVPBuffer)
ProcedureReturn Result
EndProcedure
;Debug MakeConnection("PostgreSQL ANSI","DSN=PostgreTest")
;Debug DeleteConnection("PostgreSQL ANSI","PostgreTest")
;}
;my constants
#DB_DRIVER = "SQL Server"
#DB_Server = "someserver"
#DB_DBname = "db"
#DB_uname = "User"
#DB_pw = "password"
Enumeration
#db_SQLITE
#db_sqlserver
EndEnumeration
Procedure.i DBConnect(dbPath.s, dbMode.i = #db_SQLITE)
;connects to db, if possible,
;db handle is return value
Protected dbH.i
Protected.s sServer,sAttrib,myDSN,del
Select dbMode
Case #db_SQLITE
UseSQLiteDatabase()
dbH = OpenDatabase(#PB_Any,dbPath,"","")
Case #db_sqlserver
del = ";" ;chr(0)
sServer = #db_DRIVER
myDSN = dbPath
UseODBCDatabase()
sAttrib = "DSN=" + myDSN + del
sAttrib + "DESCRIPTION="+myDSN+del
sAttrib+"SERVER="+ #DB_Server +del
;sAttrib+"ADDRESS="+ADDRESS1$+del
sAttrib+"NETWORK=DBMSSOCN"+del
sAttrib+"DATABASE="+#DB_DBname
sAttrib+del
If odbc_MakeConnection(sServer,sAttrib)
dbH= OpenDatabase(#PB_Any, myDSN,#DB_uname,#DB_pw)
Else
dbh=#False
EndIf
Default
Debug "; error: not coded yet")
ProcedureReturn 0
EndSelect
ProcedureReturn dbH
EndProcedure
Procedure.i db_CloseConnection(DBn.i, dbMode.i, DSN.s="")
If Not IsDatabase(dbn) : ProcedureReturn 0 : EndIf
Select dbMode
Case #db_SQLITE
CloseDatabase(dbn)
Case #db_sqlserver
If Not odbc_DeleteConnection(#db_DRIVER,DSN)
ProcedureReturn 0
EndIf
CloseDatabase(dbn)
Default
EndSelect
ProcedureReturn 1
EndProcedure
and their VB code:
Code: Select all
#If WIN32 Then
Dim intRet As Long
#Else
Dim intRet As Integer
#End If
Dim strDriver As String
Dim strAttributes As String
'Set the driver to SQL Server because it is most common.
strDriver = "SQL Server"
'Set the attributes delimited by null.
'See driver documentation for a complete
'list of supported attributes.
strAttributes = "SERVER=SomeServer" & Chr$(0)
strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr$(0)
strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr$(0)
strAttributes = strAttributes & "DATABASE=pubs" & Chr$(0)
'To show dialog, use Form1.Hwnd instead of vbAPINull.
intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _
strDriver, strAttributes)
If intRet Then
MsgBox "DSN Created"
Else
MsgBox "Create Failed"
End If