executing procedure from powerscript in a loop does not work properly

[Former Member]

Dear all,

I have one stored procedure in Sybase and i am running that stored procedure in powerscript in a loop for different parameter. It randomly generates the output of that procedure.

Stored procedure calculates some data and calculated value is stored in the database and it is then viewed by the user. It is run for different period say 3,6,9 and 12 month as one of its parameter. i have following script written

For li_start = 1 to  li_cnt

   ls_sql = "EXEC calc " + string(ll_period)

   li_ret = SQLCA.of_execute( ls_sql)

   If li_ret <> 0 Then

     -- error message in log file

   else

     -- success message in log file

  end if

When user checks for the calculated data, it is missing randomly. sometime 3 month data is missing and rest is available and sometime, 6 month data is missing and rest are available or sometime 3 and 6 is missing and rest is available. There is no such fix pattern.

if i run the script from back end one by one as

exec calc 3

go

exec calc 6

go

exec calc 9

go

exec calc 12

go

it calculates the data for all periods without any issue.

any idea why it is happening as it seems that powerbuilder has some issues related with some transaction or donno something else

-Brijesh

[Former Member]

One thing I would say that may have nothing to do with your problem is there isn't a need to execute your proc using the execute immediate syntax. You could just hang it off your transaction object and call it directly like any other proc passing it your argument. I only use the execute immediate syntax when building the sql dynamically at runtime.

You say it works on the backend when you call the proc one right after another. I would try the same thing in PB by eliminating the loop there...

SQLCA.of_execute("Exec calc 3")

SQLCA.of_execute("Exec calc 6")

SQLCA.of_execute("Exec calc 9")

SQLCA.of_execute("Exec calc 12")

You may want to check where you're performing a commit.

hth,

Mark

[Former Member]

can i perform commit after calling function of_execute( parameter )?

if yes then how?

thanks

-Brijesh

[arnd_schmidt]

COMMIT using SQLCA;

or use some of_commit ()  function in your transaction object.

[Former Member]

And what does your log file say?  Always success?