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

Discussion in 'Questions (Windows Mobile)' started by berndgoedecke, May 4, 2008.

  1. berndgoedecke

    berndgoedecke Active Member Licensed User

    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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
  3. berndgoedecke

    berndgoedecke Active Member Licensed User

    Is the Sqlite-Update forthcoming?

    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
     
  4. Erel

    Erel Administrator Staff Member Licensed User

  5. berndgoedecke

    berndgoedecke Active Member Licensed User

    Thank you for the parameter methods

    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
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    I don't see any problem with REAL values.
    See this code for example:
    Code:
    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.
     
  7. berndgoedecke

    berndgoedecke Active Member Licensed User

    Some feedback

    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
     
  8. berndgoedecke

    berndgoedecke Active Member Licensed User

    OleDateTime functions ?

    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 http://www.basic4ppc.com/forum/bug-reports/1306-problems-outlook-dll.html#post7095)
    Is there a way to use this functions with the avaliable command set, or is another Update needed ??

    best regards

    berndgoedecke
     
  9. Erel

    Erel Administrator Staff Member Licensed User

    What problems do you encounter with the date format?
     
  10. berndgoedecke

    berndgoedecke Active Member Licensed User

    Problems with date format

    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:http://www.basic4ppc.com/forum/german-forum/352-listview-dll-5.html#post14849)
    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
     
  11. berndgoedecke

    berndgoedecke Active Member Licensed User

    In addition to the problem above

    if you wonder where I get the information from, here the link to informations about OLE date/time functions:

    NDL

    There it is declared in better english.:(
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice