You have an error in your SQL syntax - MySQL 3.23.51, Delphi 6, DbExpress

Karol Martinka

unread,

Mar 12, 2003, 10:35:49 AM3/12/03

to

Hi!

I get every time I try to ApplyUpdates this error :

Project xxx raised exception class EDatabaseError with message 'You have an
error in your SQL syntax near 'Index, SK)
values
('656','abcd')
' at line 2'.

HAWGH! I found the error ! :) I cannot use "reserved words" as Field name
... What a stupid error and what time did I spend with it ...

But I have another question - why not use TSQLClientDataSet ?? Where are the
problems with it ??

Thank you
karol

Martijn Tonies

unread,

Mar 12, 2003, 11:32:40 AM3/12/03

to

Sounds like an error coming from MySQL directly, right?

--

With regards,

Martijn Tonies
Database Workbench - the developer tool for InterBase & Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

Ross Davis

unread,

Mar 12, 2003, 11:52:55 AM3/12/03

to

That is an error being returned from the database server, but the field name
should be quoted by the sql generation routine.

I just confirmed it myself and I agree with you. Would you me to submit a
bug report to qc for you?

"Martijn Tonies" <m.tonies@upscene_remove.com> wrote in message
news:3e6f611d$1...@newsgroups.borland.com...

Ross Davis

unread,

Mar 12, 2003, 11:57:18 AM3/12/03

to

for the record here is a snippit from the log file
20 Query insert into reservednametest
(id, index, description)
values
('-1', '2322', '454')

As you can see the fields are not quoted and should be.

Ross

"Ross Davis" <ro...@dataanywhere.net> wrote in message
news:3e6f65f0$1...@newsgroups.borland.com...

Karol Martinka

unread,

Mar 12, 2003, 12:20:29 PM3/12/03

to

O.k. thnx.

I'm new to SQL (today the first time :) I was programming another kind of
applications ...
But I have a maybe off-topic question.

I have a MySQL table with 2 indexes - Primary and IDXFirst and 4 fields. The
primary is for the first and IDXFirst for second field.
The table looks like :
Field1 * Field2 * Field3 * Field 4
1 * abc * ds * fdf
2 * zzs * dd * dd
3 * ddf * fdf * fd

If I put in the SQL Query :

SELECT * FROM slovnik USE INDEX (IDXFirst)

I have still the Primary index (it is ordered by Field1)

I use :
SQLConnection->SQLQuery->DSP->CDS->DataSource.

And is it right to put SELECT * FROM slovnik USE INDEX (Primary) ??

How the hell can I use indexes in SQL ???
Do you know a good resources to teach myself SQL ?

Thnx,
Karol

"Ross Davis" <ro...@dataanywhere.net> wrote in message
news:3e6f65f0$1...@newsgroups.borland.com...

Ross Davis

unread,

Mar 12, 2003, 12:59:31 PM3/12/03

to

There are a lot of books on Mysql. I have seen a lot but personally do not
own any of them. The documentation on the Mysql site is very good, but does
not really get into the basics of how thing should be done but is more of a
technical reference.

As far as you questions about the select.... There is no need to put a use
index on the select command. If you want to order your select results your
query should look like this:

Select * from slovnic {where yourcriteria} order by field1,field2....

That will cause the database server to return sorted results. That will not
control the sorting of your data on you application!

What you are going to want to do is create indexes on the CDS and use them.
If you use the CDS to do the sorting of your data, it is more efficient for
the database server to do the select without having an order by because the
database server will not have to sort the results.

Indexes on a SQL server are there to optimize the database server and are
not intented for use as a sorting mechanism like a DBF file or something
would.

Ross Davis

unread,

Mar 12, 2003, 1:26:52 PM3/12/03

to

Submitted to QC as 3782.

"Karol Martinka" <ka...@freeland.sk> wrote in message
news:3e6f...@newsgroups.borland.com...

Karol Martinka

unread,

Mar 12, 2003, 1:17:14 PM3/12/03

to

o.k I see.

I am right ? :
If I do not create a "server" Index for Field2 and use "order by Field2" it
will be much more slower than if I create a "server" Index for Field2 and
use the same "order by Field2" ???

I have readed that if I use "order by ......" then the MySQL server will
create temporary index for sorting ?! But there was'nt anything about
situation where there are defined Indexes.

Should I append the "USE INDEX (IDXField2)" or it is done automatically ??
like : "SELECT * FROM slovnik order by Field2 USE INDEX(IDXField2)" or
only "SELECT * FROM slovnik order by Field2" ????

I promise I go tomorrow to bookstore :)

And the last one - is there a way to override the DataSetProvider and
ClientDataSet and use only the Query to get data from table ?

Thank you!
Karol

"Ross Davis" <ro...@dataanywhere.net> wrote in message

news:3e6f...@newsgroups.borland.com...

Ross Davis

unread,

Mar 12, 2003, 1:43:46 PM3/12/03

to

"Karol Martinka" <ka...@freeland.sk> wrote in message
news:3e6f...@newsgroups.borland.com...

> o.k I see.
>
> I am right ? :
> If I do not create a "server" Index for Field2 and use "order by Field2"
it
> will be much more slower than if I create a "server" Index for Field2 and
> use the same "order by Field2" ???

If you NEED to order the result set that is returned from the database
server then having an index on that field on the server will help.

>
> I have readed that if I use "order by ......" then the MySQL server will
> create temporary index for sorting ?! But there was'nt anything about
> situation where there are defined Indexes.
>
> Should I append the "USE INDEX (IDXField2)" or it is done automatically
??
> like : "SELECT * FROM slovnik order by Field2 USE INDEX(IDXField2)" or
> only "SELECT * FROM slovnik order by Field2" ????

Mysql has a VERY efficient optimizer that will take this for you.