What is about SQLite Methods like AExecute, LastError or Interrupt...

[berndgoedecke]

Hello together,
does anybody know how to use SQLite Methods from the SQlite Object Reference in B4P??
Especially the AExcute method is very helpfull to do insertions. No Apostrophs, Chr(34) .... -constucts are needed, you put your Values into an array and insert it to the table.
In NSBasic it is used in this way:
arr(0) = UrAktTab 'IDStr
arr(1) = NewFi 'TNam
arr(2) = UrSpAnz 'HGroesse
arr(3) = FiAusdruck 'HVeraendert
arr(4) = CStr(SpAnz)
arr(5) = CNa
arr(6) = ColW
sqlins = "INSERT INTO [SysDBObs] VALUES (?,?,?,?,?,?)"
db.Execute ("BEGIN TRANSACTION")
db.AExecute sqlins, arr
db.Execute ("COMMIT TRANSACTION")
If db.lastError <> "" Then
MsgBox "Error occured: " & db.lastError
db.lastError = ""
End If
LastError is also usefull, but I don't know how to use.

Best regards

berndgoedecke

[Erel]

Support for parameterized queries will be added in the next update of the SQL library.
The syntax will not be exactly as you described but it will make it easier to build complex queries.

When an error occurs, the error is "thrown" and if not caught by an ErrorLabel the error message will be shown.

[berndgoedecke]

Hello Erel,
it's a late reply, but now, actual I have a problem with Datasets that have " and ', so it would be very helpful to have the AExecute -function to avoid complications with the SQL-special keys.
If the Update is not forthcoming it makes sense to create a workaround.

Best regards

berndgoedecke

[Erel]

The SQL library now supports (named) parameterized queries: SQL library was updated - V1.4

[berndgoedecke]

Hello Erel,
in the meantime I've tested the new Parameter methods in the Sql.dll. It works fine with Text and Varchar, but what is about Real and Integer. Curretly I try to get a table with all Sqlite3 Storage classes(NULL,INTEGER,REAL,TEXT,BLOB). I've tried different Methods to convert a formated String (eg.:1,154,125.456897) but REAL could not be inserted in the table as REAL.
One suspicion for the incompatibility is that Value in SetParameter is a String,
another is the SQLite-Version 3.3.1.2 you are using for the Sql.dll.
On Sqlite.org I've seen that later Version get a Bug-Fixed for handling Real numbers.
On the way, is it possible rename the native SQLite-Library in System.Data.SQLite.DLL to get a new SQLite Version?

I'm thankful for every hint.

Best regards
berndgoedecke

[Erel]

I don't see any problem with REAL values.
See this code for example:

Sub Globals

End Sub

Sub App_Start
    con.New1
    cmd.New1("",con.Value)
    Con.Open("Data Source = " & AppPath & "\1.db")
    CreateTable
    cmd.AddParameter("value1")
    cmd.CommandText = "INSERT INTO table1 (col1) VALUES (@value1)"
    For i = 1 To 2 Step 0.33
        cmd.SetParameter("value1",i)
        cmd.ExecuteNonQuery
    Next
End Sub
Sub CreateTable
    cmd.CommandText = "CREATE TABLE IF NOT EXISTS table1 (col1 REAL)"
    cmd.ExecuteNonQuery
End Sub

Basic4ppc uses a modified version of System.Data.SQLite.dll and therefore it is not possible to simply replace the file.

[berndgoedecke]

Hello Erel,
thanks for your reply. The error was that SQLite don't understand a formated String like eg.:1,154,125.456897. It must be 1154125.456897.
StrReplace or Regex.Replace(because it's implemented anyway) is the choice to
get the right formatting.

Best regards

berndgoedecke

[berndgoedecke]

Hello Erel,
just in time I have a problem with the skin of Date-values in SQLite. SQLites-OleDateTime functions may be not compatible with the B4P Date Format , but they can be useful to be more compatible to MS applications like Outlook etc.(see also Problems with outlook.dll)
Is there a way to use this functions with the avaliable command set, or is another Update needed ??

best regards

berndgoedecke

[Erel]

What problems do you encounter with the date format?

[berndgoedecke]

If I use the SQLite3 Session parameter function RefDate() the result is a Real Number as it is shown in the attached RefDate.jpg. So it is proved that the OLE-DateFormat (startdate: 12/30/1899) is used by SQLite3. Real numbers are also used by the useful OLEDateDiff(X,Y,Z) function. SQLite is not able sort real numbers, but you can use Sorted By abs(Date) to get a numeric sort order instead of an alphabeticly.(For sorting, an integer Date should be better)
SQLite3-OleDate functions are interpreting the Real number as a formated string as it is shown in OleDateTime.jpg

To get the "real"-date from a String I'wrote this:
(The whole Source is here:Listview.dll)
Dim Dop(0) As Double
'
'
'
x = "30.12.1899" '0 Date correction
DNp = DateParse(x)
x = "01.01.4501" 'max= 949998
DMp = DateParse(x)
DKorr= 949998 /(DMp-DNp)
'
'
'
Dop(0) = (DateParse (TempArr(j)) -DNp)* DKorr
cmd.SetParameter(ParmName(j),Dop(0))

The problem i now have, is how to use the the functions that are avaliable in SQLite.

best regards

berndgoedecke