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

/**********************************************************************************************************************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_rcstring ls_err_msgstring ls_syntaxDataStore 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."))

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 following1. creates a bussiness object that uses this code2. 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 it4. destroys itWorks 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)

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.

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