I have an error in a complex sql select statement. anyone can see where is my mistake ?
[code] ch = “SET @table_schema = '”+DatabaseName+"’;"
ch.AppendText “SET GROUP_CONCAT_MAX_LEN=131072;”
ch.AppendText “SET @selects = NULL;”
ch.AppendText “Select GROUP_CONCAT(”
ch.AppendText “‘SELECT “”’, table_name,’”" as TABLE_NAME, COUNT(*) as TABLE_ROWS FROM ', table_name, ''"
ch.AppendText " SEPARATOR ’
UNION
‘) INTO @selects"
ch.AppendText " FROM information_schema.TABLES"
ch.AppendText " WHERE TABLE_SCHEMA = @table_schema And ENGINE = ‘InnoDB’ And TABLE_TYPE = ‘BASE TABLE’;"
ch.AppendText "Select CONCAT_WS(’
UNION
',"
ch.AppendText " CONCAT(‘SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = ?"
ch.AppendText " AND ENGINE <> ““InnoDB”” AND TABLE_TYPE = ““BASE TABLE””’),@selects) INTO @selects;"
ch.AppendText “PREPARE stmt FROM @selects;”
ch.AppendText “EXECUTE stmt Using @table_schema;”
ch.AppendText “DEALLOCATE PREPARE stmt;”
rs = SQLSelect( ch)
[/code]
appendtext simply makes “ch=ch+”
if I do this in xojo, returned rs is not null, but has no field and no record and there is no sqlerror raised
if I copy the ch string with the xojo debugger, paste it in any mysql editor, the request returns the right result
that is a list of all tables names in the first column, and the rowcount in the second column.
I found this on stackexchange, and it works pretty well (but not in xojo with my method for now …)
thanks.
You are putting multiple SQL statements together… basically creating a SCRIPT…
SQLSElect… is doing one of two things…
Either ONLY executing the FIRST statment…
or it is executing all of them (which I doubt), but since the LAST statement doesn’t return a recordsset, then your result is empty… The values from the SELECT that IS there are lost by the time you get to the end.
My TADPOLE app has special parsing code to take statements such as this, and break them into their parts, execute each one , error check at each step, and abort with rollback if required.
When I have complex SQL statements, I open Microsoft Access, SLQ query designer, or some other tool that has a graphical user interface to create queries. After I have a working query, I then go to SQL view and copy the SQL code. Then I copy this code to Xojo, and modify the code to allow my variable to be used. I have found the using all caps makes it easier to identify the items to change. For example:
select all from customers where city = ‘HOUSTON’;
I put Houston in all caps because in Xojo, Houston will be a variable such as myCity, and it is clear what part of the SQL code needs to me modified so it is dynamic.
I executed the Xojo code and it generates the following SQL:
SET @table_schema = 'myschemaname';
SET GROUP_CONCAT_MAX_LEN=131072;
SET @selects = NULL;
Select GROUP_CONCAT('SELECT "', table_name,'" as TABLE_NAME, COUNT(*) as TABLE_ROWS FROM `', table_name, '`' SEPARATOR '\
UNION\
') INTO @selects FROM information_schema.TABLES WHERE TABLE_SCHEMA = @table_schema And ENGINE = 'InnoDB' And TABLE_TYPE = 'BASE TABLE';
Select CONCAT_WS('\
UNION\
', CONCAT('SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND ENGINE <> "InnoDB" AND TABLE_TYPE = "BASE TABLE"'),@selects) INTO @selects;
PREPARE stmt FROM @selects;
EXECUTE stmt Using @table_schema;
DEALLOCATE PREPARE stmt;
The SQL executes in MySQL Workbench and returns a result so the problem appears to be within Xojo.
The problem might be because you are trying to execute multiple MySQL command in one hit (ie: a script). You could try executing them one by one.
If you are still stuck here are some alternatives to try…
If you don’t care about having 100% accurate row counts you could just use the information currently held in the MySQL Information Schema:
SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE
TABLE_SCHEMA = "myschemaname" AND TABLE_TYPE = "BASE TABLE"
You could achieve something similar to your original code via the following:
a. Execute a statement to set GROUP_CONCAT_MAX_LEN
SET GROUP_CONCAT_MAX_LEN=131072;
b) Execute a statement which generates the row count SQL:
SELECT GROUP_CONCAT('SELECT ''',table_name,''' AS name, COUNT(*) AS rowcount FROM ', table_name SEPARATOR '\\rUNION ALL\\r') FROM information_schema.tables WHERE table_schema = 'myschemaname';
[quote=338817:@Kevin Gale]If you are still stuck here are some alternatives to try…
If you don’t care about having 100% accurate row counts you could just use the information currently held in the MySQL Information Schema:
[/quote]
I precisely want to avoid this by using this complex query…
[quote=338817:@Kevin Gale]The SQL executes in MySQL Workbench and returns a result so the problem appears to be within Xojo.
[/quote]
this was my first impression too, but I wanted to have other’s opinion. thanks.
That is not something i’ve personally tried. It could have something to do with the script preparing its own statement. Have you tried removing the DEALLOCATE part to see if that helps?
If it solves the problem I would probably break it up into multiple queries (maybe like how I suggested). The biggest overhead will probably be from retrieving the count from multiple tables rather than executing two or three queries.
it works if I begin a transaction at the beginning of the script
then I send a sqlselect for each line of sql script.
I get my valid recordset,
then I OUTPUT A ROLLBACK OR COMMIT no matter it works the same.
thanks Kevin for leading me to the solution.
[quote=338810:@Dave S]SQLSElect… is doing one of two things…
Either ONLY executing the FIRST statment…
or it is executing all of them (which I doubt), but since the LAST statement doesn’t return a recordsset, then your result is empty… [/quote]
it gives me the same result if I remove the last sql sentence, I should in theory get a valid recordset, but it isnt.
That character next to the ’ … That worries me… the shaped quotes rarely work in any SQL I have used.[/quote]
That’s not a “shaped quote”. It’s a back-tick (chr(96), left-most key on the row of number keys on a PC keyboard) and is required in certain instances, like table names containing special characters, among others.
yes this back tick seems particular to mysql ?
I noticed it’s mandatory if a table name is a sql keyword like group or column that you can use a regular column name.