uf_ds_from_sql() to create DataStore by dynamic SELECT
Posted: 19 Feb 2013, 21:41
by Ursego
This function returns a DataStore created dynamically by the supplied SQL SELECT.
It has two overloads. One has the argument a_tr, so you can pass the Transaction object. Another overload doesn't have that argument - it always uses SQLCA (probably, it will be most popular).
More information and examples of use are provided in the header comment of the function.
If an error occurs, the function displays an error message and returns a DataStore which has not been initialized, so the calling script must check it with IsValid(). That is not the best way, but, probably, most developers will choose it since they don't use exceptions. But if you want to use the exceptions mechanism (described here), which is the correct and preferable way, then use the version, provided in a comment later in this thread (direct link).
Here is the source code (the error message version, not the exception version) - please add it to your utilities NVO:
/********************************************************************************************************************** Dscr: Returns DataStore created dynamically by the supplied SQL SELECT.
This func allows to AVOID: 1. Creation of a large number of DataObjects, used only once. 2. Complicated formats 3 and 4 of dynamic embedded SQL. It's much easier to build the SQL as a string, pass it to uf_ds_from_sql() and then manipulate the retrieved data in a DataStore.
The DS cannot have retrieval arguments. Instead, add them to the WHERE clause of SELECT statement dynamically prior to passing it to uf_ds_from_sql():
If an argument is an array, then you can create an IN clause using the function uf_in_clause_from_array() (http://code.intfast.ca/viewtopic.php?f=4&t=95). It has two overloads - for arrays of types string and long:
ls_statuses_frag = gn_util.uf_in_clause_from_array("status", {"A", "N", "R"}) // returns "status IN ('A', 'N', 'R')" ls_depts_frag = gn_util.uf_in_clause_from_array("dept", {7, 11, 16}) // returns "dept IN (7, 11, 16)" ls_sql = "SELECT " + as_list_of_fields_to_print + " FROM emp WHERE " + ls_statuses_frag + " AND " + ls_depts_frag lds_emp = gn_util.uf_ds_from_sql(ls_sql, true /* ab_retrieve */) *********************************************************************************************************************** Arg: as_sql: SELECT statement to use as the data source of the created DS (without ";"). ab_retrieve: true = create DS, and retrieve data immediately; false = only create DS, but don't retrieve. a_tr - Transaction object for created DS. If it is SQLCA, then call the overloaded version without this arg. *********************************************************************************************************************** Ret: DataStore. Calling script must check it with IsValid()! *********************************************************************************************************************** Developer: Michael Zuskin - http://linkedin.com/in/zuskin | http://code.intfast.ca/ **********************************************************************************************************************/ long ll_rc string ls_err = '' string ls_syntax DataStore lds
as_sql = Trim(as_sql)
choose case true case as_sql = '' ls_err = "as_sql is empty string." case IsNull(as_sql) ls_err = "as_sql is null." case not IsValid(a_tr) ls_err = "a_tr is not valid." end choose
if ls_err <> '' then ls_syntax = a_tr.SyntaxFromSQL(as_sql, "style(type=grid)", ref ls_err) if Len(ls_err) > 0 then ls_err = "SyntaxFromSQL() failed:~r~n~r~n" + ls_err + "." end if
if ls_err <> '' then lds = create DataStore lds.Create(ls_syntax, ref ls_err) if Len(ls_err) > 0 then ls_err = "Create() failed:~r~n~r~n" + ls_err + "." end if
if ls_err <> '' then ll_rc = lds.SetTransObject(a_tr) if ll_rc = -1 then ls_err = "SetTransObject() failed." end if
if ls_err <> '' and ab_retrieve then ll_rc = lds.Retrieve() if ll_rc = -1 then ls_err = "Retrieve() failed." end if
if ls_err <> '' then if IsValid(a_tr) then ls_err += "~r~n~r~nServerName = '" + a_tr.ServerName + "'; LogID = '" + a_tr.LogID + "'." if Len(as_sql) > 0 then ls_err += "~r~n~r~nSQL SELECT:~r~n~r~n" + as_sql MessageBox("Error in uf_ds_from_sql()", ls_err) end if
return lds
An overload which uses SQLCA; it has only 2 parameters - as_sql and ab_retrieve, but not a_tr:
Generally speaking, using SQLs in the client side is not a good practice. The client should call a stored procedure which encapsulates (and keeps in the server side) any existing (or potential) complexity. But if you have no choice (for example, supporting an application with all the SQLs kept in PB), then uf_ds_from_sql() can make your life easier.
Re: uf_ds_from_sql() to create DataStore by dynamic SELECT
Posted: 19 Nov 2013, 01:36
by zoeyku
If we would have that function 15 years ago!
Re: uf_ds_from_sql() to create DataStore by dynamic SELECT
Posted: 05 Jul 2019, 07:57
by Ursego
If you want to use the exceptions mechanism (described here) rather than simply display an error message and return a not-instantiated DataStore, then use this version of uf_ds_from_sql() (don't forget to fill the "Throws:" field in the header with n_ex):
/********************************************************************************************************************** Dscr: Returns DataStore created dynamically by the supplied SQL SELECT.
This func allows to AVOID: 1. Creation of a large number of DataObjects, used only once. 2. Complicated formats 3 and 4 of dynamic embedded SQL. It's much easier to build the SQL as a string, pass it to uf_ds_from_sql() and then manipulate the retrieved data in a DataStore.
The DS cannot have retrieval arguments. Instead, add them to the WHERE clause of SELECT statement dynamically prior to passing it to uf_ds_from_sql():
If an argument is an array, then you can create an IN clause using the function uf_in_clause_from_array() (http://code.intfast.ca/viewtopic.php?f=4&t=95). It has two overloads - for arrays of types string and long:
ls_statuses_frag = gn_util.uf_in_clause_from_array("status", {"A", "N", "R"}) // returns "status IN ('A', 'N', 'R')" ls_depts_frag = gn_util.uf_in_clause_from_array("dept", {7, 11, 16}) // returns "dept IN (7, 11, 16)" ls_sql = "SELECT " + as_list_of_fields_to_print + " FROM emp WHERE " + ls_statuses_frag + " AND " + ls_depts_frag lds_emp = gn_util.uf_ds_from_sql(ls_sql, true /* ab_retrieve */) *********************************************************************************************************************** Arg: as_sql: SELECT statement to use as the data source of the created DS (without ";"). ab_retrieve: true = create DS, and retrieve data immediately; false = only create DS, but don't retrieve. a_tr - Transaction object for created DS. If it is SQLCA, then call the overloaded version without this arg. *********************************************************************************************************************** Ret: DataStore *********************************************************************************************************************** Developer: Michael Zuskin - http://linkedin.com/in/zuskin | http://code.intfast.ca/ **********************************************************************************************************************/ long ll_rc string ls_err string ls_syntax DataStore lds
as_sql = Trim(as_sql)
try if as_sql = '' then f_throw(PopulateError(1, "as_sql is empty string.")) // f_throw(): http://code.intfast.ca/viewtopic.php?f=2&t=1 if IsNull(as_sql) then f_throw(PopulateError(2, "as_sql is null.")) if not IsValid(a_tr) then f_throw(PopulateError(3, "a_tr is not valid."))
ls_syntax = a_tr.SyntaxFromSQL(as_sql, "style(type=grid)", ref ls_err) if Len(ls_err) > 0 then f_throw(PopulateError(4, "SyntaxFromSQL() failed:~r~n~r~n" + ls_err + "."))
lds = create DataStore lds.Create(ls_syntax, ref ls_err) if Len(ls_err) > 0 then f_throw(PopulateError(5, "Create() failed:~r~n~r~n" + ls_err + "."))
ll_rc = lds.SetTransObject(a_tr) if ll_rc = -1 then f_throw(PopulateError(6, "SetTransObject() failed."))
if ab_retrieve then ll_rc = lds.Retrieve() if ll_rc = -1 then f_throw(PopulateError(7, "Retrieve() failed.")) end if catch (n_ex ln_ex) // Add some more info to the Exception's message and re-throw: if IsValid(a_tr) then ls_err += "~r~n~r~nServerName = '" + a_tr.ServerName + "'; LogID = '" + a_tr.LogID + "'." if Len(as_sql) > 0 then ls_err += "~r~n~r~nSQL SELECT:~r~n~r~n" + as_sql ln_ex.SetMessage(ls_err) throw ln_ex end try