B4J Question One INSERT SQL Query for Dynamique table

[david13]

Hi All,
I'm trying to find a solution to this problem from a long time and could not find anything,
I want to send in one insert SQL query all the table lines, I can do it when I know the table fixed lines number, but in case of a dynamic table, it impossible for me to do it,
Thanks for your help

 

[Erel]

Can you give a small example of the table before the change and after it?

 

[david13]

Can you give a small example of the table before the change and after it?

Hi Erel, Thanks for the answer,

I send a table with two columns, one for the product name, and the other contains a number ( how much that product was sold)

The problem is the number of products changes depending on the company that is using my software,

When the user opens the software, all the product are loaded on a table, and since the number of product changes, the number of lines changes and so I can't use an INSERT query with a fixed number of columns,

 

[Erel]

The number of columns shouldn't be changed. Only the number of rows which is equal to the number of products, right?

I'm not sure that I understand the problem...

 

[david13]

The number of columns shouldn't be changed. Only the number of rows which is equal to the number of products, right?

I'm not sure that I understand the problem...

Yes you are right, attached my table, but I'll explain only by two colonmns and two lines,


Product table ( when loaded by my customer number 1)
detergent 1 4 USD
detergent 2 3 USD
detergent 3 2 USD

Product table (same table) ( when loaded by my customer number 2)

Pillow 1 10 usd
Pillow 2 8 usd



Code to insert all the lines for the Product table ( when loaded by my customer number 1)

INSERT INTO Amir_Test3_SQL_Stock (Produit,Stock, Date)  VALUES ('"&GetLabel(i,0).Text&"','"&liste_tableau_stock.Get(i)&"','"&DatePicker.GetDate&"'),('"&GetLabel(i+1,0).Text&"','"&liste_tableau_stock.Get(i+1)&"','"&DatePicker.GetDate&"'),('"&GetLabel(i+2,0).Text&"','"&liste_tableau_stock.Get(i+2)&"','"&DatePicker.GetDate&"'),('"&GetLabel(i+3,0).Text&"','"&liste_tableau_stock.Get(i+3)&"','"&DatePicker.GetDate&"')", Insert_Stock_Today_From_Produit)

Code to insert all the lines for the Product table ( when loaded by my customer number 1)

INSERT INTO Amir_Test3_SQL_Stock (Produit,Stock, Date)  VALUES ('"&GetLabel(i,0).Text&"','"&liste_tableau_stock.Get(i)&"','"&DatePicker.GetDate&"'),('"&GetLabel(i+1,0).Text&"','"&liste_tableau_stock.Get(i+1)&"','"&DatePicker.GetDate&"'),('"&GetLabel(i+2,0).Text&"','"&liste_tableau_stock.Get(i+2)&"','"&DatePicker.GetDate&"')", Insert_Stock_Today_From_Produit)

As you can see the code changes depending on the number of the table lines, I want to have only one code for different lines!

I can send line by line using a for loop, but as I wrote in a different post, the problem is that if internet connection problem, some lines are sent and others no, while when I use insert for all the lines in the same time, I'll rather send all the lines or don't send anything!

Hope I could explain the problem

 

[Erel]

You should never write such queries. Use parameterized queries instead.

The correct solution is to add the insert commands with SQL.AddNonQueryToBatch and then execute it with SQL.ExecNonQueryBatch. It creates a single transaction so the state is well defined.

https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content

 

[david13]

You should never write such queries. Use parameterized queries instead.

The correct solution is to add the insert commands with SQL.AddNonQueryToBatch and then execute it with SQL.ExecNonQueryBatch. It creates a single transaction so the state is well defined.

https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content

Thanks Erel, I'll start by trying to understand it then I'll apply it to my example! Thanks again