Board index » delphi » Problem with queries that contain '^'

Problem with queries that contain '^'


2005-07-28 10:07:12 PM
delphi185
Not sure if this is just in Firebird or all other servers but, we are
having some problems with queries that contain the '^' character.
Using a hash to create a unique identifier. When the hash generates a
string that contains the '^' character, it can be inserted into a field
in the database. However, when using a query to check (as an example)
for '... where has = 'W9*7hh_$#^imk(*'... or like 'W9*7hh_$#^imk(*' the
query breaks.
We have tested by removing the characters after the '^' and the query
does not work until we remove the '^'. Is this character a reserved
character in Firebird 1.5 SQL implementation? Or is it bad in any SQL?
How come we can insert with a query but not get at the info with a query?
Thanks!
- Eduardo
Accept the challenges so that you can feel
the exhilaration of victory.
-- General George S. Patton
Eminent Domain Software
"Custom Software Development For Your Domain"
Makers of EDSSpell, EDSPrint, EDSZipCodes and
XSpell, the IDE Expert.
 
 

Re:Problem with queries that contain '^'

"Eduardo A. Salgado" <XXXX@XXXXX.COM>???????/???????? ? ????????
?????????: news:42e8e690$XXXX@XXXXX.COM...
Quote
Not sure if this is just in Firebird or all other servers but, we are
having some problems with queries that contain the '^' character.

Using a hash to create a unique identifier. When the hash generates a
string that contains the '^' character, it can be inserted into a field in
the database. However, when using a query to check (as an example) for
'... where has = 'W9*7hh_$#^imk(*'... or like 'W9*7hh_$#^imk(*' the query
breaks.

We have tested by removing the characters after the '^' and the query does
not work until we remove the '^'. Is this character a reserved character
in Firebird 1.5 SQL implementation? Or is it bad in any SQL?

How come we can insert with a query but not get at the info with a query?

Thanks!

- Eduardo
Accept the challenges so that you can feel
the exhilaration of victory.
-- General George S. Patton

Eminent Domain Software
"Custom Software Development For Your Domain"

Makers of EDSSpell, EDSPrint, EDSZipCodes and
XSpell, the IDE Expert.
AFAIK '^' is used to separate SQL batches within long IB/FIB scripts. There
is some syntax to reassign some aribitrary child instead '^', but I don't
think that it will be right solution. General solution for this kind of
problems: do not hardcode values in SQL, but use parameters instead.
 

Re:Problem with queries that contain '^'

Quote
>Not sure if this is just in Firebird or all other servers but, we are
>having some problems with queries that contain the '^' character.
>
>Using a hash to create a unique identifier. When the hash generates a
>string that contains the '^' character, it can be inserted into a field
in
>the database. However, when using a query to check (as an example) for
>'... where has = 'W9*7hh_$#^imk(*'... or like 'W9*7hh_$#^imk(*' the
query
>breaks.
>
>We have tested by removing the characters after the '^' and the query
does
>not work until we remove the '^'. Is this character a reserved
character
>in Firebird 1.5 SQL implementation? Or is it bad in any SQL?
>
>How come we can insert with a query but not get at the info with a
query?
>
AFAIK '^' is used to separate SQL batches within long IB/FIB scripts.
There
This isn't true.
InterBase or Firebird don't support batches.
Scripts are parsed at the client and send to the server on a per-statement
basis. The statement terminator is ";" by default.
Whenever a stored procedure or trigger needs to be created, the
source code contains ";" characters as command separators.
To aid the parsers in parsing the complete CREATE PROCEDURE
or TRIGGER statement, the terminator is changed via a
SET TERM <new terminator><old terminator>statement.
After this, a CREATE PROCEDURE has to end with <new terminator>.
The "^" character is often used as a <new terminator>. However, the
server doesn't know anything about this.
Quote
is some syntax to reassign some aribitrary child instead '^', but I don't
think that it will be right solution. General solution for this kind of
problems: do not hardcode values in SQL, but use parameters instead.
That given, I have no idea why the server should fail on "^". Could it be
something else? What is the error message?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:Problem with queries that contain '^'

Quote
>AFAIK '^' is used to separate SQL batches within long IB/FIB scripts.
There

This isn't true.

InterBase or Firebird don't support batches.
Any SQL server support batches, but some of them (and IB/FIB are among them)
allow only single statment per batch. So fo FB/IB statment and batch means
the same.
Quote
Scripts are parsed at the client and send to the server on a per-statement
This is quite good explanation of batch term.
 

Re:Problem with queries that contain '^'

