Need some help on "ExecNonQuery2"

Yafuhenk

Yafuhenk

Active Member
Licensed User
Longtime User
Hi,

This is my first post here after purchasing this wonderful software.
I am totally new to B4A by the way

Here is my question:
I am trying to import an csv file to a database.
Loading the CSV file to a List works fine, inserting all the data into a table doesn't work. Ofcourse I am doing something wrong, but I can't figure out what.
(The csv file has 14 columns and about 3500 rows)

Any help is appreciated

Code: 
   List1 = StringUtils1.LoadCSV(File.DirAssets, "test.csv", "";"")
   
   If FirstTime Then 
       SQL1.Initialize(File.DirAssets, "test1.db", True)
    End If
   SQL1.ExecNonQuery("DROP TABLE IF EXISTS table1")
        SQL1.ExecNonQuery("CREATE TABLE table1 (col_1 TEXT ,col_2 TEXT ,col_3 TEXT ,col_4 TEXT ,col_5 TEXT ,col_6 TEXT ,col_7 TEXT ,col_8 TEXT ,col_9 TEXT ,col_10 TEXT ,col_11 TEXT ,col_12 TEXT ,col_13 TEXT ,col_14 TEXT)")
   SQL1.BeginTransaction
   For I = 0 To List1.Size-1
     SQL1.ExecNonQuery2("INSERT INTO table1 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(List1.Get(I))
   Next I
   SQL1.EndTransaction
 
Sort by date Sort by votes
It doesn't work because you have the database on File.DirAssets, that directory is READ ONLY, you will have to copy the database to a read/write directory.

File.DirAssets is in the APK.
 
Upvote 0
Hi,

I changed to code as follows SQL1.Initialize(File.DirInternal, "test1.db", True)
Result: Still the same failure:
Code: 
Compiling code.                         Error
Error parsing program.
Error description: Invalid number of parentheses.
Occurred on line: 45
 SQL1.ExecNonQuery2("INSERT INTO table1 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(List1.Get(I))
 
Upvote 0
Hi,

Sorry for asking but I need to understand this.
The failures means that the numbers of columns itsn't right doesn't it?

This means that something goes wrong with the LoadCSV command.

I use this code:
Code: 
List1 = StringUtils1.LoadCSV(File.DirAssets, "test.csv", "";"")

This is the result of
Code: 
Log(List1.Get(0))
: [Ljava.lang.String;@44faf8e8

This is how the first line of the csv file looks like:
Code: 
"Sales Organization";"Sales Organization";"Article group";"Article group";"Customer";"Customer";"Country";"Country";"Postal Code";"Fiscal year/period";"Material";"Material";"Net sales costing based";"Sales volume"
 
Upvote 0
A couple of things, you don't need double quotes to indicate the delimiter:

Code: 
List1 = StringUtils1.LoadCSV(File.DirAssets, "test.csv", "";"") ' NO

List1 = StringUtils1.LoadCSV(File.DirAssets, "test.csv", ";") 'YES

Count the parentheses:

Code: 
SQL1.ExecNonQuery2("INSERT INTO table1 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(List1.Get(I))

You have 4 "(" and 3 ")" you are missing one.
 
Upvote 0
Ok, we are going somewhere now.
Parenthesis error isn't there anymore because of the bracket ")" I assume. Thanks !

At compiling however I now get this message:
Code: 
Compiling code.                         0.03
Generating R file.                      0.00
Compiling generated Java code.          Error
B4A line: 45
SQL1.ExecNonQuery2(\
javac 1.6.0_26
src\B4A\Try\trywebview.java:276: not a statement
_i;
^
1 error

I don't have any idea what it means.

Here is the code I have now
Code: 
   List1 = StringUtils1.LoadCSV(File.DirAssets, "test.csv", ";")
   If FirstTime Then 
     SQL1.Initialize(File.DirInternal, "test1.db", True)
    End If
   SQL1.ExecNonQuery("DROP TABLE IF EXISTS table1")
  SQL1.ExecNonQuery("CREATE TABLE table1 (col_1 TEXT ,col_2 TEXT ,col_3 TEXT ,col_4 TEXT ,col_5 TEXT ,col_6 TEXT ,col_7 TEXT ,col_8 TEXT ,col_9 TEXT ,col_10 TEXT ,col_11 TEXT ,col_12 TEXT ,col_13 TEXT ,col_14 TEXT)")
   SQL1.BeginTransaction
   For I = 0 To List1.Size-1
       SQL1.ExecNonQuery2("INSERT INTO table1 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(List1.Get(I)))
   Next I
   SQL1.EndTransaction
 
Upvote 0
You don't need to specify the loop variable in the Next part of a for/next loop.

Next I

should just be

Next
 
Upvote 0
Thank you all!
Changing "Next I" to "Next" was to last change to make it work

Henk
 
Last edited:
Upvote 0
Hmm back again.

The database only contains 1 row althought the list contains over 3000 rows.

Code: 
        List1 = StringUtils1.LoadCSV(File.DirAssets, "test.csv", ";")
   If FirstTime Then 
       SQL1.Initialize(File.DirRootExternal, "test1.db", True)
    End If
   Log(File.DirRootExternal)
   Log(List1.Size)
   SQL1.ExecNonQuery("DROP TABLE IF EXISTS table1")
        SQL1.ExecNonQuery("CREATE TABLE table1 (col_1 TEXT ,col_2 TEXT ,col_3 TEXT ,col_4 TEXT ,col_5 TEXT ,col_6 TEXT ,col_7 TEXT ,col_8 TEXT ,col_9 TEXT ,col_10 TEXT ,col_11 TEXT ,col_12 TEXT ,col_13 TEXT ,col_14 TEXT)")
   SQL1.BeginTransaction
   For I = 0 To List1.Size-1
     SQL1.ExecNonQuery2("INSERT INTO table1 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(List1.Get(I)))
   Next
   SQL1.EndTransaction
   WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL1, "SELECT * FROM table1", Null, List1.Size-1, True))

List1.Size = 3251
Webview1 only shows the column names
Opening test1.db, table1 gives the same result. (only column names)

Any idea?

Thanks in advance

Henk
 
Upvote 0
Have a look here

About a quarter of the way down in the InsertManyRows code example, it explains about using a try block for update transactions and the SQL Transaction successful flag.

That may help

Steve
 
Upvote 0
Ok I changed some code as suggested.

Code: 
        List1 = StringUtils1.LoadCSV(File.DirAssets, "test.csv", ";")
   If FirstTime Then 
       SQL1.Initialize(File.DirDefaultExternal, "test1.db", True)
    End If
   'Log(File.DirDefaultExternal)
   'Log(List1.Size)
   SQL1.ExecNonQuery("DROP TABLE IF EXISTS table1")
        SQL1.ExecNonQuery("CREATE TABLE table1 (col_1 TEXT ,col_2 TEXT ,col_3 TEXT ,col_4 TEXT ,col_5 TEXT ,col_6 TEXT ,col_7 TEXT ,col_8 TEXT ,col_9 TEXT ,col_10 TEXT ,col_11 TEXT ,col_12 TEXT ,col_13 TEXT ,col_14 TEXT)")
   SQL1.BeginTransaction
   Try
       For I = 0 To List1.Size-1
         SQL1.ExecNonQuery2("INSERT INTO table1 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(List1.Get(I)))
          Next
          SQL1.TransactionSuccessful
   Catch
          Log(LastException.Message) 'no changes will be made
   End Try
   SQL1.EndTransaction
   WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL1, "SELECT * FROM table1", Null, List1.Size-1, True))

The thing with the example is that is just generates some code to fill the table, where I read a csv file. It might be that something goes wrong there.
This is what I get:



Then the question about the indentifier:
I know this sounds very stupid but I don't know what you mean by that. I am really a beginner. Thanks for your patience ;)
 
Upvote 0
Each record on a DB needs to have a unique identifier... usually it's an integer... but it doesn't have to be. It may well be that you have an automatic unique identifier already given by SQLite...
 
Upvote 0
Problem solved!

I now understand what I did wrong.

I used:
SQL1.ExecNonQuery2("INSERT INTO table1 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(List1(I)))

One should however first write the content of List1(I) to a string array

This works:
Code: 
        List1.Initialize
   List1 = StringUtils1.LoadCSV(File.DirAssets, "test.csv", "";"")
   If FirstTime Then 
       SQL1.Initialize(File.DirDefaultExternal, "test1.db", True)
    End If
   SQL1.ExecNonQuery("DROP TABLE IF EXISTS table1")
        SQL1.ExecNonQuery("CREATE TABLE table1 (col_1 TEXT ,col_2 TEXT ,col_3 TEXT ,col_4 TEXT ,col_5 TEXT ,col_6 TEXT ,col_7 TEXT ,col_8 TEXT ,col_9 TEXT ,col_10 TEXT ,col_11 TEXT ,col_12 TEXT ,col_13 TEXT ,col_14 TEXT)")
   SQL1.BeginTransaction
   Try
     For I = 0 To List1.Size-1
     Dim ColumnContent() As String
     ColumnContent = List1.Get(I)
     SQL1.ExecNonQuery2("INSERT INTO table1 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(ColumnContent(0), ColumnContent(1), ColumnContent(2), ColumnContent(3), ColumnContent(4), ColumnContent(5), ColumnContent(6), ColumnContent(7), ColumnContent(8), ColumnContent(9), ColumnContent(10), ColumnContent(11), ColumnContent(12), ColumnContent(13))) 
        Next
        SQL1.TransactionSuccessful
   Catch
       Log(LastException.Message) 'no changes will be made
   End Try
   SQL1.EndTransaction
   WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL1, "SELECT * FROM table1", Null, List1.Size-1, True))

Thanks for all the replies.

Henk
 
Upvote 0
You must log in or register to reply here.

Similar Threads

J
  • Question Question
Android Question error QR scanner with R8
Replies
0
Views
1
jhgtapp
J
Erel
Server software is being upgraded
Replies
3
Views
75
LucaMs
LucaMs
A
eMedNY
Replies
0
Views
90
Alex_197
A
Daestrum
When tech works for you. (minimum effort)
Replies
0
Views
122
Daestrum
Daestrum
Robert Valentino
  • Question Question
Android Question #R8 and B4XLib crashing method not found in: okhttp3.Request$Builder
Replies
4
Views
164
Robert Valentino
Robert Valentino
Back
Top