Store procedures

[2002 JI Open Discussion]

By: Muralidhar bp - pmurli

Store procedures

2003-01-17 01:52

Hello,

 

How to use store procedures in an xml document. I want to user store procedure instead of (<query_string>.

Is it possible to use multiple queries in a single xml document.

 

 

 

 

By: Chuck Deal - cdeal

RE: Store procedures

2003-01-17 05:29

I use SQL Server 7.0 and I call stored procedures like this:

 

<queryString><![CDATA[exec usp_rpt_TESTStepsByStatus $P!{BaselineId}, $P!{ProgramId}, '$P!{StepStatus}', '$P!{DevStatus}', $P!{AuthorId}]]></queryString>

 

Of course, I don't use OUTPUT parameters so this is acceptable.

 

 

 

 

By: Teodor Danciu - teodord

RE: Store procedures

2003-01-17 05:55

 

Hi,

 

You could call in the query string stored procedures

that return result sets and do not have OUT parameters.

 

Thank you,

Teodor

 

 

 

 

 

By: Muralidhar bp - pmurli

RE: Store procedures

2003-01-20 05:23

Hii

 

Thanx for the reply, I kindly request you to send me the example for the same. I dont know how to handle the result set in the Jasper Reports. My email is pmurli@psi.soft.net

 

Thanx

Murli

 

 

 

 

By: Chuck Deal - cdeal

RE: Store procedures

2003-01-21 05:42

Is your stored proceure complex? Complex meaning more than one resultset or uses OUT parameters?

 

If not, then you can simply imagine that the call to the stored procedure and the call to a query are the same thing... both return a list of rows delimited into columns.

 

Therefore, you can handle a simple stored procedure the same way you would handle a query in JasperReports.

 

 

 

 

By: Muralidhar bp - pmurli

RE: Store procedures

2003-01-22 06:03

Hello Charles,

 

Thanks for the reply, will you please write a simple procedure which gives the result set and please show me how you r using it in the XML file.

 

Please tell me what i should write in xml file which shows the " ************* " indication mark.

I mean should I have to write a column name or procedure name

 

<queryString><![CDATA[exec SwiftMessage $P!{LCNumber}]]></queryString>

 

<field name="******************" class="java.lang.String"/>

 

<detail>

<band height="500">

<textField isStretchWithOverflow="true">

<reportElement x="0" y="5" width="500" height="495" forecolor="#333333"/>

<textFieldExpression class="java.lang.String">

$F{*************************}

</textFieldExpression>

</textField>

</band>

</detail>

 

Please help me.

 

Thanks

Murli

 

 

 

 

By: Chuck Deal - cdeal

RE: Store procedures

2003-01-22 07:13

Sure, I'll give it a try.

 

You have a users table that contains two varchar fields: last_name and first_name.

 

Then, we could write a sql stmt like this to get those values.

 

SELECT last_name, first_name FROM users

 

This would return a two column resultset with as many rows as there are in this table.

 

Now, you could create a stored procedure called usp_rpt_GetUsers. For simplicity we will not have any parameters. The body of this stored procedure should contain the query that we defined earlier in the message. Therefore, if we execute that stored procedure like this:

 

exec usp_rpt_GetUsers

 

We will also get a two column resultset with as many rows as there are in the database.

 

That brings us up to how to use this stored procedure in the JasperReports XML definition. Using yur basic example, I will fill in the blanks as best as I can.

 

<queryString><![CDATA[exec usp_rpt_GetUsers]]></queryString>

 

<field name="last_name" class="java.lang.String"/>

<field name="first_name" class="java.lang.String"/>

 

<detail>

<band height="500">

<textField isStretchWithOverflow="true">

<reportElement x="0" y="5" width="500" height="495" forecolor="#333333"/>

<textFieldExpression class="java.lang.String">$F{last_name}</textFieldExpression>

</textField>

<textField isStretchWithOverflow="true">

<reportElement x="500" y="5" width="500" height="495" forecolor="#333333"/>

<textFieldExpression class="java.lang.String">$F{first_name}</textFieldExpression>

</textField>

</band> </detail>

 

From here, you should be able to see how to add parameters to the stored procedure. Remember only INPUT parameters are supported by JasperReports. Therefore, it you were to establish a filter by, let's say last_name, you could pass that parameter to the stored procedure. Your resultset will still contain two columns, but your filter would have limited the number of rows returned.

 

Now, as far as your data being of CLOB type, you may have to perform some conversion in your stored procedure to make the CLOB type a varchar (as you mentioned in another post).

 

Good luck, I hope I was able to help you.

 

 

 

 

By: Muralidhar bp - pmurli

RE: Store procedures

2003-01-22 09:08

Hello,

 

Thank you very much for the information, please have a look at my procedure and give suggestions.

Also tell me if there is any mistake in this procedure.

 

 

CREATE OR REPLACE PROCEDURE StillMessage(p_lc_number IN STILLLOG.LC_NO%type) AS

v_text_loc CLOB;

v_text_amt VARCHAR2(32000):=80;

v_text_pos INTEGER:=1;

v_text_buffer VARCHAR2(32000);

v_text_length INTEGER;

v_error_code VARCHAR2(100);

v_error_msg VARCHAR2(100);

BEGIN

SELECT STILL_MESSAGE INTO v_text_loc FROM STILLLOG WHERE LC_NO=p_lc_number;

v_text_length := DBMS_LOB.GETLENGTH(v_text_loc);

LOOP

DBMS_LOB.READ(v_text_loc, v_text_amt, v_text_pos, v_text_buffer);

v_text_pos := v_text_pos + v_text_amt;

 

IF ((v_text_pos+80)>= v_text_length) THEN

v_text_amt := v_text_length - v_text_pos;

END IF;

EXIT WHEN v_text_pos >= v_text_length;

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

v_error_code := SQLCODE;

v_error_msg := SQLERRM;

END;

/

 

 

 

Please give suggestions on this.

Thanks, once again for your effort to educate me.

 

Thanks

Murli

 

 

 

 

 

By: Chuck Deal - cdeal

RE: Store procedures

2003-01-22 11:25

I'm not familiar with the particular syntax you are using, but it doesn't look like you are actually returning a resultset. As far as I know, there is no explicit way to do this, you simply run a select statement.

 

Here is my intepretation of your stored proc and a potential solution:

 

It looks as if you are selecting a single CLOB field out of your table and then processing it (i'm a little fuzzy on the actual processing that you are doing, but that is ok, for now). When the loop finishes, my guess is that your final value is stored in v_text_buffer (?). The thing is, no where in your proc did you execute a plain-old SELECT stmt. Here is what I would try. Right after END LOOP and before EXCEPTION put this stmt:

 

SELECT v_text_buffer AS clob_field

 

Based upon what I can see of your proc this will cause a resultset of 1 column and 1 row to be returned. Then, your <field> tag will use "clob_field" as the name attribute.