MS SQL Plugin -> get result of select to variable

[StyX]

So I'm running a select query where I get a folder path back (like c:\\temp) and I want to store this in a variable. Now when I use the return variable provided by the plugin I simply get Success back into the variable.

It might be me having a real brain freeze here. But anyone out there that has a suggestion ?

[MichaelNesmith]

Sorry, we don't have the ability to return that right now. We are currently testing a new version of the plug-in with some volunteers that returns detailed error messages. However, a return value from a script is currently not supported.

If you would like to participate in the current set, and offer us help in also returning an error result from the script execution, please contact me privately.

[Marko]

Hi Mihael

Is there anything new?
I am very interesting in getting result from sql scripts.
I am currently using version 10.10 and still getting SUCCES as a result.


Regards
Marko

[giaviv]

Dear Marko,

I am afraid that we are still unable to return results from SQL scripts. Please consider other alternatives such as writing to a file and then reading from it.

[Marko]

Hi Aviv

I am already using this alternatives but sometimes is a little hard to get results.
I hope you will come up with some solution.


Best whishes
Marko

[Marko]

Hi Aviv

I came up with solution to get result from Oracle script.
It is not perfect and may not be suitable for all situations.

It is based on the fact that executing the script returns result in variable which returns the status of the script (SUCCESS, some error, ...).
Well, if there is an error then this variable returns complete contents of that error. So there is a statement in Oracle to produce such an error:
raise_application_error(err_num, err_txt);
Based on this I created script (see example):

declare
rez string(10);
begin
select count(*) REZULTAT into rez from user_tab_columns
where table_name = 'BP3EXCG' and column_name = 'TIPUVOZ';
raise_application_error(-20000, rez);
end;

First you define (declare) an variable to store result of the script.
Then modify the script by inserting - "into rez".
Finally insert raise_application_error statement.
Then on Oracle Database Script DialogBox select "Abort execution on script error".

When script is executed an error will raise and variable of Oracle Database Script will get the content of this error:
ORA-20000: "content of rez" ORA-XXXXX $NEWLINE$ "ERROR message"


I hope this will help someone.


Regards
Marko

[giaviv]

Thanks for sharing!

[mikKummerfeld]

still not possible? I have a select statement but I need the result of that...like into a table in my setup

[FrancescoT]

Dear Mik,

Unfortunately, it is not possible.

Regards

[mikKummerfeld]

So I can only create a sql statement to create a table on the sql server (so push something, but not get results)?
Do you have a workaround for that problem ???

----> Point for InstallShield right?

[FrancescoT]

Dear Mik,

Honestly, I really don't if it is possible with that "other" tool you mentioned .

Anyway, if I'm not wrong, you should use a stored procedure in order to have a result.
You could try to run a call to SQLEXEC using Run Program command and then checking the result value ... probably it works.
I used SQLEXEC to run script in the past, but I never tried with a stored procedure.

Let me know.

Regards

[mikKummerfeld]

We use now sqlcmd from sql native client and write that into a textfile and then we read it...now it works

[FrancescoT]

Happy you solved !

[christ23]

Hi Mik (and all the others),

if you only get ONE parameter back (sounds like this as you are saying you want to have "C:\.....") - maybe then i got a solution for you. Or the first steps for a solution .

I have written a C# plugin, which allows you to get back ONE value (we only need to get something back from "select count(*) from...."). If you want, i can send you the source code of my plugin. (It is written for oracle, but the logic will be still the same on nearly every other DB type).

Just give post and i will send it to you.

[mikKummerfeld]

I need more than 50 results from one query, sometimes about 6000 But it is solved very good now.
Thank you