topic Re: Placing User Interface Data into an SQL query in Discussions

Placing User Interface Data into an SQL query

Tom_P — Fri, 09 Jun 2023 19:56:25 GMT

I've found tons of creating user interface examples, but there aren't that many discussions explaining how to handle/manipulate user data. I'm struggling getting data from a text box (# of days) and using that in an expression On_OK. I also want to then use data as a string in an sql query for days. In the past i would set a global variable ::DATETIMESTUFF="10" and then i could put that into

WHERE A.SCANDATETIME>(SYSDATE-"|| ::DATETIMESTUFF || ")

ui = New Window( "Choose an option",
	<<Modal,
	V List Box(
		Text Box( "Query Last 'x' Number of days:" ),
		Spacer Box( Size( 20, 20 ) ),
		days = Text Edit Box( "" )		
	),
	Button Box( "Ok", 
	current_days = days << Get Text; 
	On_OK() 
	)
);

On_OK = Expr(

	::DATETIMESTUFF = current_days;
	
	Open Database("DSN=xxx;UID=xxxxx;PWD=xxxxx;",
	"Select	*
	from DATABASE A
	WHERE A.SCANDATETIME>(SYSDATE-"|| ::DATETIMESTUFF || ")
    ",
		"Table1"
	);
);

Re: Placing User Interface Data into an SQL query

pmroz — Fri, 10 Sep 2021 18:58:57 GMT

Try this. You need to put on_ok above the new window().

On_OK = Expr(
	Open Database("DSN=xxx;UID=xxxxx;PWD=xxxxx;", sql_statement, "Table1");
);

ui = New Window( "Choose an option",
	<<Modal,
	V List Box(
		Text Box( "Query Last 'x' Number of days:" ),
		Spacer Box( Size( 20, 20 ) ),
		days = number Edit Box( "" )		
	),
	Button Box( "Ok", 
		current_days = days << Get; 
		On_OK
	),
);

sql_statement = evalinsert(
"Select	*
   from DATABASE A
  WHERE A.SCANDATETIME > (SYSDATE - ^current_days^)");

Re: Placing User Interface Data into an SQL query

Tom_P — Mon, 13 Sep 2021 15:27:37 GMT

If i put the number 10 instead of ^current_days^ in your script, it works correctly.

But with the ^current_days^, it gives an Error ORA-00936 (missing expression)

So i still can't get the data from the text box and put it into the query

Re: Placing User Interface Data into an SQL query

pmroz — Mon, 13 Sep 2021 18:58:29 GMT

Try using a function instead of an expression. Also you can inspect the values of your variables by hovering over the variable itself, or using the show() command and looking at the log window.

On_OK = function({curr_days},
	sql_statement = evalinsert(
"Select	*
   from DATABASE A
  WHERE A.SCANDATETIME > (SYSDATE - ^curr_days^)");

	show(curr_days, sql_statement);
	Open Database("DSN=xxx;UID=xxxxx;PWD=xxxxx;", sql_statement, "Table1");
);

ui = New Window( "Choose an option",
	<<Modal,
	V List Box(
		Text Box( "Query Last 'x' Number of days:" ),
		Spacer Box( Size( 20, 20 ) ),
		days = number Edit Box( "" )		
	),
	Button Box( "Ok", 
		current_days = days << Get; 
		On_OK(current_days);
	),
);

Re: Placing User Interface Data into an SQL query

Tom_P — Mon, 13 Sep 2021 19:13:53 GMT

PMROZ...both solutions worked. I really appreciate it