Create DSN (for DatabaseConnectivity) on the fly

Share your advanced PureBasic knowledge/code with the community.
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Create DSN (for DatabaseConnectivity) on the fly

Post by BackupUser »

Code updated for 5.20+

Restored from previous forum. Originally posted by Rings.

With this code you can access every MDB or excel or other Database on the fly without add them to the ODBC-Control panel first.This snippet is another great converting from VB - by me, THE CODEGURU !

Code: Select all

;
;Create DSN on the fly  for ODBC
;
; An example by Siegfried Rings (CodeGuru)

#ODBC_ADD_DSN = 1        ; Add Data source
#ODBC_CONFIG_DSN = 2     ; Configure (edit) Data source
#ODBC_REMOVE_DSN = 3     ; Remove Data source

Procedure MakeConnection(Driver.s, strAttributes.s)
  Result=OpenLibrary(1,"ODBCCP32.DLL")
  If Result
    lpszDriver.s=Driver
    ; MessageRequester("Info",strAttributes,0)
    MyMemory=AllocateMemory(Len(strAttributes))
    CopyMemory(@strAttributes,MyMemory,Len(strAttributes))
    For L=1 To Len(strAttributes )
      If PeekB(MyMemory +l-1)=Asc(";")
        PokeB(MyMemory +l-1,0)
      EndIf
    Next L
    Result = CallFunction(1, "SQLConfigDataSource", 0, #ODBC_ADD_DSN, @lpszDriver.s, MyMemory)
    FreeMemory(MyMemory)
    CloseLibrary(1)
    If Result
      ProcedureReturn 1;MessageRequester("Info","DSN Created",0)
    EndIf
  EndIf
EndProcedure


Procedure DeleteConnection(Driver.s,DSN.s)
  Result=OpenLibrary(1,"ODBCCP32.DLL")
  If Result
    lpszDriver.s=Driver
    strAttributes.s = "DSN="+DSN
    Result = CallFunction(1, "SQLConfigDataSource", 0, #ODBC_REMOVE_DSN, @lpszDriver.s, @strAttributes)
    CloseLibrary(1)
    If Result
      ProcedureReturn 1;MessageRequester("Info","DSN Delete",0)
    EndIf
  EndIf
EndProcedure

;example:
Debug Makeconnection("Microsoft Access Driver (*.mdb)","Server=SomeServer; Description=Description For PurebasicODBC;DSN=PureBasic_DSN;DBQ=d:\biblio.mdb;UID=Rings;PWD=Siggi;")
Debug Makeconnection("Microsoft Excel Driver (*.xls)","DSN=Excel_TEST;Description=New_Excel_Data_Source;FileType=Excel97;DBQ=C:\\Telefon.xls;")

;or delete:
Debug DeleteConnection("Microsoft Access Driver (*.mdb)","PureBasic_DSN")
Its a long way to the top if you wanna .....CodeGuru

Edited by - Rings on 09 August 2002 12:44:23
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Post by BackupUser »

Restored from previous forum. Originally posted by Fangbeast.
With this code you can access every MDB or excel or other Database on the fly without add them to the ODBC-Control panel first.This snippet is another great converting from VB - by me, THE CODEGURU !
Holy Cow, this is exactly what I have been wanting for ages. I told Paul of the resources site that beginners are not going to want to follow (or understand) ODBC connecting but genius that he was (so he told me) (EVIL GRIN, he said he didn't know of a way.

Thanks Rings, you saved my bacon yet again :)

Paul, if you are lurking, hitting with the trout is no longer allowed :):)

Fangles
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Post by BackupUser »

Restored from previous forum. Originally posted by Rings.

i have edited the snippet below and changed the samples.please take a look for the changes (->Fangles before you kill me that it is not work ) .Both examples, the Access and the Excel works fine for me.
An not to forget, vote me for the offical BeerGuru of the month !

greez from your personal Codeguru - HappyWeekend!


Edited by - Rings on 09 August 2002 12:48:45
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Post by BackupUser »

Restored from previous forum. Originally posted by Fangbeast.
i have edited the snippet below and changed the samples.please take a look for the changes (->Fangles before you kill me that it is not work ) .Both examples, the Access and the Excel works fine for me.
An not to forget, vote me for the offical BeerGuru of the month !

greez from your personal Codeguru - HappyWeekend!


Edited by - Rings on 09 August 2002 12:48:45
I can't kill anyone because I'm waiting for Paul to kill me for the trout comment. If you uploaded those to his site, I can take a look there :):)

P.s. SOrry to disappoint you but I can't vote for youa s BeerGuru because I don't drink alcohol. But if you were an attractive brunette lady, I could vote for that :):)

Fangles
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Post by BackupUser »

Restored from previous forum. Originally posted by Paul.

Nice code Rings...

But it only seems useful if you already have an existing database. If not, then you must go into ODBC Data Sources to setup and create a database anyway.

I'm quite busy over the next week so I will be unable to look into this but if your code could be modified to include Creating the MDB database, then it would be extremely useful for those that know nothing about ODBC setup.

