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

HowardTheDuck

Member
Licensed User
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.

B4X:
#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

Expert
Licensed User
Longtime User
Its INSERT OR IGNORE, not INSERT IGNORE. (for SQLite, MySQL may accept INSERT IGNORE)
 
Last edited:
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Upvote 0

HowardTheDuck

Member
Licensed User
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.
 
Upvote 0
Top