PureBasic Forums - English

t57042 wrote:Somebody knows how to adapt the PROTOTYPE in this function?

Code: Select all

;============================<[ Sel Str ]>=============================
;Declare Sub sluSelStr lib "SQLiteningU.Dll" alias "sluSelStr" ( _
 ;                                byval Statement As Long, _
  ;                               byval ModChars As Long, _
   ;                              byval FieldsAndRows As Long, _
    ;                             byref SizeOfFieldsAndRows As Long)
;   Statement is a pointer to a null-terminated string.
;   ModChars is a pointer to a null-terminated string. If not needed you
;            may pass a zero.
;   FieldsAndRows is a pointer to the memory which will receive the fields
;                 and rows. The fields and rows are returned as a $VT:$BS
;                 delimited text string.
;   SizeOfFieldsAndRows is both passed and returned. Pass the size of
;                       FieldsAndRows. It must be at least the size of
;                       the returning fields and rows. The actual length
;                       of the returing fields and rows is returned.
;                       If the passed size is too small then the returning
;                       length will be set to -1.
Prototype  sluSelStr(Statement.p-ascii, ModChars.p-ascii , FieldsAndRows.p-ascii, d)

Prototype  sluSelStr(Statement.p-ascii, ModChars.p-ascii , *FieldsAndRows.Ascii, *SizeOfFieldsAndRows.Long)

len.l = 10000
buffer.i = AllocateMemory(len)

sluSelStr(Statement$, ModChars$, buffer, @len)

If len = -1
  ; buffer too small
