wrapper - dll translation

[Danilo]

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)

Code: Select all

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

Usage:

Code: Select all

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)

Same principle as sluF.

[t57042]

Hi Danilo,
sluselstr ok. thanks

What is wrong with this?

Code: Select all

'========================<[ Get Column Name ]>=========================
;Declare Sub      sluGetColumnName lib "SQLiteningU.Dll" alias "sluGetColumnName" ( _
 ;                                byval ColumnNumber As Long, _
  ;                               byval ColumnName As Long, _
   ;                              byref SizeOfColumnName As Long, _
    ;                             byval SetNumber As Long)
;   ColumnName is a pointer to the memory which will receive the column
;              name(s).  Will return all column names if the passed column
;              number is zero. If all column names are returned then it is
;              a delimited text string which is $NUL seperated.  A Null
;              (hex '00') will be added to end (meaningless if all column
;              names are returned).
;   SizeOfColumnName is both passed and returned. Pass the size of ColumnName.
;                    It must be at least the size of the returning column name + 1.
;                    The actual length of the returing column name(s) is returned.
;                    If the passed size is too small then returning length will be
;                    set to -1.
Prototype  sluGetColumnName(colNumber.l, *colName.ascii, *SizeOfColumnName.Long, SetNumber.l=0)

Code: Select all

lengt.l = 10000
            buffer.i = AllocateMemory(lengt)
            sluGetColumnName(1, buffer, @lengt)
            If lengt = -1
              Debug "buffer too small"
            Else
              s$ = PeekS(buffer,-1,#PB_Ascii)
            EndIf 
            FreeMemory(buffer)
            Debug s$

[Danilo]

What is wrong with this?

Looks good. What do you get?

[t57042]

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

Richard

[t57042]

CORRECTION:

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

I get 'buffer to small'

Richard

[Danilo]

I get 'buffer to small'

You get -1.

-1 could also mean there is an error. Use sluGetError() / slGetError() to get the error description.
In this case, it returns "-14 = Invalid set number" - because sluGetColumnName() requires a valid SetNumber.

I guess you want to get the column names of the table!? Use sluGetTableColumnNames() for that.

See example:

Code: Select all

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]

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

Richard

[Danilo]

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

Found them in the same directory where I had run the following code: Run this code to create the import libs

[t57042]

Thanks Danilo, another problem.
The sluConnect function connects to the server to work in remote mode.

Following 3 lines work:

Code: Select all

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

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

Richard

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)

[Danilo]

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

Check which port is used and set it as 2nd argument.

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

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

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)

Outdata is outgoing. You can't use p-ascii with it - p-ascii is only for incoming parameters.

Use same method ("buffer, @len") as before:

Code: Select all

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

Don't forget: sluConnect returns 0 for OK, error number if failed.

[t57042]

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

How can I check that? Is the default port not always ok?
btw

Code: Select all

 sluconnect("localhost",0)

--> this works

Richard

[t57042]

This works ok:

Code: Select all

sluconnect("127.0.0.1",0)

[t57042]

Problem solved - the config file had to be adapted.

Richard.

[t57042]

This function returns the result in an a array . Is this possible with Purebasic?
Richard

Code: Select all

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

[Danilo]

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.

AFAIK that's not possible with PureBasic.