uf_ds_from_sql() to create DataStore by dynamic SELECT

[Ursego]

Link to this posting

by Ursego » 19 Feb 2013, 21:41

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:

Code: Select all

/**********************************************************************************************************************
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():

            ls_sql = "SELECT " + as_list_of_fields_to_print + " FROM emp WHERE dept_id = " + String(al_dept_id)
            lds_emp = gn_util.uf_ds_from_sql(ls_sql, true /* ab_retrieve */)

            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:

Code: Select all

return this.uf_ds_from_sql(as_sql, ab_retrieve, SQLCA)

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.

[zoeyku]

Link to this posting

by zoeyku » 19 Nov 2013, 01:36

If we would have that function 15 years ago!

[Ursego]

Link to this posting

by Ursego » 05 Jul 2019, 07:57

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):

Code: Select all

/**********************************************************************************************************************
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():

            ls_sql = "SELECT " + as_list_of_fields_to_print + " FROM emp WHERE dept_id = " + String(al_dept_id)
            lds_emp = gn_util.uf_ds_from_sql(ls_sql, true /* ab_retrieve */)

            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

return lds