B4J Question Mysql and "LOAD DATA INFILE"

[micro]

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

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

this is the exception

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

Second test whitout arguments

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

exception

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]

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

 

[Erel]

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

 

[micro]

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]

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]

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]

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]

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]

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