Keep up the great work !!
Paul


Fangs: I have my anti-Fang filter turned on so I don't hear you :)
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Post by BackupUser »

Restored from previous forum. Originally posted by Fangbeast.

Fangs: I have my anti-Fang filter turned on so I don't hear you :)
ROFL /me removes fangs and inserts gerrets

Fangles

(I meant Ferrets, not gerrets) Grrrr

Edited by - fangbeast on 11 August 2002 12:30:31
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Post by BackupUser »

Restored from previous forum. Originally posted by Rings.
Nice code Rings...

But it only seems useful if you already have an existing database. If not, then you must go into ODBC Data Sources to setup and create a database anyway.

I'm quite busy over the next week so I will be unable to look into this but if your code could be modified to include Creating the MDB database, then it would be extremely useful for those that know nothing about ODBC setup.

Keep up the great work !!
Paul


Fangs: I have my anti-Fang filter turned on so I don't hear you :)

thx Paul.a simple way to create a database is to include an empty one.You can create tables and files on the fly with native SQL-Statements.(Remember CREATE ) .
Another point is how to retrieve tablenames from a given Database.......

Its a long way to the top if you wanna .....CodeGuru
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Post by BackupUser »

Restored from previous forum. Originally posted by andyboy_uk.

I tried to compile this with the trial version but it didnt seem to work, is that down to the restrictions or should it work with this version.

Thanks



Andy Driskell
andyboy_uk@http://www.com on MSN Messenger
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Post by BackupUser »

Restored from previous forum. Originally posted by fred.

Demo version doesn't have API support...

Fred - AlphaSND
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Post by BackupUser »

Restored from previous forum. Originally posted by bpbig.

Hi,

I can not use this SQL Command like this:
Select * from Authors where [Author] like '*John*'
or
Select * from Authors where [Author] like '???John???????'

anybody can help!
Thanks
BackupUser
PureBasic Guru
PureBasic Guru
Posts: 16777133
Joined: Tue Apr 22, 2003 7:42 pm

Post by BackupUser »

Restored from previous forum. Originally posted by Num3.
Originally posted by bpbig

Hi,

I can not use this SQL Command like this:
Select * from Authors where [Author] like '*John*'
or
Select * from Authors where [Author] like '???John???????'

anybody can help!
Thanks
Try something like this:

Select * from Authors where Author like '%John%'

For more info check the SQL docs at http://www.sql.org/
:)

Anyway here's a snippet from a program I made:

;-Query
;*************************************************
query :


DBName.s = "cpostais.db"
DBHandle.l = SQLite_Open(DBName, 0)
If DBHandle

qline.s="SELECT Arruamento,Troco,Cpostal FROM dados WHERE Arruamento LIKE '%" + iinput + "%'"
If GetGadgetState(8)
qline + "And Distrito='Lisboa'"
EndIf

; execute SQL query
timer = GetTickCount_()
out.s = ""
SQL.s = qline
Result = SQLite_Get_Table(DBHandle, SQL, @Rows, @Cols)
If Result = #SQLITE_OK



; display returned rows
For Row.l = 1 To Rows
out + Chr(13)+Chr(10)+"***** " + Str(Row)+" *****" + Chr(13)+Chr(10)

For Col = 0 To Cols - 1
out + DBData(Row, Col) + Chr(13)+Chr(10)
Next
Next
result = GetTickCount_()-timer

SetGadgetText(#W1String2, out)
SetGadgetText(#W1Text3, "Registos encontrados:" + Str(row - 1) + " em " + Str(1000 / result) + " segundos")
SetGadgetText(#W1String1, "")
iinput = ""
input = ""

Else
MessageRequester("SQLite Error", "Get_Table error = " + Str(Result), #MB_IconError | #MB_OK)
EndIf
Else
MessageRequester("SQLite Error", "Can't open database " + DBName, #MB_IconError | #MB_OK)
EndIf

Return

;*************************************************


--
Kind Regards
Rui Carvalho

Old programmers never die... They branch into a subroutine...
infratec
Always Here
Always Here
Posts: 6818
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Create DSN (for DatabaseConnectivity) on the fly

Post by infratec »

Hi,

updated the code for 5.50 with unicode only:

Code: Select all

CompilerIf #PB_Compiler_IsMainFile
  EnableExplicit
CompilerEndIf


;
;Create DSN on the fly  for ODBC
;
; An example by Siegfried Rings (CodeGuru)

#ODBC_ADD_DSN = 1        ; Add Data source
#ODBC_CONFIG_DSN = 2     ; Configure (edit) Data source
#ODBC_REMOVE_DSN = 3     ; Remove Data source
#ODBC_ADD_SYS_DSN = 4
#ODBC_CONFIG_SYS_DSN = 5
#ODBC_REMOVE_SYS_DSN = 6
#ODBC_REMOVE_DEFAULT_DSN = 7

Procedure.i MakeConnection(Driver$, strAttributes$)
  
  Protected Result.i, Lib.i, *Driver, *Attributes, i.i
  
  
  Lib = OpenLibrary(#PB_Any, "ODBCCP32.DLL")
  If Lib
    *Driver = AllocateMemory(Len(Driver$) + 1)
    If *Driver
      PokeS(*Driver, Driver$, -1, #PB_Ascii)

      *Attributes = AllocateMemory(Len(strAttributes$) + 2) ; + 2 for double 0 terminated
      If *Attributes
        PokeS(*Attributes, strAttributes$, -1, #PB_Ascii)
        For i = 1 To MemorySize(*Attributes)
          If PeekA(*Attributes + i - 1) = ';'
            PokeA(*Attributes + i - 1, 0)
          EndIf
        Next i
        
        If CallFunction(Lib, "SQLConfigDataSource", 0, #ODBC_ADD_DSN, *Driver, *Attributes)
          Result = #True
        EndIf
        FreeMemory(*Attributes)
      EndIf
      FreeMemory(*Driver)
    EndIf
    
    CloseLibrary(Lib)
  EndIf
  
  ProcedureReturn Result
  
EndProcedure


Procedure DeleteConnection(Driver$, DSN$)
  
  Protected Result.i, Lib.i, *Driver, strAttributes$, *Attributes
  
  
  Lib = OpenLibrary(#PB_Any, "ODBCCP32.DLL")
  If Lib
    *Driver = AllocateMemory(Len(Driver$) + 1)
    If *Driver
      PokeS(*Driver, Driver$, -1, #PB_Ascii)
      
      strAttributes$ = "DSN=" + DSN$
      *Attributes = AllocateMemory(Len(strAttributes$) + 2) ; + 2 for double 0 terminated
      If *Attributes
        PokeS(*Attributes, strAttributes$, -1, #PB_Ascii)
        
        If CallFunction(Lib, "SQLConfigDataSource", 0, #ODBC_REMOVE_DSN, *Driver, *Attributes)
          Result = #True
        EndIf
        FreeMemory(*Attributes)
      EndIf
      FreeMemory(*Driver)
    EndIf
    CloseLibrary(Lib)
  EndIf
  
  ProcedureReturn Result
  
EndProcedure


CompilerIf #PB_Compiler_IsMainFile
  ;example:
  Debug Makeconnection("Microsoft Access Driver (*.mdb)", "Server=SomeServer; Description=Description For PurebasicODBC;DSN=PureBasic_DSN;DBQ=c:\tmp\biblio.mdb;UID=Rings;PWD=Siggi;")
  ;Debug Makeconnection("Microsoft Excel Driver (*.xls)", "DSN=Excel_TEST;Description=New_Excel_Data_Source;FileType=Excel97;DBQ=C:\\Telefon.xls;")
  
  ;or delete:
  Debug DeleteConnection("Microsoft Access Driver (*.mdb)", "PureBasic_DSN")
CompilerEndIf
Bernd
User avatar
bbanelli
Enthusiast
Enthusiast
Posts: 543
Joined: Tue May 28, 2013 10:51 pm
Location: Europe
Contact:

Re: Create DSN (for DatabaseConnectivity) on the fly

Post by bbanelli »

I'm trying to make connection to Azure, but keep getting "Invalid keyword-value pairs".

Code: Select all

Makeconnection("ODBC Driver 11 for SQL Server", "Driver={SQL Server Native Client 11.0};Server=tcp:xxx.database.windows.net,1433;Database=xxx;Uid=xxx;Pwd=MyPassword;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Dsn=xxx")
Any clues?

I've tried multiple variations, with or without "Driver" specified, but no good... :(

This seems to be working!!!

... Stil not, I have to double check it!
Last edited by bbanelli on Wed Aug 10, 2016 1:37 pm, edited 2 times in total.
"If you lie to the compiler, it will get its revenge."
Henry Spencer
https://www.pci-z.com/
infratec
Always Here
Always Here
Posts: 6818
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Create DSN (for DatabaseConnectivity) on the fly

Post by infratec »

Hi,

does it work when you do it the 'normal' way by hand?
And driver is without brackets.

Bernd
User avatar
bbanelli
Enthusiast
Enthusiast
Posts: 543
Joined: Tue May 28, 2013 10:51 pm
Location: Europe
Contact:

Re: Create DSN (for DatabaseConnectivity) on the fly

Post by bbanelli »

infratec wrote:Hi,

does it work when you do it the 'normal' way by hand?
And driver is without brackets.

Bernd
OK, I've figured it out:
Makeconnection("ODBC Driver 11 for SQL Server", "Dsn=XXX;Server=tcp:XXX.database.windows.net,1433;Database=XXX;Encrypt=yes;TrustServerCertificate=no;")
So, UID and PWD parameters have to be omitted, but when you supply them through OpenDatabase() parameters, connection works as expected!

BTW, as far as I've noticed, this procedure does not require Administrator privileges, or am I wrong? In addition, is there any harm in calling that function on each start of a program, since I can't find any easy "SQLCheckDataSource" function?
"If you lie to the compiler, it will get its revenge."
Henry Spencer
https://www.pci-z.com/
Post Reply