Page 1 of 1
PostgreSQL cast using ::
Posted: 14.01.2010, 18:22
by ezemasa
Just add the marked line, otherwise it'll be treated as a parameter and throws syntax error
Code: Select all
constructor TZPostgreSQLSymbolState.Create;
begin
inherited Create;
Add('<=');
Add('>=');
Add('<>');
Add('<<');
Add('>>');
+ Add('::');
end;
sample query:
SELECT ID, 'HELLO'::varchar FROM TABLE
thaks a lot
excelent work
Posted: 15.01.2010, 08:30
by Wild_Pointer
Hello, ezemasa
:: is treated as : in query. So instead of SELECT ID, 'HELLO'::varchar FROM TABLE you should write SELECT ID, 'HELLO'::::varchar FROM TABLE
I don't think this should be fixed (or at least that way) because then there would be no way to put single : into sql
Posted: 15.01.2010, 15:13
by ezemasa
Adding that line does not avoid the use of single ':', I tested queries with params and they work fine. The same way the previous line ( Add('>>') ) doesn't avoid the use of a single '>'.
On the other hand, the current solution requires to escape characters when writing queries.
Not a big problem only if the queries are hard coded, but if they come form different sources and even the user (when he can generate his own reports) this is a big problem and gets bigger if the customer has a lot of queries written already.
I didn't see the need to wrap de ZQuery just to escape the '::'; if the ZTokenizer powerfull enough to perform this task.
Posted: 15.01.2010, 16:08
by Wild_Pointer
Ok,
there is is just one hypothetical question - how would one pass single : to postgresql server. (I cant think of reason why you should do it, but...) If I'd want do that now I'd use :: in SQL.text, but if :: is interpreted as :: then what is interpreted as : ? Or am I missing the point here?
Posted: 15.01.2010, 19:00
by ezemasa
I don't understand your question but maybe this example will help:
select 'HELLO:WORLD'::varchar, * from TEST_TABLE where ID = :ID and NAME <> 'SOME:THING'
This query works fine, the tokenizer doesn't get confused between ':' and '::', so you write de query the same just like you do in pg_admin. Or if you have de query text in the source code you only need to escape de quotes as usual, because of delphi strings, without changing anything because of ZeosLib
Posted: 18.01.2010, 08:53
by Wild_Pointer
Hello, ezemasa
What I wanted to ask is: for example in postgresql 8.9 operator : appears. In pgAdmin you will write SELECT column:something FROM table. Now in TZQuery we would write SELECT column::something FROM table, as :: is converted to : . How would this query look like after applying your patch?
Also how about backwards compatibility? Would people who used :::: have to rewrite queries?
Posted: 18.01.2010, 20:24
by ezemasa
Ok, now I undersand, I didn't know about the PostgreSQL arrays. I fixed it so it works, but the legacy is a big issue (that's why I made this change in the firt place, to deal with my own legacy queries).
I don't think this should be included if breaks any backwards compatibility.