uf_ds_from_sql() to create DataStore by dynamic SELECT

Share this topic:



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 " + ls_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, a_tr) // if tr object is not SQLCA
lds_emp = gn_util.uf_ds_from_sql(ls_sql_select, true) // if tr object is 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.

The function has two main purposes:

1. To prevent a large number of one-off DataObjects in your PBLs.
2. To avoid client-side cursors (including ones in the formats 3 and 4 of dynamic SQL - it's much easier to build the SQL as a string, pass it to uf_ds_from_sql() and then manipulate data in the DS).

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
/**********************************************************************************************************************
Acc:   public
-----------------------------------------------------------------------------------------------------------------------
Dscr: Returns DataStore created dynamically by the supplied SQL SELECT.
      Purposes:
         1. Using DS with dynamically created SQL instead of formats 3 and 4 of dynamic SQL.
         2. To avoid creation of extra DataStores and prevent chokage of PBLs with one-off objects.
      Example of use is here: http://forum.powerbuilder.us/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;
            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
-----------------------------------------------------------------------------------------------------------------------
Thr:   n_ex
-----------------------------------------------------------------------------------------------------------------------
Log:   02aug2011 Michael Zuskin   Initial version
**********************************************************************************************************************/
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 + "."))
   
   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

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
ls_in_clause = gn_util.uf_in_clause_from_array("emp_id", al_emp_id_arr[])
ls_sql = "SELECT emp_id, last_name, first_name, birth_date FROM emp WHERE " + ls_in_clause
lds_emp = gn_util.uf_ds_from_sql(ls_sql, true /* ab_also_retrieve */)

In common, dealing with SQLs in the client side is not a good practice, but if you are working in such an application then uf_ds_from_sql() can make your life easier.
User avatar
Ursego
Site Admin
 
Posts: 112
Joined: 19 Feb 2013, 20:33

Link to this posting

Postby aldobpb » 12 Oct 2013, 17:27

Hi, I used this code to replace a cursor that I used. It works very well. Now I have an issue that maybe you can solve.
I have a script that loops and does the following
1. creates a bussiness object that uses this code
2. retrieves the object (one select and a retrieve using the functionallity from the uf_ds_from_sql(). the string is something like "select * from clients where clientid = 1")
3. does something with it
4. destroys it
Works fine except that at the iteration number 380 or 390 the settransobjetc part in the function fails (returns -1) and keeps failing until I stop excecution and restart (another 390 and fails again and so on)

Can you help me?
Aldo
aldobpb
 
Posts: 2
Joined: 12 Oct 2013, 17:16

Link to this posting

Postby Ursego » 16 Oct 2013, 11:40

1. I've never heard about that problem.
2. Is it possible to create a DS once for the whole population and then process its rows? The method you've described (SELECT in a loop) is not looking efficient. BTW, that can also solve your problem.
User avatar
Ursego
Site Admin
 
Posts: 112
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

Link to this posting

Postby aldobpb » 03 Dec 2013, 09:11

Hi, thanks for the repply and the suggestion. I can't follow it because the main loop creates bussines object which use de uf_ds_from_sql() to populate part of their structure. Also, I went back to a previous version of the program that did't implement your function and the problem repeated so now I'm thinking that it may be a database issue (we changed to sqlsrv a while back and we didn't try this particular process). Thank's again for sharing your knowledge.
Aldo
aldobpb
 
Posts: 2
Joined: 12 Oct 2013, 17:16


Return to Tips and Tricks

Who is online

Users browsing this forum: No registered users and 1 guest


uf_ds_from_sql() to create DataStore by dynamic SELECT

Share this topic:


If you think that this site is not too bad, please LIKE it in Facebook. Thanks!





free counters

eXTReMe Tracker