Eval insert SQL

[#1]

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.

[#2 · Accepted solution]

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') );

[#3]

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!

[#4]

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

[#5]

@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

[#6 · Accepted solution]

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, "¤"));

[#7]

thank you very much @jthi this is perfect!

[#8 · Accepted solution]

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.

[#9]

@ron_horne, try REGEXP_LIKE

[#10]

Thank you @ErraticAttack ,

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

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