uf_ds_from_sql() to create DataStore by dynamic SELECT


Link to this posting

Postby Ursego » 19 Feb 2013, 21:41

This function returns a DataStore created dynamically by the supplied SQL SELECT:

Code: Select all
string      ls_sql_select
DataStore   lds_emp
      
ls_sql_select = "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_select, true /* ab_also_retrieve */) // this overload uses SQLCA
lds_emp = gn_util.uf_ds_from_sql(ls_sql_select, true /* ab_also_retrieve */, a_tr) // if the transaction object is not SQLCA

The second argument, ab_also_retrieve, instructs the function to retrieve data just after the DS has been created (true) or not to retrieve (false). False can be passed, for example, when the calling script will populate the DS with InsertRow(). The third argument is the Transaction object to use. If SQLCA is used widely in the application then you can add an overload keeping only two the first arguments and always pass SQLCA.

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 the DataStore).

The source code of the function is here (you can add it to your utilities NVO). It utilizes the exceptions mechanism described here (but, of course, you can change the function to use the error messaging mechanism of your application).

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).
               
            Example of use: http:/code.intfast.ca/viewtopic.php?f=4&t=12
***********************************************************************************************************************
Arg:         as_sql_select: SQL SELECT to be used as the data source of the created DS (without ";").     
            ab_also_retrieve:
                  true = create DS and retrieve data immediately;
                  false = only create DS, don't retrieve (for example, if the DS will be populated later programmatically).
            a_tr - Transaction object for created DS. If it is SQLCA then call 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_msg
string      ls_syntax
DataStore   lds

as_sql_select = Trim(as_sql_select)

try
   if uf_empty(as_sql_select) then f_throw(PopulateError(1, "as_sql_select is empty."))
   if not IsValid(a_tr) then f_throw(PopulateError(2, "a_tr is not valid."))

   ls_syntax = a_tr.SyntaxFromSQL(as_sql_select, "style(type=grid)", ref ls_err_msg)
   if Len(ls_err_msg) > 0 then f_throw(PopulateError(3, "SyntaxFromSQL() failed:~r~n~r~n" + ls_err_msg + "."))
   
   lds = create DataStore
   lds.Create(ls_syntax, ref ls_err_msg)
   if Len(ls_err_msg) > 0 then f_throw(PopulateError(4, "Create() failed:~r~n~r~n" + ls_err_msg + ".")) // f_throw(): http://code.intfast.ca/viewtopic.php?f=2&t=1
   
   ll_rc = lds.SetTransObject(a_tr)
   if ll_rc = -1 then f_throw(PopulateError(5, "SetTransObject() failed."))
   
   if ab_also_retrieve then
      ll_rc = lds.Retrieve()
      if ll_rc = -1 then f_throw(PopulateError(6, "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_msg += "~r~n~r~nServerName = '" + a_tr.ServerName + "'~r~nLogID = '" + a_tr.LogID + "'"
   ls_err_msg += "~r~n~r~n###############################################~r~n~r~nSQL SELECT:~r~n~r~n" + as_sql_select
   ln_ex.SetMessage(ls_err_msg)
   throw ln_ex
end try

return lds

An overload which uses SQLCA (probably, this overload will be used in 99% of the cases):
Code: Select all
return this.uf_ds_from_sql(as_sql_select, ab_also_retrieve, SQLCA)


You can utilize the function uf_in_clause_from_array() to create the IN clause for the SELECT, sent to uf_ds_from_sql():

Code: Select all
string ls_last_names[] = {"Johnson", "Singh", "Ivanov"}
string ls_countries[] = {"Canada", "USA", "Australia"}
ls_last_name_frag = gn_util.uf_in_clause_from_array("last_name", ls_last_names[]) // returns "last_name IN ('Johnson', 'Singh', 'Ivanov')"
ls_country_frag = gn_util.uf_in_clause_from_array("country", ls_countries[]) // returns "country IN ('Canada', 'USA', 'Australia')"
ls_sql = "SELECT emp_id, last_name, first_name, birth_date FROM emp WHERE " + ls_last_name_frag + " AND " + ls_country_frag
lds_emp = gn_util.uf_ds_from_sql(ls_sql, true /* ab_also_retrieve */)

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.
User avatar
Ursego
Site Admin
 
Posts: 118
Joined: 19 Feb 2013, 20:33

Link to this posting

Postby zoeyku » 19 Nov 2013, 01:36

If we would have that function 15 years ago!
zoeyku
 
Posts: 2
Joined: 19 Nov 2013, 01:32
Location: usa




IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:




free counters

eXTReMe Tracker