B4J Question Mysql and "LOAD DATA INFILE"

micro · May 14, 2016

Hi to all
I have same problems to load a csv file into Mysql db.
This is the code

B4X:

sql.ExecNonQuery2("LOAD DATA INFILE " & namedir & "\" & namef & " INTO TABLE Products", Array As String("FIELDS TERMINATED BY ';'"))

this is the exception

B4X:

java.lang.Exception:  java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

Second test whitout arguments

B4X:

sql.ExecNonQuery("LOAD DATA INFILE " & namedir & "\" & namef & " INTO TABLE Products FIELDS TERMINATED BY ';'")

exception

B4X:

java.lang.Exception:  com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'F:\Progetti_SW_HW\B4J_Gest\products.csv INTO TABLE Products FIELDS TERMINATED' at line 1

tests also performed without quote char (')
Wher's the error?
Thanks

LucaMs · May 14, 2016

http://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile

Erel · May 15, 2016

Another option is to read the CSV file with StringUtils.LoadCSV and then add the records.

micro · May 15, 2016

Erel said:
Another option is to read the CSV file with StringUtils.LoadCSV and then add the records.

It's the function that always use, but if the db is too big the application waits a lot of time.
Thanks

LucaMs · May 15, 2016

micro said:
It's the function that always use, but if the db is too big the application waits a lot of time.
Thanks

Have you solved the problem?
What is the structure of the file?

Erel · May 15, 2016

micro said:
It's the function that always use, but if the db is too big the application waits a lot of time.

Make sure to create a single transaction. If it is still slow then use SQL.ExecNonQueryBatch.

LucaMs · May 15, 2016

Am I totally mad or...

I have a vague memory of the possibility to disable indexes for a table and I was doing this when I had to insert a lot of records, then I did rebuild them.
Maybe I dream, because I do not remember what were the operating system, the DB and the language!

Anyway, I did some tests and that command works (I replied yesterday in the Italian forum: if this can be useful I can bring everything here).

valentino s · May 16, 2016

I'm not sure, but it seems a problem of datas or structure.

Test with a new simple csv (don't use data already in your csv). If it works, it's not the software.

Could be:

1) structure of the tables doesn't allow duplicates, use ignore option
2) error in csv format (strings / integers/ dates)

To solve
1) use sql, not csv.
2) the error is:
near 'F:\Progetti_SW_HW\B4J_Gest\products.csv INTO TABLE Products FIELDS TERMINATED'

Is it the name of the file inside '...' ? In your example it's not.

micro · May 17, 2016

For now i solved with LoadCSV and ExecNoQueryBatch, it's quite fast.
After return on the "LOAD DATA INFILE" problem
Thanks

B4J Question Mysql and "LOAD DATA INFILE"

micro

Well-Known Member

LucaMs

Expert

Erel

B4X founder

micro

Well-Known Member

LucaMs

Expert

Erel

B4X founder

LucaMs

Expert

valentino s

Active Member

micro

Well-Known Member

Similar Threads

B4J Question Mysql and "LOAD DATA INFILE"

Well-Known Member

Expert

B4X founder

Well-Known Member

Expert

B4X founder

Expert

Active Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency