PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

I am a new user ( need database exemple )

https://www.purebasic.fr/english/viewtopic.php?t=6072

Page 1 of 1

I am a new user ( need database exemple )

Posted: Mon May 05, 2003 4:56 pm
by hteamspy
Hi, for my birthday I had get the PureBasic licence.

And now I search an exemple who use an Ms Access database

1 Is it possible
2 If anyone as it can it mail me at

nicolas.alpi@laposte.net

Tks

( sorry for my english )

Posted: Mon May 05, 2003 8:30 pm
by Num3
Here's a working snippet

Code: Select all

; Enhanced Database example
;by Siegfried Rings (CodeGuru)
;File$ = OpenFileRequester("PureBasic - Open", "C:\*.mdb", "Microsoft Access (*.mdb)|*.mdb;*.bat|Microsoft Excel (*.xls)|*.xls", 1)
File$ = "C:\TestDB.mdb"
#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
    MyMemory=AllocateMemory(1,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 )
    NewResult=SQLConfigDataSource_(0,#ODBC_ADD_DSN,lpszDriver.s,MyMemory )
    
    FreeMemory(1)
    CloseLibrary(1)
    If Result
      ProcedureReturn 1
    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


MeinPointer.l
Procedure GetDBHandle()
  Shared MeinPointer.l
  !EXTERN _PB_DataBase_CurrentObject;_PB_DataBase_CurrentObject
  !MOV dword EAX,[_PB_DataBase_CurrentObject]
  !MOV dword [v_MeinPointer], EAX
  ProcedureReturn MeinPointer
EndProcedure


;File$ = OpenFileRequester("PureBasic - Open", "C:\*.mdb", "Microsoft Access (*.mdb)|*.mdb;*.bat|Microsoft Excel (*.xls)|*.xls", 1)
File$ = "C:\TestDB.mdb"
If File$<>""
  ;  MessageRequester("Information", "Selected File: "+File$, 0);
Else
  End
EndIf

EXT.s=UCase(GetExtensionPart(File$))
Select EXT
Case "MDB"
  Result=Makeconnection("Microsoft Access Driver (*.mdb)","Server=SomeServer; Description=Description For Purebasic MDB-ODBC;DSN=PureBasic_DSN;DBQ="+file$+";UID=Rings;PWD=Siggi;")
  ;Case "XLS"
  ; Result=Makeconnection("Microsoft Excel Driver (*.xls)","DSN=PureBasic_DSN;Description=Description For Purebasic Excel;FileType=Excel97;DBQ="+File$+";")
EndSelect

If InitDatabase() = 0
  MessageRequester("Error", "Can't initialize Database (ODBC v3 or better) environment", 0)
  End
EndIf

OpenConsole()

Dim DatabaseType.s(4)
DatabaseType(0) = "Unknown"
DatabaseType(1) = "Numeric"
DatabaseType(2) = "String"
DatabaseType(3) = "Float"

; First, let's see which drivers are attached to the system..
;
PrintN("Available drivers:")
PrintN("")

If ExamineDatabaseDrivers()
  While NextDatabaseDriver()
    PrintN(DatabaseDriverName()+" - "+DatabaseDriverDescription())
  Wend
EndIf

; Open an ODBC database
;
;'If OpenDatabaseRequester(0)
User$=""
Password$=""
#Database=1
Result = OpenDatabase(#Database, "PureBasic_DSN", User$, Password$)
If Result
  Browse$="Select * from Authors"
  PrintN("")
  PrintN("Database successfully opened !")
  
  
  PrintN("Type EXIT to quit.")
  PrintN("or anything else to browse database")
  
  Repeat
    Command$ = Input()
    Select UCase(Command$)
    Case "EXIT"
      Quit = 1
      
      Default
      
      If DatabaseQuery(Browse$)
        
        NbColumns = DatabaseColumns()
        PrintN("NbColums: " + Str(NbColumns))
        
        For k=0 To NbColumns-1
          PrintN(DatabaseColumnName(k) + " - " + DatabaseType(DatabaseColumnType(k)))
        Next
        
        PrintN("")
        Print ("Press return to continue") : Input()
        PrintN("")
        PrintN("Query Result -------------------------------------")
        
        While NextDatabaseRow()
          PrintN(GetDatabaseString(0)+Chr(9) +GetDatabaseString(1))
        Wend
        
        PrintN("--------------------------------------------------")
      Else
        PrintN("Bad Query !")
      EndIf
    EndSelect
  Until Quit = 1
Else
  MessageRequester("Info", "Operation canceled", 0)
EndIf

;and delete:
DeleteConnection("Microsoft Access Driver (*.mdb)","PureBasic_DSN")

Posted: Mon May 05, 2003 10:08 pm
by freak
> Hi, for my birthday I had get the PureBasic licence.



Well, then: Image

And a big: Image

to the PB community. I hope you have a lot of fun here.

Timo

Posted: Tue May 06, 2003 8:49 am
by Fred
freak :lol:

Posted: Tue May 06, 2003 11:45 am
by ShDancer
Hello.
This example works fine for Access, but if i want to make a DSN connection to a MsSQL 2000 Server it doesn't work. :(

No error msgs, but also no DSN.

Can some one help me on this one?

Thanks in advance.

Posted: Tue May 06, 2003 4:02 pm
by Rings
create your dsn by hand or read the docu about M$-sql-server carefully.
I have no M$-Server running here to test , sorry .

I dont understand

Posted: Tue May 06, 2003 9:07 pm
by hteamspy
When I try to complie and run your code, I have a Prure Basic message bos saying :
" Cant't create the pure basic file ( already running ? ) "

So ????

Thk for your snippet

I'am going to retry and retry ??

( I'am a windows XP user, is it important ? )

Nicolas

Posted: Tue May 06, 2003 10:03 pm
by Andre
When I try to complie and run your code, I have a Prure Basic message bos saying :
" Cant't create the pure basic file ( already running ? ) "
Same here, also WinXP Home SP1 and PB3.62 + latest updates.
Tried it also with PB3.50, same error.

Another try with PB 3.40 and voila, there it works... :D
But this shouldn't be the best solution... :roll:

Maybe its a thing for Fred, to check the source for any incompatibility with newer PB versions...

Posted: Tue May 06, 2003 10:49 pm
by freak
From 3.40 to 3.50, the Assembler was changed from NASM to FASM.
And the syntax is a little different. In this code, there a direct ASM
command "EXTERN", which must be "EXTRN" in FASM.

Change this line:

Code: Select all

  !EXTERN _PB_DataBase_CurrentObject;_PB_DataBase_CurrentObject
to

Code: Select all

  !EXTRN _PB_DataBase_CurrentObject;_PB_DataBase_CurrentObject
(remove the 'E' from EXTERN.)

It should work fine now.

Timo

Posted: Wed Mar 02, 2005 6:05 pm
by dell_jockey
Hi Group,

today I needed an example of using ODBC and found the snippet above. I added the change suggested by Freak. Also, I changed a call to AllocateMemory, as this function only needs one argument. It now compiles fine with PB 3.93.

Thanks for the snippet. The corrected one follows below:

Code: Select all

; Enhanced Database example 
;by Siegfried Rings (CodeGuru) 
;File$ = OpenFileRequester("PureBasic - Open", "C:\*.mdb", "Microsoft Access (*.mdb)|*.mdb;*.bat|Microsoft Excel (*.xls)|*.xls", 1) 
File$ = "C:\TestDB.mdb" 
#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 
    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 ) 
    NewResult=SQLConfigDataSource_(0,#ODBC_ADD_DSN,lpszDriver.s,MyMemory ) 
    
    FreeMemory(1) 
    CloseLibrary(1) 
    If Result 
      ProcedureReturn 1 
    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 


MeinPointer.l 
Procedure GetDBHandle() 
  Shared MeinPointer.l 
  !EXTRN _PB_DataBase_CurrentObject;_PB_DataBase_CurrentObject 
  !MOV dword EAX,[_PB_DataBase_CurrentObject] 
  !MOV dword [v_MeinPointer], EAX 
  ProcedureReturn MeinPointer 
EndProcedure 


;File$ = OpenFileRequester("PureBasic - Open", "C:\*.mdb", "Microsoft Access (*.mdb)|*.mdb;*.bat|Microsoft Excel (*.xls)|*.xls", 1) 
File$ = "C:\TestDB.mdb" 
If File$<>"" 
  ;  MessageRequester("Information", "Selected File: "+File$, 0); 
Else 
  End 
EndIf 

EXT.s=UCase(GetExtensionPart(File$)) 
Select EXT 
Case "MDB" 
  Result=Makeconnection("Microsoft Access Driver (*.mdb)","Server=SomeServer; Description=Description For Purebasic MDB-ODBC;DSN=PureBasic_DSN;DBQ="+file$+";UID=Rings;PWD=Siggi;") 
  ;Case "XLS" 
  ; Result=Makeconnection("Microsoft Excel Driver (*.xls)","DSN=PureBasic_DSN;Description=Description For Purebasic Excel;FileType=Excel97;DBQ="+File$+";") 
EndSelect 

If InitDatabase() = 0 
  MessageRequester("Error", "Can't initialize Database (ODBC v3 or better) environment", 0) 
  End 
EndIf 

OpenConsole() 

Dim DatabaseType.s(4) 
DatabaseType(0) = "Unknown" 
DatabaseType(1) = "Numeric" 
DatabaseType(2) = "String" 
DatabaseType(3) = "Float" 

; First, let's see which drivers are attached to the system.. 
; 
PrintN("Available drivers:") 
PrintN("") 

If ExamineDatabaseDrivers() 
  While NextDatabaseDriver() 
    PrintN(DatabaseDriverName()+" - "+DatabaseDriverDescription()) 
  Wend 
EndIf 

; Open an ODBC database 
; 
;'If OpenDatabaseRequester(0) 
User$="" 
Password$="" 
#Database=1 
Result = OpenDatabase(#Database, "PureBasic_DSN", User$, Password$) 
If Result 
  Browse$="Select * from Authors" 
  PrintN("") 
  PrintN("Database successfully opened !") 
  
  
  PrintN("Type EXIT to quit.") 
  PrintN("or anything else to browse database") 
  
  Repeat 
    Command$ = Input() 
    Select UCase(Command$) 
    Case "EXIT" 
      Quit = 1 
      
      Default 
      
      If DatabaseQuery(Browse$) 
        
        NbColumns = DatabaseColumns() 
        PrintN("NbColums: " + Str(NbColumns)) 
        
        For k=0 To NbColumns-1 
          PrintN(DatabaseColumnName(k) + " - " + DatabaseType(DatabaseColumnType(k))) 
        Next 
        
        PrintN("") 
        Print ("Press return to continue") : Input() 
        PrintN("") 
        PrintN("Query Result -------------------------------------") 
        
        While NextDatabaseRow() 
          PrintN(GetDatabaseString(0)+Chr(9) +GetDatabaseString(1)) 
        Wend 
        
        PrintN("--------------------------------------------------") 
      Else 
        PrintN("Bad Query !") 
      EndIf 
    EndSelect 
  Until Quit = 1 
Else 
  MessageRequester("Info", "Operation canceled", 0) 
EndIf 

;and delete: 
DeleteConnection("Microsoft Access Driver (*.mdb)","PureBasic_DSN")

Posted: Sun May 15, 2005 9:36 pm
by kane
Maybe somone can make me working simple program of this as example if possible. I'm brand new at programing but I use this all time..

Code: Select all

DECLARE @accountname varchar(30)
SET @accountname = ''
DECLARE @action varchar(30)
SET @action = 'unban'

Use Lin2DB

IF @action = 'ban'
BEGIN
UPDATE User_Account SET block_flag = 1 WHERE account = @accountname
UPDATE User_Account SET block_flag2 = 1 WHERE account = @accountname
PRINT 'Selected account has been banned'
END
ELSE
IF @action = 'unban'
BEGIN
UPDATE User_Account SET block_flag = 0 WHERE account = @accountname
UPDATE User_Account SET block_flag2 = 0 WHERE account = @accountname
PRINT 'Selected account has been unbanned'
END
thats mssql quary.

I use file ODBC with a user/pass sa/password
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited