B4J Question [SOLVED] SQL questions re: INSERT IGNORE, and EndTransaction

[HowardTheDuck]

Two questions:

1. In an SQL query, is the IGNORE command somehow not valid syntax when passed from a B4J program?

In the sample program below, when I include IGNORE in the sqlquery I get an error mesage in the logs:
(SQLException) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "IGNORE": syntax error)

Removing the "IGNORE" statement allows it proceed as expected.


2. In one of the guide books, I found an example of some code which suggested using BeginTransaction etc under some circumstances; it also says .EndTransaction is required to complete it. Has EndTransaction been deprecated or something? When I try to type it in the IDE, it generates an "Unknown member: endtransaction" error.

#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
    #AdditionalJar: sqlite-jdbc-3.7.2
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    
    Public SQL1 As SQL ' requires jSQL library
    Public Query As String
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.Show
    
    SQL1.InitializeSQLite(File.DirData("ExamplesSQL"),"test.db", True)
    Query = "CREATE TABLE IF NOT EXISTS Stats (ID INT PRIMARY KEY, COL_A VARCHAR(255))"
    
    SQL1.ExecNonQuery(Query)

    AddNew
    
End Sub

'Return true to allow the default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

Private Sub AddNew
    SQL1.BeginTransaction
    Try
            Dim ID As Int
        For i = 0 To 10000
            ID = i
            Dim sqlquery As String
            sqlquery = "INSERT IGNORE INTO Stats VALUES(" & ID & "," & Rnd(0,1000000) & ")"
            SQL1.ExecNonQuery(sqlquery)
        Next
        Log("DB updated")
        SQL1.TransactionSuccessful
    Catch
        Log(LastException)
    End Try
    SQL1.EndTransaction
End Sub

 

[Daestrum]

Its INSERT OR IGNORE, not INSERT IGNORE. (for SQLite, MySQL may accept INSERT IGNORE)

 

[DonManfred]

For i = 0 To 10000
ID = i
Dim sqlquery As String
sqlquery = "INSERT IGNORE INTO Stats VALUES(" & ID & "," & Rnd(0,1000000) & ")"
SQL1.ExecNonQuery(sqlquery)
Next

I suggest to use 10000x AddNonQueryToBatch
followed by one ExecNonQueryBatch
It is probably faster
ExecNonQueryBatch is doing a transaction internally.

 

[HowardTheDuck]

Thank you both. I copied the INSERT IGNORE statement from some of my working php/mysql code, guess I'd better keep an eye on that. I had (apparently incorrectly) assumed the SQL syntax was the same.