It is currently Thu Oct 17, 2019 9:24 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 15 posts ] 
Author Message
 Post subject: Create DSN (for DatabaseConnectivity) on the fly
PostPosted: Fri Aug 09, 2002 9:58 am 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
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:
;
;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


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Fri Aug 09, 2002 11:06 am 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
Restored from previous forum. Originally posted by Fangbeast.

Quote:
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


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Fri Aug 09, 2002 11:46 am 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
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


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sat Aug 10, 2002 3:18 am 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
Restored from previous forum. Originally posted by Fangbeast.

Quote:
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


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sat Aug 10, 2002 4:41 pm 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
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 :)


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sun Aug 11, 2002 3:24 am 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
Restored from previous forum. Originally posted by Fangbeast.

Quote:

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


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sun Aug 11, 2002 11:46 am 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
Restored from previous forum. Originally posted by Rings.

Quote:
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


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 08, 2002 2:58 pm 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
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


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Tue Oct 08, 2002 3:03 pm 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
Restored from previous forum. Originally posted by fred.

Demo version doesn't have API support...

Fred - AlphaSND


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Sun Feb 23, 2003 9:25 am 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
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


Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: Mon Feb 24, 2003 11:07 pm 
Offline
PureBasic Guru
PureBasic Guru

Joined: Tue Apr 22, 2003 7:42 pm
Posts: 16777133
Restored from previous forum. Originally posted by Num3.

Quote:
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...


Top
 Profile  
Reply with quote  
 Post subject: Re: Create DSN (for DatabaseConnectivity) on the fly
PostPosted: Mon Aug 08, 2016 8:04 am 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 4361
Location: Germany
Hi,

updated the code for 5.50 with unicode only:
Code:
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Create DSN (for DatabaseConnectivity) on the fly
PostPosted: Wed Aug 10, 2016 12:40 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue May 28, 2013 10:51 pm
Posts: 536
Location: Europe
I'm trying to make connection to Azure, but keep getting "Invalid keyword-value pairs".

Code:
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!

_________________
"If you lie to the compiler, it will get its revenge."
Henry Spencer
https://www.pci-z.com/


Last edited by bbanelli on Wed Aug 10, 2016 1:37 pm, edited 2 times in total.

Top
 Profile  
Reply with quote  
 Post subject: Re: Create DSN (for DatabaseConnectivity) on the fly
PostPosted: Wed Aug 10, 2016 1:30 pm 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 4361
Location: Germany
Hi,

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

Bernd


Top
 Profile  
Reply with quote  
 Post subject: Re: Create DSN (for DatabaseConnectivity) on the fly
PostPosted: Wed Aug 10, 2016 1:40 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue May 28, 2013 10:51 pm
Posts: 536
Location: Europe
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:

Quote:
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/


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 15 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 6 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye