Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » How do I load sqlite3 .sql with triggers on it?
icon5.gif  How do I load sqlite3 .sql with triggers on it? [message #37155] Fri, 31 August 2012 15:38 Go to next message
rxantos is currently offline  rxantos
Messages: 72
Registered: October 2011
Member
How do I load a .sql with triggers?

I tried to load the .sql to a String and then use Sqlite3PerformScript;

I also tried SQL.Execute, but that only executes one line.

Sqlite3PerformScript runs until it gets to statements like this:
create trigger if not exists ti_usuario after insert on USUARIO begin
	update USUARIO set UTIME = datetime('now') where ID = new.ID;
end;


and gives me an error like this:
ERROR near "ID": syntax error(0): Preparing: create trigger if not exists ti_usuario after insert on USUARIO begin
	update USUARIO set UTIME = datetime('now') where ID = new.ID


The same .sql compiles flawlessly to a db when using the sqlite3 program.

What call shall I use to load a .sql directly?

Update:
It seems that the error is caused because Sqlite3PerformScript dives the trigger statement into two. Eating the ';' in the process.

Note that a trigger is ended by a "; end;" and not by a simple ';'. Thus its a single statement.

http://www.sqlite.org/lang_createtrigger.html

The following is a work around hack based on Sqlite3PerformScript.

Hack, because it will just work for statements started with "create trigger "

Updated the hack.
Now it needs "create trigger " at the start of the line and "end;" on its own line. That way multi statement triggers are possible. Still a hack though.

bool Sqlite3PerformScript_hack(const String& txt, StatementExecutor& se, Gate2<int, int> progress_canceled)
{
	const char* text = txt;
	const char * p;
	static const char szCreateTrigger[] = "create trigger ";
	static const size_t szCreateTrigger_len = sizeof(szCreateTrigger) - 1;
	
	for(;*text; text++) {
		String stmt;
		bool isTrigger = false;

		while(*text <= ' ' && *text > '\0') {
			text++;
		}
		
		if('\0' == *text) {
			break;
		}
		
		isTrigger = (0 == _strnicmp(szCreateTrigger,text, szCreateTrigger_len));

		for(;*text;) {
			if(';' == *text) {
				if(isTrigger) { 
					p = text - 3;
					if(!_strnicmp("end;",p,4)) {
						--p;
						if((32 >= *p)||(';' == *p)) {
							break;
						}
					}
					stmt.Cat(*text++);
					continue;
				}
				break;
			} else if('\'' == *text) {
				text = Upp::Sqlite3ReadString(text, stmt);
			} else if('\"' == *text) {
				text = Upp::Sqlite3ReadString(text, stmt);
			} else {
				stmt.Cat(*text++);
			}
		}

		if(progress_canceled(text - txt.Begin(), txt.GetLength())) {
			return false;
		}

		if(!se.Execute(stmt)) {
			return false;
		}
	}

	return true;
}

[Updated on: Mon, 03 September 2012 05:09]

Report message to a moderator

Re: How do I load sqlite3 .sql with triggers on it? [message #37228 is a reply to message #37155] Fri, 07 September 2012 11:20 Go to previous message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Sorry for confusion, some "deprecating" work to do here, anyway DB specific *RunScripts have been replaced by common

SqlPerformScript

some time ago.

I yet have to change reference examples and add comments...

Mirek

Previous Topic: Problem when call a BIG stored procedure !
Next Topic: XmlNode and text
Goto Forum:
  


Current Time: Thu Apr 25 18:19:00 CEST 2024

Total time taken to generate the page: 0.01959 seconds