Issue with QTscript and INSERT statement

[fkuhnke]

Hi all,

I'm quite a newby to Javascript and QTscript, and I simply cannot resolve the following issue:

A field from table old should be split into several records that will be inserted in table new. For instance the original data from old

myID	value
1	abc|def
2	xxx|yyy|zzz
3	www|mmm

should become something like this in new

myID	value_no	value
1	1	abc
1	2	def
2	1	xxx
2	2	yyy
2	3	zzz
3	1	www
3	2	mmm

I know I can handle this with a little Python (export as CSV, process with Python script, re-import to database), but I just thought a user-defined function might be nice. So I came up with the following:

var ID = arguments[0];
var value_in = arguments[1];
var table_out = arguments[2];
// split value_in to array
var values_out = value_in.split('|');
//create beginning of INSERT statement 
var sql = "INSERT INTO " + table_out + " (myID, value_no, value)\nVALUES\n"
//loop through array to add the value sets
for (var i = 0; i < values_out.length; i++){
	if(i+1<values_out.length ){
		sql = sql + "('" + ID + "', '" + (i+1) + "', '" + values_out[i] + "'),\n";
	}
	//semicolon instead of trailing comma for the last value set!
	else{
		sql = sql + "('" + ID + "', '" + (i+1) + "', '" + values_out[i] + "');";
	}
}
db.eval(sql);
return(sql);

When I call this function with SELECT split_record(myID, value, 'new') FROM old; the SQL statements created by the function look like the example below (here the result for the first record):

INSERT INTO new (myID, value_no, value)
VALUES
('1', '1', 'abc'),
('1', '2', 'def'); 

However, for some reason the INSERT statement is executed twice, and I end up with duplicate records in the table new. I assume that my approach to trigger the function by a SELECT statement and/or the way how I pass the parameters to the function might be faulty, but I'm stuck completely.
What am I doing wrong? Any advice is appreciated ...

All the best and thanks in advance,
\frank

[pawelsalawa]

SQLiteStudio indeed does execute your query twice. Why?
The thing is that SQLiteStudio always counts results of the SELECT query. Unfortunately SQLite3 does not provide API to discover number of results - you need to either fetch all results (sometimes billions) and count them yourself, or count them by executing count() on the same query. SQLiteStudio does the second thing, because first one would be very slow. That means, that SQLiteStudio executes your query to get results you asked for and then it re-executes the query, just with count() instead of any result columns to find out how many results are there in total.

A way to work around it would be to use your function in other query than SELECT. For example create a temp table, like this:

CREATE TEMP TABLE trash (value);

and then you can do: INSERT INTO trash VALUES SELECT split_record(myID, value, 'new') FROM old;

This way you will have whatever is returned by return(sql); inside of the trash table, but the most importantly SQLiteStudio will not double-execute this query, because it's INSERT, not SELECT.

[pawelsalawa]

...and by the way - this (your problem of double execution) was probably the reason why I did not recommend (in manual - https://github.com/pawelsalawa/sqlitestudio/wiki/ScriptingQt) to do any data altering operations inside of custom SQL functions.

[fkuhnke]

Hi Pawel,

Thanks for looking into that and pointing out the cause of the issue.
I felt like something that is going on behind the curtains leads to this effect, but of course I could not be sure ...
I think I'll go for Python when I have to split records (of course I should work directly on the database then); I guess this might be a cleaner approach :-)

Apart from this, let me confirm that SQLiteStudio is an excellent tool and I appreciate it very much!

Thanks and all the best for the new year,
\frank