Encode double colon in postgres sql statement

dev support
1

Hi all for the first time

My question: i’ll try to execute a sql query on psg with a double colon in the statement:

qGetJourneyDisplay = queryExecute("
SELECT journey_title, journey_id FROM baba.journey JOIN unnest('{5,3,1,2,4}'::int[ ]) WITH ORDINALITY AS x(journey_id, order_nr) USING (journey_id) ORDER BY x.order_nr
",{},{returntype="query"});

Lucce has a problem with a double colon in the quety and eats one : away,

SELECT journey_title, journey_id FROM babawanga.journey JOIN unnest('{5,3,1,2,4}':int[]) WITH ORDINALITY AS x(journey_id, order_nr) USING (journey_id) ORDER BY x.order_nr

ERROR: syntax error at or near ":"

So the query fails. What is the correct way to encode the :: in ?

Thx

PS : Also this forum software has encoding issues… the tick box should be squared brackets

PS2: queryExecute expects a param after a colon. There must be a way to encode that colons, right?

PS3: I’m new to cfscript and this forum…

1 Like
2

hi @baba
Already has a ticket for this issue in Jira LDEV-1063

Workaround for your issue, If you didn’t use any params means you can use named argument in queryExecute()

qGetJourneyDisplay = queryExecute(
    sql="SELECT journey_title, journey_id FROM baba.journey JOIN unnest(’{5,3,1,2,4}’::int) WITH ORDINALITY AS x(journey_id, order_nr) USING (journey_id) ORDER BY x.order_nr.......",
    options={returntype=“query”}
);

Or use the query tag-script method

query name="qGetJourneyDisplay" returntype="query" {
    echo("SELECT journey_title, journey_id FROM baba.journey JOIN unnest(’{5,3,1,2,4}’::int) WITH ORDINALITY AS x(journey_id, order_nr) USING (journey_id) ORDER BY x.order_nr.........");
}
1 Like
3

I use a good old taged query. Escaping with \ would be a nice feature for the JDBC driver, i will post a issue.
Thx a lot

1 Like
4

I’m having this issue right now. Im trying to work with json in postgress and the :: issue is cropping up.

1 Like