[bug_fixed] Zquery and Ztable not accept numeric database

[Jaimefig]

I migration from zeoslib 5.5.0 to 6.6.0.
I am use mysql 4.0.18-nt e my database's is numeric (1,50,99,...).
Zconnection connect ok, but, when active the Zdataset or ztable the error:

"You have a syntax error in sql statement near '1 LIKE'%" line 1.

I tested com alphanumeric database (test,mysql) with good results.

Regards,

[mdaems]

Are you telling your databases have numeric names?
In that case you should use backticks in your queries or in the database name, I think. (eg select a from `2`.testtable) Anyway, not zeoslib is complaining : it's mysql who says the query is not right.
To see what query is launched exactly you can add a zsqlmonitor in your project and let it write a trace file. Maybe 6.6.0 does make a query that is a little different from 5.5.0 queries.

Mark

[Jaimefig]

I tested with various ticks and problem persisting.
The logs from ZSQLMonitor is:

2006-11-08 12:50:26 cat: Execute, proto: mysql-4.1, msg: SHOW TABLES FROM 99 LIKE 'clientes', errcode: 1064, error: Você tem um erro de sintaxe no seu SQL próximo a '99 LIKE 'clientes'' na linha 1
2006-11-08 12:51:45 cat: Execute, proto: mysql-4.1, msg: `select * from clientes`
, errcode: 1064, error: Você tem um erro de sintaxe no seu SQL próximo a 'select * from clientes`' na linha 1
2006-11-08 12:52:15 cat: Disconnect, proto: mysql-4.1, msg: DISCONNECT FROM "99"
2006-11-08 12:52:19 cat: Connect, proto: mysql-4.1, msg: CONNECT TO "`99`" AS USER "root", errcode: 1049, error: Banco de dados '`99`' desconhecido
2006-11-08 12:52:31 cat: Connect, proto: mysql-4.1, msg: CONNECT TO "'99'" AS USER "root", errcode: 1049, error: Banco de dados ''99'' desconhecido
2006-11-08 12:52:46 cat: Connect, proto: mysql-4.1, msg: CONNECT TO ""99"" AS USER "root", errcode: 1049, error: Banco de dados '"99"' desconhecido
2006-11-08 12:54:43 cat: Connect, proto: mysql-4.1, msg: CONNECT TO "99" AS USER "root"
2006-11-08 12:55:11 cat: Execute, proto: mysql-4.1, msg: select * from `99`.clientes

2006-11-08 12:55:12 cat: Execute, proto: mysql-4.1, msg: SHOW TABLES FROM 99 LIKE 'clientes', errcode: 1064, error: Você tem um erro de sintaxe no seu SQL próximo a '99 LIKE 'clientes'' na linha 1

[]'s

[zippo]

SHOW TABLES FROM 99 LIKE 'clientes'

Should be

SHOW TABLES FROM `99` LIKE 'clientes'

[Jaimefig]

SHOW TABLES FROM 99 LIKE 'clientes' is maked by zeos.
My sql query is:

select * from clientes.

In the Zconnection properties.database I try set:

99, '99', `99`, "99", \`99\`

In the Zquery properties.SQL I try set:

select * from clientes and `select * from clientes`

The problem persist.

[mdaems]

Indeed,

Your last query was good!! (select * from `99`.clientes)
It's Zeos who makes the 'wrong' show tables statement. I'll move this topic to the bugs reports forum.
Anyway, I want to mention that using numbers as a database name is not common practice. There are databases refusing this kind of names... Mysql however permits it so we could try to fix the issue.

Mark

[zippo]

I agree with mdaems - it's a very risky practice.
Suggestion: Make a table prefix, for example "tbl1" or "application1"... etc.

[mdaems]

Hi Jaimefig,

I think I solved your problem by treating all identifiers starting with a number as 'specialcase', thus forcing zeoslib to quote them.
If you use SVN : try svn testing branch rev 174 or higher.
If not, replace this function in ZDbcMetadata.pas:
[syntax="delphi"]
function TZDefaultIdentifierConvertor.IsSpecialCase(const Value: string): Boolean;
var
I: Integer;
begin
Result := False;
if (Value[1] in ['0'..'9']) then
begin
Result := True;
exit;
end;
for I := 1 to Length(Value) do
begin
if not (Value in ['A'..'Z','a'..'z','0'..'9','_']) then
begin
Result := True;
Break;
end;
end;
end;

[/syntax]

That does the trick here.

Can you confirm it is working?

Mark

[Jaimefig]

Hi,

Now is ok.
I replaced the function.


Thank you very much.