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 » SqlPerformScript bug: Not doing anything if script starts with "BEGIN;" and ends with "END;"
SqlPerformScript bug: Not doing anything if script starts with "BEGIN;" and ends with "END;" [message #60208] Fri, 13 October 2023 03:12 Go to next message
zsolt is currently offline  zsolt
Messages: 698
Registered: December 2005
Location: Budapest, Hungary
Contributor
Try this simple code:
#include <Core/Core.h>
#include <plugin/sqlite3/Sqlite3.h>

using namespace Upp;


CONSOLE_APP_MAIN
{
	String sql_script = R"sql(
		BEGIN;
		create table TBL (
			COL integer
		);
		insert into TBL values(123);
		END;
	)sql";

	Sqlite3Session session;
	String file_name = ConfigFile("SqlPerformScript_Test.db");
	DeleteFile(file_name);
	session.Open(file_name);
	SqlPerformScript(session, sql_script);
	Sql sql(session);
	sql * Select(SqlId("COL")).From(SqlId("TBL"));
	if(!sql.Fetch()){
		Cerr() << EOL << "Nothing in table" << EOL;
		return;
	}
	int v = sql[0];
	Cout() << EOL;
	if(v == 123)
		Cout() << "It is OK. Value is: ";
	else
		Cout() << "Bad value: ";
	Cout() << IntStr(v) << EOL;
}


It can be fixed with this change in SqlPerformScript():
-		if(c == ';' && level == 0 && !esc) {
+		if(c == ';' && !esc) {


I don't know what this level thing should do, but removing level variable and related code seems to be working well also.
Any idea?

@@ -18,32 +18,25 @@ StatementExecutor& SQLStatementExecutor() {
 }
 #endif
 
 bool SqlPerformScript(SqlSession& session, Stream& script,
                       Gate<int, int> progress_canceled, bool stoponerror)
 {
 	String stmt;
-	int level = 0;
 	bool ok = true;
 	bool esc = false;
 	while(!script.IsEof()) {
 		int c = script.Term();
 		if(IsAlpha(c) || c == '_') {
 			String id;
 			while(IsAlNum(script.Term()) || script.Term() == '_') {
 				c = script.Get();
 				stmt.Cat(c);
 				id.Cat(ToUpper(c));
 			}
-			if(!esc) {
-				if(id == "BEGIN")
-					level++;
-				if(id == "END")
-					level--;
-			}
 		}
 		else
 		if(c == '\'') {
 			stmt.Cat(c);
 			script.Get();
 			for(;;) {
 				c = script.Get();
@@ -65,15 +58,15 @@ bool SqlPerformScript(SqlSession& session, Stream& script,
 			if(script.Term() == '$') {
 				script.Get();
 				stmt.Cat('$');
 				esc = !esc;
 			}
 		}
 		else
-		if(c == ';' && level == 0 && !esc) {
+		if(c == ';' && !esc) {
 			Sql sql(session);
 			session.ClearError();
 			int q = 0;
 			while(stmt[q] == '\r' || stmt[q] == '\n')
 				q++;
 			stmt = stmt.Mid(q);
 			if(!sql.Execute(stmt)) {
@@ -81,20 +74,14 @@ bool SqlPerformScript(SqlSession& session, Stream& script,
 				if(stoponerror)
 					break;
 			}
 			stmt.Clear();
 			script.Get();
 		}
 		else {
-			if(!esc) {
-				if(c == '(')
-					level++;
-				if(c == ')')
-					level--;
-			}
 			if(c != '\r') {
 				if(session.GetDialect() == ORACLE && c == '\n')
 					stmt.Cat('\r');
 				stmt.Cat(c);
 			}
 			script.Get();
 		}
Re: SqlPerformScript bug: Not doing anything if script starts with "BEGIN;" and ends with "END;" [message #60211 is a reply to message #60208] Fri, 13 October 2023 11:02 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13986
Registered: November 2005
Ultimate Member
Well, the problem is that SqlPerformScript is primary dedicated to DDL and must support creation of triggers:

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

Unfortunately, with most SQLs, you have to send trigger body as single statement (that probably includes Sqlite3 as well). Thus we need to detect BEGIN/END and supress sending after ';' until END is reached, then send the whole thing at once. That is the reason for the 'level' variable.

I am not sure how to solve your problem frankly. I would be happy with 'do not use SqlPerformScript for transactions' note in docs, but if you have any suggestions... But I do not want to overcomplicate SqlPerformScript by e.g. detecting whether the BEGIN is function body or start of transactions, that would be hardly manageable, which means chances are slim...

Mirek
Re: SqlPerformScript bug: Not doing anything if script starts with "BEGIN;" and ends with "END;" [message #60212 is a reply to message #60211] Fri, 13 October 2023 12:30 Go to previous message
zsolt is currently offline  zsolt
Messages: 698
Registered: December 2005
Location: Budapest, Hungary
Contributor
Thanks for the clarification.

I create a database dump in a web server and push the whole thing to the socket as an HTTP response body.
At the start I don't know the size of it, so I don't fill the Content-Length header.
I thought it would be a good idea to put it in a transaction to ensure, that the client read the whole thing until the end.

I don't need triggers there, so I think, I will use my modified version with no level handling.

Thanks again.
Previous Topic: Sqlite encryption
Next Topic: PostgreSql does not handle BOOL correctly
Goto Forum:
  


Current Time: Sat Jun 22 23:34:58 CEST 2024

Total time taken to generate the page: 0.01729 seconds