Ich habe mir mal eine Universelle Einfache Procedure geschrieben, um SQL abfragen so leicht wie möglich zu starten.
Das Ergebnis einer SQL abfrage (Recordset) wird in eine LinkedList geschrieben, damit kann man dann weiterarbeiten.
Code: Alles auswählen
NewList CFF_MySQL_RS.s() ; Recordset Liste Beispiel:
Code: Alles auswählen
; Change this for your Database Conection
host.s = "localhost"
user.s = "Iam_mysql_User"
passwd.s = "my_password"
db.s = "my_DB"
;Beispiel
SQL.s = "Select * FROM my_table"
Errormsg = MySQL_Query(SQL,host,user,passwd,db) ; SQL Abfrage starten
If Errormsg > ""
;debug Errormsg
Debug "SQL Fehler = " + Errormsg
Else
While NextElement(CFF_MySQL_RS())
Debug CFF_MySQL_RS()
Wend
EndIfDie ergebnisse in der LinkedList sind alles Strings !!!
Die Fehler die bei MySQL Abfragen auftauchen, werden als Rückgabewert durchgereicht.
Ist der rückgabewert = "" . Ist kein Fehler aufgetreten.
Wenn man sich folgende Code als Include *.pbi ablegt geht dann alles einfacher.
Beachtet bitte, das die MySQL_libSearch() Procedure Windows zeug ist….
Code: Alles auswählen
; Icludefile for the use with libmysql.dll
; Code by Max.² English Forum Aug 23, 2004
; Edited by Pamkkkkk September 5. 2005
#libmysql = 1
#MySQL_CLIENT_COMPRESS = 32
NewList CFF_MySQL_RS.s() ; Recordset Liste
Global CFF_MySQL_Init.l
Global CFF_MySQL_ERRNO.l
Global CFF_MySQL_ERROR.l
Global CFF_MySQL_Real_Connect.l
Global CFF_MySQL_Real_Query.l
Global CFF_MySQL_Store_Result.l
Global CFF_MySQL_Field_Count.l
Global CFF_MySQL_Use_Result.l
Global CFF_MySQL_Fetch_Row.l
Global CFF_MySQL_Fetch_Lengths.l
Global CFF_MySQL_Free_Result.l
Global CFF_MySQL_Close.l
Global CFF_MySQL_LIBMYSQL_PATH.s
Procedure.s MySQL_libSearch()
; Windows stuff... Edit it for other systems!
; Searching and setting the libmysql.dll Path in Current Directory, Windows, Windows\system and Windows\system32
; Returns "" if dll ist not found, else the dll Path
; if dll is in CurentDir, it has the Choice
Result.s = ""
CurrentDir.s = Space(512)
GetCurrentDirectory_(Len(CurrentDir), @CurrentDir)
If FileSize( CurrentDir +"\libmysql.dll") > 0
ProcedureReturn CurrentDir +"\libmysql.dll"
EndIf
Systemroot.s = Space(260)
GetEnvironmentVariable_("SYSTEMROOT",@Systemroot,Len(Systemroot)) ; Copy Environment Variable to Systemroot
If FileSize( Systemroot +"\libmysql.dll") > 0
Result = Systemroot +"\libmysql.dll"
EndIf
If FileSize( Systemroot +"\system\libmysql.dll") > 0
Result = Systemroot +"\system\libmysql.dll"
EndIf
If FileSize( Systemroot +"\system32\libmysql.dll") > 0
Result = Systemroot +"\system32\libmysql.dll"
EndIf
If FileSize( Systemroot + "\Abfrage\tools\libmysql.dll") > 0
Result = Systemroot + "\Abfrage\tools\libmysql.dll"
EndIf
If Result = ""
;debug "libmysql.dll not found !"
EndIf
ProcedureReturn Result
EndProcedure
Procedure MySQL_Init()
If OpenLibrary(#libmysql,CFF_MySQL_LIBMYSQL_PATH)
CFF_MySQL_Init = IsFunction(#libmysql,"mysql_init")
CFF_MySQL_ERRNO = IsFunction(#libmysql,"mysql_errno")
CFF_MySQL_ERROR = IsFunction(#libmysql,"mysql_error")
CFF_MySQL_Real_Connect = IsFunction(#libmysql,"mysql_real_connect")
CFF_MySQL_Real_Query = IsFunction(#libmysql,"mysql_real_query")
CFF_MySQL_Store_Result = IsFunction(#libmysql,"mysql_store_result")
CFF_MySQL_Field_Count = IsFunction(#libmysql,"mysql_field_count")
CFF_MySQL_Use_Result = IsFunction(#libmysql,"mysql_use_result")
CFF_MySQL_Fetch_Row = IsFunction(#libmysql,"mysql_fetch_row")
CFF_MySQL_Fetch_Lengths = IsFunction(#libmysql,"mysql_fetch_lengths")
CFF_MySQL_Free_Result = IsFunction(#libmysql,"mysql_free_result")
CFF_MySQL_Close = IsFunction (#libmysql,"mysql_close")
ProcedureReturn CallFunctionFast (CFF_MySQL_Init,dbHnd)
Else
;Result = MessageRequester("Critical Error","libmysql.dll not found")
End
EndIf
EndProcedure
Procedure.s MySQL_GetError(db_ID,requester)
Protected Errormsg.s, i.l, Error.l
If CallFunctionFast(CFF_MySQL_ERRNO,db_ID) > 0
*Error =CallFunctionFast(CFF_MySQL_ERROR,db_ID)
Errormsg=PeekS(*Error)
If requester
Result= MessageRequester("MySQL error",Errormsg,#PB_MessageRequester_Ok)
EndIf
EndIf
ProcedureReturn Errormsg
EndProcedure
Procedure MySQL_Real_Connect (dbHnd,host.s,user.s,password.s,db.s,port.l,options.l)
ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Connect,dbHnd, host, user, password.s, db, port, 0, options)
EndProcedure
Procedure MySQL_Real_Query (dbHnd,SQL.s)
ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Query, dbHnd, SQL, Len(SQL))
EndProcedure
Procedure MySQL_Store_Result (dbHnd)
ProcedureReturn CallFunctionFast(CFF_MySQL_Store_Result,dbHnd)
EndProcedure
Procedure MySQL_Field_Count (dbHnd)
ProcedureReturn CallFunctionFast(CFF_MySQL_Field_Count,dbHnd)
EndProcedure
Procedure MySQL_Use_Result (dbHnd)
ProcedureReturn CallFunctionFast(CFF_MySQL_Use_Result,dbHnd)
EndProcedure
Procedure MySQL_Fetch_Row (*mysqlResult)
ProcedureReturn CallFunctionFast(CFF_MySQL_Fetch_Row,*mysqlResult)
EndProcedure
Procedure MySQL_Fetch_Lengths (*mysqlResult)
ProcedureReturn CallFunctionFast (CFF_MySQL_Fetch_Lengths,*mysqlResult)
EndProcedure
Procedure MySQL_Free_Result (*mysqlResult)
ProcedureReturn CallFunctionFast(CFF_MySQL_Free_Result,*mysqlResult)
EndProcedure
Procedure MySQL_Close (dbHnd)
CallFunctionFast(CFF_MySQL_Close,dbHnd)
EndProcedure
Procedure.s MySQL_Query(SQLquery.s,host.s,user.s,passwd.s,db.s)
port.l = 3306 ; Defaut (no need to change)
ClearList(CFF_MySQL_RS()) ; alte mSQL abfrage aus der Liste löschen
dbHnd.l
i.l
j.l
affRows.l
fieldNum.l
rowsNum.l
Result.s = ""
dbHnd = MySQL_Init()
If MySQL_Real_Connect (dbHnd, host, user, passwd, db, port, 32) = 0
ProcedureReturn MySQL_GetError(dbHnd,0)
EndIf
Real_Query= MySQL_Real_Query (dbHnd, SQLquery)
If Real_Query
ProcedureReturn MySQL_GetError(dbHnd,0)
EndIf
*mysqlResult=MySQL_Use_Result (dbHnd)
;debug*mysqlResult
;no result returned
If *mysqlResult=0
;no fields returned means error
If MySQL_Field_Count(dbHnd)
Result = MySQL_GetError(dbHnd,1)
;fields are returned, so no error but query didn't return data
Else
EndIf
;results are returned
Else
;affRows = CallFunction(#libmysql,"mysql_affected_rows",dbHnd)
fieldNum = CallFunction(#libmysql,"mysql_num_fields",*mysqlResult)
;rowsNum = CallFunction(#libmysql,"mysql_num_rows",*mysqlResult)
;debug affRows
;debug fieldNum
;debug rowsNum
Repeat
*mysqlRow=MySQL_Fetch_Row(*mysqlResult)
If *mysqlRow<>0
*mysqlLen=MySQL_Fetch_Lengths(*mysqlResult)
;length of given field
For j=1 To fieldNum
length=PeekL(*mysqlLen+4*(j-1))
fieldptr=PeekL(*mysqlRow+4*(j-1))
If fieldptr>0
content.s=PeekS(fieldptr,length)
Else
;zero pointer returend means empty field
content="NULL"
EndIf
;debug content
AddElement(CFF_MySQL_RS())
CFF_MySQL_RS() = content
Next j
EndIf
Until *mysqlRow = 0
MySQL_Free_Result(*mysqlResult)
EndIf
;debug CountList(CFF_MySQL_RS())
If dbHnd
MySQL_Close(dbHnd)
EndIf
ResetList(CFF_MySQL_RS())
ProcedureReturn Result
EndProcedure