topic Eval insert SQL in Discussions

Eval insert SQL

ron_horne — Fri, 09 Jun 2023 16:02:49 GMT

Hi All,

i am trying to produce an SQL statment that will bring only rows with matching id names.

i have a userlist that i want to Eval insert into the SQL string. problem is that it parses it with double quotes instead of single quotes.

Names default to here (1);
// got the userlist from a data table using dt:users << get as matrix
userlist  = {"01E1B5C0651EDA80", "F62B70921920FCBE", "909D35028E2039CE", "B339888591CF1346"};

// define an SQL string to host the list of user ids
sqlstr_ =
"SELECT  \!"t1\!".uid 
FROM \!"Tables\!".\!"Table\!"  \!"t1\!"
WHERE  ( \!"t1\!".uid IN ( ^userlist^) )
;";

Eval Insert( sqlstr_logs );
// i get the following non working SQL string 
"SELECT  \!"t1\!".uid
FROM \!"Tables\!".\!"Table\!"  \!"t1\!"
WHERE  ( \!"t1\!".uid IN ( {\!"01E1B5C0651EDA80\!", \!"F62B70921920FCBE\!", \!"909D35028E2039CE\!", }) )
;"
// i would like to get the following working SQL statement
"SELECT \!"t1\!".uid 
FROM \!"Tables\!".\!"Table\!"  \!"t1\!" 
WHERE  ( \!"t1\!".uid IN  ( '01E1B5C0651EDA80' ,  'F62B70921920FCBE' ,  'F62B70921920FCBE' )  ) ;"

thank you very much in advance.

Re: Eval insert SQL

Craige_Hales — Wed, 23 Nov 2022 01:15:13 GMT

userlist= {"01E1B5C0651EDA80", "F62B70921920FCBE", "909D35028E2039CE"};
sqlstr_ =
"SELECT  \!"t1\!".uid 
FROM \!"Tables\!".\!"Table\!"  \!"t1\!"
WHERE  ( \!"t1\!".uid IN ( '^
concatitems(userlist,\!"','\!")
^') )
;";

write(Eval Insert( sqlstr_ ));

The leading and trailing apostrophes are handled differently from the interior apostrophes; see just before and after the ^, vs the separator string. If there is any possibility userlist could have 0 items, you should check for that before running the query because it will probably not make any sense.

You can make it a little more readable using the other escape mechanism (html does not know about it so the syntax coloring is wrong on the web):

userlist= {"01E1B5C0651EDA80", "F62B70921920FCBE", "909D35028E2039CE"};
sqlstr_ =
"\[
SELECT  "t1".uid 
FROM "Tables"."Table"  "t1"
WHERE  ( "t1".uid IN ( '^
concatitems(userlist,"','")
^') );
]\";

write(Eval Insert( sqlstr_ ));

Using write() to display the result helps too:

SELECT  "t1".uid 
FROM "Tables"."Table"  "t1"
WHERE  ( "t1".uid IN ( '01E1B5C0651EDA80','F62B70921920FCBE','909D35028E2039CE') );

Re: Eval insert SQL

ron_horne — Wed, 23 Nov 2022 11:25:57 GMT

thank you @Craige_Hales , this is very helpful.

the eval insert now works correctly. with respect to the other escape mechanism, it is much easier. pity this is not the way the software writes it.
I have another issue with SQL. i would like to get rows only where the value is made out of 16 alphanumeric characters.

the length statement by itself works fine. the regex doesn't

sqlstr_uid =
"\[
SELECT DISTINCT "t1".uid 
FROM "Tables"."Table"  "t1" 
WHERE ( (LENGTH	( "t1".uid) = 16) AND (  "t1".uid REGEXP '^[A-Za-z0-9]+$' ) )

]\";

am i missing a = or brackets?

thanks again!

Re: Eval insert SQL

Craige_Hales — Wed, 23 Nov 2022 19:45:23 GMT

Looking at some random sites on the web, it looks right. I'm not an SQL expert though.

Re: Eval insert SQL

ErraticAttack — Wed, 23 Nov 2022 20:49:54 GMT

@ron_horne, try REGEXP_LIKE

Re: Eval insert SQL

ron_horne — Thu, 24 Nov 2022 12:02:03 GMT

Thank you @ErraticAttack ,

it didn't work. i did manage to use this expression:

regexp_substr("t1".uid, '^[A-Za-z0-9]+$')="t1".uid )

Re: Eval insert SQL

ron_horne — Sun, 25 Dec 2022 20:32:49 GMT

@Craige_Hales
the following regex expression works when submitting the sql statement as a string:
regexp_substr("t1".uid, '^[A-Za-z0-9]+$')="t1".uid )

yet, when submitting this expression using eval insert (sql_str) i get an error.
the error has to do with the [ so i assume that evel insert is having a hard time with the ^ just before it.
is there a way of escaping the ^ ?
the following shows the exact error.

sqlstr_uid =
"\[
SELECT DISTINCT "t1".uid 
FROM "Tables"."Table"  "t1" 
WHERE    "t1".uid REGEXP '^[A-Za-z0-9]+$' 
]\";

write(Eval Insert( sqlstr_uid ));

many thanks!

ron

Re: Eval insert SQL

jthi — Sun, 25 Dec 2022 20:55:04 GMT

You can define which character to use with Eval Insert and I would suggest using some other character than default ^ if you are evaluating sql strings (especially ones with regex), I usually use "¤"

Names Default To Here(1);

sqlstr_uid =
"\[
SELECT DISTINCT "t1".uid 
FROM "Tables"."Table"  "t1" 
WHERE    "t1".uid REGEXP '^[A-Za-z0-9]+$' 
]\";

Write(Eval Insert(sqlstr_uid, "¤"));

Re: Eval insert SQL

ron_horne — Sun, 25 Dec 2022 21:38:58 GMT

thank you very much @jthi this is perfect!

Re: Eval insert SQL

Craige_Hales — Sun, 25 Dec 2022 21:45:26 GMT

Similar example

sqlstr_uid =
"\[
SELECT DISTINCT "t1".uid 
FROM "Tables"."Table"  "t1" 
WHERE    "t1".uid REGEXP '^[A-Za-z0-9]+$' 
]\";

write(Eval Insert( "the test ☺sqlstr_uid☺ works", "☺" ));

the test
SELECT DISTINCT "t1".uid
FROM "Tables"."Table" "t1"
WHERE "t1".uid REGEXP '^[A-Za-z0-9]+$'
works

There does not appear to be a \! escaping mechanism in Eval Insert; you need to pick an unused character.