SQLExecute (API 1.0) vs. ExecuteSQL (API 2.0) - API 2.0 Bug?

Databases
41

[quote=459517:@Norman Palardy]
That however only deals with one issue
They still have the performance issues because they crate a new prepared statement each time and thats exactly counter to at least one reason to use a prepared statement on many db’s
Normally you would create it once then reuse it (say in a loop) to insert a lot of data without recreating the prepared statement each time
Basically you end up just not using this new API 2.0 mechanism[/quote]
depending on the Database in use, there are real server-side prepared statements and parameterised queries at the API level to differentiate, see <https://xojo.com/issue/37805>

42

[quote=459493:@Alberto DePoo]They can introduce ExecuteScriptSQL for this situation like Python Sqlite3.

Don’t worry about that, this code works:
[/quote]

I appreciate the effort, but a simple split(’;’) wont work if the script includes triggers:

-- simple contact table
CREATE TABLE contacts
(
  contact_id INTEGER
        CONSTRAINT pk_contacts_contact_id PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  telephone TEXT,
  last_update datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create triggers for table dispatch.contacts

CREATE TRIGGER contacts_BUPD BEFORE UPDATE
 ON contacts
 FOR EACH ROW
begin
	UPDATE new SET last_update=CURRENT_TIMESTAMP; -- this 'extra' semicolon is part of the syntax
end;

The semicolon is also separates the ‘sub’ statement.

43

Sorry John I obviously misunderstood what you wrote.

This bug is marked as fixed.