Else
  s.s = PeekS(buffer,-1,#PB_Ascii)
EndIf

FreeMemory(buffer)

t57042 wrote:What is wrong with this?

buffer to small and a repetition of the result of the previous query

t57042 wrote:I get 'buffer to small'

Import "SQLiteningU.lib"
    sluF(ColumNumber.l, *FieldValue, *SizeOfField, SetNumber.l = 0)
    sluGetRow(SetNumber.l = 0, ModChars.p-ascii = 0)
    sluOpen(FileName.p-ascii = 0 , ModChars.p-ascii = 0)
    sluSel(Statement.p-ascii, SetNumber.l = 0, ModChars.p-ascii = 0)
    
    sluGetColumnName(colNumber.l, *colName.ascii, *SizeOfColumnName.Long, SetNumber.l)
    sluGetTableColumnNames(TableName.p-ascii, *colName.ascii, *SizeOfColumnName.Long)
    sluGetError(*buffer.Ascii, *bufferLen.Long)
EndImport

Import "SQLitening.lib"
    slGetColumnCount(SetNumber.l = 0)
EndImport

Procedure.s slGetError()
    Protected err$
    Protected len.l = 1024
    Protected buffer.i = AllocateMemory(len)
    
    sluGetError(buffer,@len)
    If len <> -1
        err$ = PeekS(buffer,-1,#PB_Ascii)
    EndIf
    
    FreeMemory(buffer)
    ProcedureReturn err$
EndProcedure


err = sluOpen (GetPathPart(ProgramFilename())+"sample.db3")
If Not err
    
    err = sluSel("Select * from Parts where rowid < 11") 
    If Not err
        
        Debug "ColumnCount: "+slGetColumnCount()
        
        Debug "sluGetRow:"
        While sluGetRow()
            
            Rec$ = ""
            For i = 1 To 4
                buffer$ = Space(100)
                len = Len(buffer$)
                sluF(i, @buffer$, @len)
                Rec$ + PeekS(@buffer$,len,#PB_Ascii) + " - "
            Next
            Debug rec$
        Wend
        Debug "----------"
        
        Debug "sluGetColumnName:"
            len = 10000
            buffer.i = AllocateMemory(len)
            sluGetColumnName(1, buffer, @len, 0)
            If len = -1
                Debug slGetError()
            Else
                s$ = PeekS(buffer,-1,#PB_Ascii)
                Debug s$
            EndIf
            FreeMemory(buffer)
        Debug "----------"

        Debug "sluGetTableColumnNames:"
            len = 10000
            buffer.i = AllocateMemory(len)
            sluGetTableColumnNames("Parts", buffer, @len)
            If len = -1
                Debug slGetError()
            Else
                start = buffer
                name$ = ""
                Repeat
                    s$ = PeekS(start,-1,#PB_Ascii)
                    start + Len(s$) + 1
                    If s$ <> ""
                        Debug s$
                        name$ + s$ + Chr(10)
                    EndIf
                Until s$ = ""
                MessageRequester("Column names for table 'Parts'",name$)
            EndIf
        FreeMemory(buffer)
        Debug "----------"
        
        
        
    Else
        Debug "Error with sluSel(): "+Str(err)
    EndIf
Else
    Debug "can't open sample.db3 - Error code: "+Str(err)
EndIf

t57042 wrote:Thanks Danilo.
A question - where do you find the IMPORT files SQLiteningU.lib and Import "SQLitening.lib"?

sluConnect()
sluConnect("")
sluConnect("localhost")

 sluConnect("192.168.1.103")

error -16 cannot connect
statement= connect -- server = 192.168.1.103 Port = 0

;============================<[ Connect ]>=============================
;Declare Function sluConnect lib "SQLiteningU.Dll" alias "sluConnect" (_
 ;                                byval Server As Long, _
  ;                               byval Port As Long, _
   ;                              byval ModChars As Long, _
    ;                             byval OutData As Long, _
     ;                           byref SizeOfOutData As Long) As Long
;   Server is a pointer to a null-terminated string.
;   ModChars is a pointer to a null-terminated string. If not needed you
;            may pass a zero.
;   DataOut is a pointer to the returning out data. If not needed you
;           may pass a zero.
;   SizeOfOutData is both passed and returned. Pass the size of OutData.
;                 It must be at least the size of the returning out data.
;                 The actual length of the returing out data is returned.
;                 If the passed size is too small then the returning length will
;                 be set to -1.
Prototype  sluConnect(Server.p-ascii=0, Port.l = 0, ModChars.p-ascii=0,Outdata.p-ascii=0, *bufferLen.Long=0)

t57042 wrote:Connecting via an IP adress (correct and tested with ping) does not work:

Code: Select all

 sluConnect("192.168.1.103")

I get :

error -16 cannot connect
statement= connect -- server = 192.168.1.103 Port = 0

If Port is zero or omitted it will default to 51234.

t57042 wrote:

Code: Select all

;============================<[ Connect ]>=============================
;Declare Function sluConnect lib "SQLiteningU.Dll" alias "sluConnect" (_
 ;                                byval Server As Long, _
  ;                               byval Port As Long, _
   ;                              byval ModChars As Long, _
    ;                             byval OutData As Long, _
     ;                           byref SizeOfOutData As Long) As Long
;   Server is a pointer to a null-terminated string.
;   ModChars is a pointer to a null-terminated string. If not needed you
;            may pass a zero.
;   DataOut is a pointer to the returning out data. If not needed you
;           may pass a zero.
;   SizeOfOutData is both passed and returned. Pass the size of OutData.
;                 It must be at least the size of the returning out data.
;                 The actual length of the returing out data is returned.
;                 If the passed size is too small then the returning length will
;                 be set to -1.
Prototype  sluConnect(Server.p-ascii=0, Port.l = 0, ModChars.p-ascii=0,Outdata.p-ascii=0, *bufferLen.Long=0)

Prototype  sluConnect(Server.p-ascii=0, Port.l=0, modChars.p-ascii=0, *outData=0, *outDataLen.Long=0)

What is the correct port on your server? Firewall settings checked? Routing checked?

 sluconnect("localhost",0)

Declare Function slSelAry lib "SQLitening.Dll" alias "slSelAry" (byref rsStatement As String, byref wsaColsAndRows() As String, optional byval rsModChars As String) As Long

slSelAry (rsStatement String, wsaColsAndRows() String, [rsModChars String]) Long



Loads the passed Data array with all the column data from each row which is returned by the select statement passed in Statement. The returned array will be one or two dimensions.If the 'Q' ModChar is not passed then the array will be two decisions where the first dimension is equal to the number of columns while the second is equal to the number of rows (plus one if column names are returned).


If the 'Q' ModChar is passed then the array is one dimension with an entry for each row (plus one if column names are returned) and the columns will be delimited by the ascii value following the 'Q' ModChar. Be sure that none of your returning data may contain the delimiter character. If a 'c' ModChar is not passed then the index of the first row is zero and will contain the column names (lbound = 0). If a 'c' ModChar is passed then the index of the first row is one and no column names are passed (lbound = 1). Regardless of the 'c' ModChar, the first row data is always index one.


Beware that very large record sets will consume lots of memory.


ModChars:


· Em = Return errors. This will override the global return errors flag.
m is the optional message display modifier and can be:
0 = No message is displayed. This is the default.
1 = Display a warning message with OK button. Error is returned when OK pressed.
2 = Display a question message with OK and Cancel buttons.
If they press OK, error is returned. If they press Cancel, will exit process.
· e = Do not return errors, display message and exit process. This will override the global return errors flag.
· Fn = Set the size of the first Row Data Chunk (RDC). The value of n is in K so the actual size is * 1000. Maximum value for n is 200000. Greater values will be ignored. Will default to half the size of MaxChunkSize which is set in the Config file.
· Bn = Do a Begin Transaction before doing the Sel command. The type of Begin is controlled by the value of n as follows:
0 = Deferred. This is the default if n is omitted.
1 = Immediate.
2 = Exclusive.
This allows for database locking and selecting in one trip to the server.
CAUTION: If the Begin or the Select returns Busy then will restart with the Begin. Use Begin Immediate to prevent this or set ProcessMods to %gbfDoNotRetryIfBusy.
· Q# = Return a one dimension array where each column is delimited by a single character. That character is determined by the ascii value at #. The default is to return a two dimension array.
· R = Release all named locks owned by this connection after doing the SelAry.
· c = Do not return the column names as a row of data at index zero. The default is to return the column names as row zero.
· D$ = Decrypt.
· U$ = Uncompress.
· N$ = Return NULL fields as $NUL. CAUTION: You can not distinguish between a true NULL field and one that contains a single $NUL char.
· t$ = If field is DateTime then do not return time.
· z$ = If field is DateTime then do not return time if zero.
· d$ = If field is DateTime then do not return date.
· y$ = If field is DateTime then return empty if time is zero.


Note: The above $ is a comma delimited list of column numbers. Beware that this columns apply to ALL rows. So if, for example, sometimes a column is compressed and sometimes not then do not use slSelAry, rather build your own array using slSel.


Example:
slSelAry "Select F1, F2 from T1", A(), "D1,2N2" would decrypt columns 1 and 2 and also return $NUL if column 2 is Null. If table T1 had 3 rows then the array dimensions would be (1 to 2, 0 to 3). A(1,0) would contain "F1". A(2,0) would contain "F2". A(1,1) would contain the value of F1 from row 1. A(2,1) would contain the value of F2 from row 1. Etc....


Example:
slSelAry "Select F1, F2 from T1", A(), "Q9c" would return a one dimension array with the columns tab delimited and no column names in index zero.


Returns zero if processed OK. Else, depending on ModChars and the global return errors flag, will either display error and exit or will return the error number.






Code Example:


#Dim All
#Compile Exe
#Include "SQLitening.Inc"


Function PbMain()
Local st As String
Dim a(10) as String
Local i as Long


slOpen "sample.db3"
slSelAry "SELECT * FROM Parts LIMIT 10", a(), "Q9"


ST = a(0) & $crlf & $crlf
FOR i = 1 to 10
ST = ST & a(i) & $crlf
NEXT

? st, 0, "slSelAry Test"
End Function

t57042 wrote:

slSelAry (rsStatement String, wsaColsAndRows() String, [rsModChars String]) Long

Loads the passed Data array with all the column data from each row which is returned by the select statement passed in Statement. The returned array will be one or two dimensions.