[bug_fixed] Zquery and Ztable not accept numeric database

In this forum all bug reports concerning the 6.x branch will be gahtered. You have the possibility to track the bug fix process.

Moderators: EgonHugeist, mdaems

Jaimefig
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 08.11.2006, 13:48

[bug_fixed] Zquery and Ztable not accept numeric database

Postby Jaimefig » 08.11.2006, 14:09

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,

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 08.11.2006, 14:26

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
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 08.11.2006, 13:48

Postby Jaimefig » 08.11.2006, 17:59

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
Silver Boarder
Silver Boarder
Posts: 322
Joined: 12.10.2005, 18:01
Location: Slovenia

Postby zippo » 08.11.2006, 18:49

SHOW TABLES FROM 99 LIKE 'clientes'

Should be

SHOW TABLES FROM `99` LIKE 'clientes'

Jaimefig
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 08.11.2006, 13:48

Postby Jaimefig » 08.11.2006, 19:15

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.

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 08.11.2006, 20:39

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
Silver Boarder
Silver Boarder
Posts: 322
Joined: 12.10.2005, 18:01
Location: Slovenia

Postby zippo » 08.11.2006, 23:08

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

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 09.11.2006, 01:22

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[I] 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
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 08.11.2006, 13:48

Postby Jaimefig » 09.11.2006, 14:30

Hi,

Now is ok.
I replaced the function.


Thank you very much.


Return to “Bug Reports”

Who is online

Users browsing this forum: No registered users and 1 guest