PureBasic Forums - English

Posted: **Sun Sep 28, 2008 11:59 am**

Three months ago I began writing applications in Pure Basic. So far I only used Access-Databases to do the job, but this had the downside that different users had different versions of Access and therefore complications were sure to happen.
Therefore I searched for a solution and were glad to see that PureBasic was able to fulfill (nearly) all my needs.
It is my aim to translate my Access-Applications into PureBasic, but the data should still be stored in Access-Databases.
The advantage I see in this is that I can easily view the data and therefore be able to check them for errors. Furthermore with PureBasic I can not only access tables but also queries.
My problem at the beginning was, that I couldn’t find extensive information on how access Access-Databases.
I needed several days of research in different boards to find out how to do it. Furthermore I also found out that I was not the only one with these problems.
Because of this I decided to compile everything I could find needed to access Access-Databases in order to save others the hard work of collecting these information from different sites all over the web.

Code: Select all


Enumeration 1 

#ODBC_ADD_DSN             ; // Add data source 
  
EndEnumeration 

Procedure.s AddDSN(databasename.s, user.s = "", pass.s = "", driver.s = "") ; Result DSN 
  
  ; This Procedure is nessessery to acces an ACCESS-Database.
  
  Protected name.s, strDriver.s, strAttributes.s 
  Protected L.l, result.l 
  Protected *buffer.Character 
  
  ; generate database name (DSN) 
  name.s = "Pure_Viewer" 

  ; choose driver automatically on error 
  If driver = "" 
    driver = GetExtensionPart(databasename) 
  EndIf 
  ; Choose database driver 
  Select UCase(driver) 
    Case "ACCESS", "MDB" 
      strDriver.s = "Microsoft Access Driver (*.mdb)" 
      ; Attributes zustellen 
      strAttributes.s = "Server=APServer;Description=" + name 
      strAttributes.s + ";DSN=" + name 
      strAttributes.s + ";DBQ=" + databasename 
      strAttributes.s + ";UID=" + user 
      strAttributes.s + ";PWD=" + pass + ";" 
  
    Default 
      strDriver.s = driver 
  EndSelect 
  
  *buffer = @strAttributes 
  For L = 0 To Len(strAttributes) - 1 
    If *buffer\c = ';' 
      *buffer\c = 0 
    EndIf 
    *buffer + 1 
  Next L                                                         ; Check the next byte 

  result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, strDriver, @strAttributes)   ; Call the function you need from the ODBC library with the right details 
    
  If result 
    ProcedureReturn name 
  Else 
    ProcedureReturn "" 
  EndIf 

EndProcedure  
  
;------------------------------------------------------------------------------------------- 
; First Database access 

User$ = "" 
Password$ = "" 

Path.s = C:\Folder\databasename.mdb" 

Base.s = AddDSN(Path,Benutzer$,Passwort$) ; Caling the procedure, that allows accessing the database. 

UseODBCDatabase() ; opens the Database 

If Base 

    If OpenDatabase(0, Base, User$, Password$) 
    
    Else 
        MessageRequester("Won’t work that way.", "The Database databasename.mdb has to be in directory "+Directory, #PB_MessageRequester_Ok) 
        End 
    
    EndIf 

EndIf 

;------------------------------------------------------------------------------------------- 

 ; Select Record by criteria    
    
    SQL = "SELECT * FROM Tabellenname WHERE Tabellenfeld1 = "+ Chr(39) + Variable$+ Chr(39) +"order by Tabellenfeld2 asc, Tabellenfeld3 asc" ; Ermittelt alle Einträge in der Tabelle 
    DatabaseQuery(0, SQL) ; SQL Befehl ausführen 

    Z = 0 
          
    While NextDatabaseRow(0)   ; alle Einträge durchlaufen 
      
      Z = Z + 1 
      Inhalt_Tabellenfeld1$(Z) = GetDatabaseString(0, 0) 
      Inhalt_Tabellenfeld2$(Z) = GetDatabaseString(0, 1) 
      Inhalt_Tabellenfeld3$(Z) = Str(GetDatabaseLong (0,2) 
      ;usw 
    
    Wend 

;------------------------------------------------------------------------------------------- 
; Delete Record 


      SQL = "DELETE FROM Tabellenname WHERE Feldname = "+ Chr(39) + Variable$+ Chr(39)  ; Ermittelt alle Einträge 
      DatabaseQuery(0, SQL) ; SQL Befehl ausführen 

;------------------------------------------------------------------------------------------- 

; Update Record
If  Variable$ = “”: Variable$ = “ “ : Endif ; If NULL –Value Uptdate is not possible

      SQL = "UPDATE Tabellenname SET Tabellenfeld ="+ Chr(39) +Variable$+ Chr(39) 
      SQL = SQL + "WHERE Tabellenfeld ="+ Chr(39) + Variable2$+ Chr(39)  
      DatabaseQuery(0, SQL) 

;------------------------------------------------------------------------------------------- 
;insert Record
If  Variable1$ = “”: Variable1$ = “ “ : Endif ; If  NULL –Value  Insert is not possible

     SQL = "INSERT INTO Tabellenname (Tabellenfeld1, Tabellenfeld2) VALUES (" + Chr(39)+Variable1$+ Chr(39) +","+ Chr(39)+Variable2$+ Chr(39)+")" 
     DatabaseQuery(0, SQL) ; SQL Befehl ausführen

These examples are not from me. I only collected them from different posts in different forums.

Should data be updated in a query, the query property must be set to “Dynaset”. An update cannot happen if this is not the case.

There is one problem I wasn’t able to solve:
I could not find a possibility to access a table element with WHERE if this element is declared as a number (float, integer etc). Using the exact same SQL-Query on an element declared as text, no error occurs and the query is handled properly.

Maybe someone of you can help me in this matter.

Thanks in advance, NLink

Posted: **Sun Sep 28, 2008 2:04 pm**

Regardless of the type you can always use GetDataBaseString().

As for the numbers you should get rid of the ''.

Code: Select all

SELECT * FROM MYTABLE WHERE Number=12345

Posted: **Sun Sep 28, 2008 5:21 pm**

Oh yes... ABBKlaus is correct.

Without his aide and assistance my POS application would still be a struggling dream, or attempting to integrate Cheetah badly!

You can also select unique as well!

Posted: **Wed Oct 01, 2008 1:33 pm**

Hallo ABBKlaus,
Many thanks for your help!

PureBasic Forums - English

PureBasic and Access-Databases

PureBasic and Access-Databases