Placing User Interface Data into an SQL query

Tom_P

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

 

pmroz

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