Need some help on "ExecNonQuery2"

Yafuhenk · Dec 8, 2011

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

B4X:

   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

Erel · Dec 8, 2011

Are you getting any errors (check the logs in the logs tab)?

NJDude · Dec 8, 2011

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.

Yafuhenk · Dec 8, 2011

Hi,

I changed to code as follows SQL1.Initialize(File.DirInternal, "test1.db", True)
Result: Still the same failure:

B4X:

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))

NJDude · Dec 8, 2011

This is your clue:

B4X:

Invalid number of parentheses

Yafuhenk · Dec 8, 2011

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:

B4X:

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

This is the result of

B4X:

Log(List1.Get(0))

: [Ljava.lang.String;@44faf8e8

This is how the first line of the csv file looks like:

B4X:

"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"

NJDude · Dec 8, 2011

A couple of things, you don't need double quotes to indicate the delimiter:

B4X:

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

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

Count the parentheses:

B4X:

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

You have 4 "(" and 3 ")" you are missing one.

Yafuhenk · Dec 8, 2011

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:

B4X:

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

B4X:

   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

stevel05 · Dec 8, 2011

You don't need to specify the loop variable in the Next part of a for/next loop.

Next I

should just be

Next

eps · Dec 8, 2011

What is statement 45?

Yafuhenk · Dec 8, 2011

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

Henk

Yafuhenk · Dec 8, 2011

Hmm back again.

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

B4X:

        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

stevel05 · Dec 8, 2011

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

eps · Dec 8, 2011

What's your identifier?

Yafuhenk · Dec 9, 2011

Ok I changed some code as suggested.

B4X:

        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

eps · Dec 9, 2011

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

Yafuhenk · Dec 9, 2011

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:

B4X:

        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

Need some help on "ExecNonQuery2"

Yafuhenk

Active Member

Erel

B4X founder

NJDude

Expert

Yafuhenk

Active Member

NJDude

Expert

Yafuhenk

Active Member

NJDude

Expert

Yafuhenk

Active Member

stevel05

Expert

eps

Expert

Yafuhenk

Active Member

Yafuhenk

Active Member

stevel05

Expert

eps

Expert

Yafuhenk

Active Member

eps

Expert

Yafuhenk

Active Member