What happens if you use a parameter and assign the value containing the
^ to the parameter?
What is the error message? Saying "the query breaks" does not tell us
much.
--
Bill Todd (TeamB)
 

Re:Problem with queries that contain '^'

Quote
>>AFAIK '^' is used to separate SQL batches within long IB/FIB scripts.
>There
>
>This isn't true.
>
>InterBase or Firebird don't support batches.
Any SQL server support batches, but some of them (and IB/FIB are among
them)
allow only single statment per batch. So fo FB/IB statment and batch means
the same.
Yes well, that is as silly as saying that MySQL supports transactions on
a single statement per transaction basis :-) :-) (non InnoDB, of course).
Quote
>Scripts are parsed at the client and send to the server on a
per-statement
This is quite good explanation of batch term.
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:Problem with queries that contain '^'

"Martijn Tonies" <XXXX@XXXXX.COM>сообщи?сообщила ?
новостях следующе? news:XXXX@XXXXX.COM...
Quote
Yes well, that is as silly as saying that MySQL supports transactions on
a single statement per transaction basis :-) :-) (non InnoDB, of course).
Yes well, if you that smart, then please explain differences between: sql
statment, sql batch, sql script.
 

Re:Problem with queries that contain '^'

Quote
>Yes well, that is as silly as saying that MySQL supports transactions on
>a single statement per transaction basis :-) :-) (non InnoDB, of
course).
Yes well, if you that smart, then please explain differences between: sql
statment, sql batch, sql script.
ok, according to me :) ...
SQL statement: a single command (eg: CREATE TABLE, or INSERT INTO)
SQL batch: multiple commands, send to the server in a single go.
SQL script: a collection of statements, executed on a per-statement basis
or as a whole (as a batch).
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:Problem with queries that contain '^'

Quote
SQL script: a collection of statements, executed on a per-statement basis
or as a whole (as a batch).
Yeah well then how scripts for MS SQL server (or any other servers that
supports batches with multiple statments) match to this definition according
to you? :-))
 

Re:Problem with queries that contain '^'

Quote
>SQL script: a collection of statements, executed on a per-statement
basis
>or as a whole (as a batch).
Yeah well then how scripts for MS SQL server (or any other servers that
supports batches with multiple statments) match to this definition
according
to you? :-))
Actually, MS SQL native tools have a special "GO" terminator for the
script parser to signal the "end of a batch".
Also, a CREATE TRIGGER/PROCEDURE/FUNCTION is always
the last statement in the "batch".
So a script, in MS SQL Server, can be multiple batches and statement.
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:Problem with queries that contain '^'

Martijn,
Based on your answer, I checked further.
We had problems with the results of a query and "another tool" (not
yours) reported just: "Problem with query 'W9*7hh_$#^imk(*' ". We
checked with better tools and saw that you are correct. The query
*does* work. The error must be caused by something elsewhere and not
with the '^'.
Thanks for setting us straight.
- Eduardo
Accept the challenges so that you can feel
the exhilaration of victory.
-- General George S. Patton
Eminent Domain Software
"Custom Software Development For Your Domain"
Makers of EDSSpell, EDSPrint, EDSZipCodes and
XSpell, the IDE Expert.
Martijn Tonies writes:
Quote

That given, I have no idea why the server should fail on "^". Could it be
something else? What is the error message?


 

Re:Problem with queries that contain '^'

Quote
Also, a CREATE TRIGGER/PROCEDURE/FUNCTION is always
the last statement in the "batch".
No comments!
 

Re:Problem with queries that contain '^'

Quote
Also, a CREATE TRIGGER/PROCEDURE/FUNCTION is always
the last statement in the "batch".
LOL :-))
 

Re:Problem with queries that contain '^'

Quote
>Also, a CREATE TRIGGER/PROCEDURE/FUNCTION is always
>the last statement in the "batch".
LOL :-))
Might be me, but I don't "get it".
This comes from the MS SQL Help and, for the record, has nothing to do
with other database engines.
This also shows that a MS SQL Server script can contain multiple batches
(being it single statements or multiple statements) but a batch always ends
at "create trigger/.." despite the client sending more statements after it.
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:Problem with queries that contain '^'

Hello Eduardo,
Quote
Based on your answer, I checked further.

We had problems with the results of a query and "another tool" (not
yours)
Good ;)
Quote
reported just: "Problem with query 'W9*7hh_$#^imk(*' ". We
checked with better tools and saw that you are correct. The query
*does* work. The error must be caused by something elsewhere and not
with the '^'.

Thanks for setting us straight.
My please.
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com