topic Re: How to use evalinsert on SQL Query? in Discussions

How to use evalinsert on SQL Query?

UserID16644 — Wed, 13 Mar 2024 06:05:15 GMT

Hi all,

I am trying to query using a list and a variable. However, I cannot figure out how to query the dateNow variable. I keep on getting this error:

Error occurred while trying to execute a query: [SQLState 42601] ERROR: syntax error at or near "00"
LINE 4: and test_date_time between 03/12/2024 00:00:00 AM and 03/12/...

Here is my sample script:

namecol= {"James", "John", "Anna", "Rose")
dateNow = "03/12/2024"
eval(parse(evalinsert("\[ 

	dt_qst = Try ( Open Database( "DSN=dbs;UID=uname;PWD=pw;APP=JMP;DATABASE=db;", 
"select name, age, address, test_date_time
from  db
where name in (^namecol^)
and test_date_time between ^dateNow^ 00:00:00 AM and ^dateNow^ 11:59:59 PM'", 
"rawdata") )
	
]\")));;

Re: How to use evalinsert on SQL Query?

jthi — Wed, 13 Mar 2024 06:26:01 GMT

There are many small typos in your script which prevent it from working. Outside of that you need to use Concat Items to go from list to a string. Also to make it a bit easier to debug, build your query string outside of Open Database and then use that new string inside it.

Names Default To Here(1);

namecol= {"James", "John", "Anna", "Rose"};
dateNow = "03/12/2024";

sql_template = "\[
select
	name, 
	age, 
	address, 
	test_date_time
from db
where
	name in ('^namestr^')
	and test_date_time between ^dateNow^ 00:00:00 AM and ^dateNow^ 11:59:59 PM'
]\";


namestr = Concat Items(namecol, "', '");

sql_str = Eval Insert(sql_template);

I also think this has been basically answered already twice

and

Re: How to use evalinsert on SQL Query?

UserID16644 — Wed, 13 Mar 2024 06:45:01 GMT

Yes, but I don't understand why I kept on getting this error

Error occurred while trying to execute a query: [SQLState 42601] ERROR: syntax error at or near "00"
LINE 10: and test_date_time between 03/12/2024 00:00:00 AM and 03/12...

Re: How to use evalinsert on SQL Query?

jthi — Wed, 13 Mar 2024 06:54:29 GMT

Maybe your dates aren't correctly formatted? Most likely you are missing ' around the dates