PB Newbie - DW's DataSource

[Former Member]

Hi PB Gurus,

I need help.

I have a stored procedure (SQL Server 2012) with argument. It run ok in Database painter. Now, I want the result of my stored procedure to be the

datasource of my datawindow. How can I achieve this?

TIA,

[Former Member · Accepted solution]

Hi Ryan;

  I'm starting a new sub-thread here to make this posting easier to read - but, continuing from your last test that you posted. As far as I can tell, I have created your example table & SP using the same names and pasting in your SP code "as is". Here are my test results ...

1) Create "tbl_user" & populate with test data ...

2) Create identical SP to yours ....

3) Create SP based DataWindow. On Retrieve, entered "1" for ID ...

                                 No problems! 

Note: Again, I am using SNC to connect to SS - not ODBC.

HTH

Regards .. Chris

[Former Member · Accepted solution]

Thank you so much, Chris!

[Former Member · Accepted solution]

I did what you said, Chris.

Everthing is ok. But during runtime I have to specify the arguments.

I wish, the arguments would be based on the sle_1.text.

And this window('Specify Retrieval Arguments') would not pop out.

Is that possible?

Thanks!

[Former Member · Accepted solution]

dw_1.settransobject (SQLCA)

dw_1.Retrieve ( sle_1.text )

or

dw_1.Retrieve ( long ( sle_1.text ))

[Former Member]

If you have created a datawindow that works in the datawindow painter from the stored procedure, what do you mean by using the procedure as data source?

[Former Member]

Here is a sample code:

Under a clicked event  of commandbutton, I typed:

   DECLARE getempid PROCEDURE FOR dbo.getempid

      @empid = :parent.sle_1.text

  USING SQLCA_POA ;

Now, my execute command gives me a syntax error.

execute getempid;1 @empid = :empid

But when I execute it under database painter it works fine.

Please see my screenshots

TIA,

[Former Member]

Was that datawindow created with Stored Procedure as data source? You can't change the SQL in a datawindow to call the procedure.  If it was created from ps then was the procedure changed since?

[Former Member]

Hi Ryan;

  Was the column EmpID data type changed since the DW was originally built?

Regards .. Chris

[Former Member]

EmpID is char(7) and it wasn't changed.

All I did was create a stored procedure in SQL Server 2012

and when I execute it under DB painter it is ok. but when I place it in a DW painter or window and have it executed under the clicked event of a commandbutton, it gives me a syntax error.

Thank you Chris!

[Former Member]

All I did was create a stored procedure in SQL Server 2012

and when I execute it under DB painter it is ok. but when I place it in a DW painter or window and have it executed under the clicked event of a commandbutton, it gives me a syntax error.

Thank you, Lars!

[Former Member]

Thing is, you don't "place it in the DW painter", you must create a new datawindow with stored procedure as a data source:

[Former Member]

Hi, Lars!

I did that and this is what happened.

What I'm trying to do is have a datawindow with datasource as SP and my SP should have and argument..

Thank you

[Former Member]

:id argument must be of type string...

[Former Member]

My id field in the DB is int.

Should all my argument be string?

Thanks you, Lars

[Former Member]

Hi Ryan;

  Can you post your SP source?

Regards ... Chris

[Former Member]

Sorry Ryan,

I am getting your two stored procedures mixed up... sp_getid .... int  and sp_getempid ..... string

Lars

[Former Member]

My SP (SQL Server 2012) :

create procedure [dbo].[getid_1] @id int output, @empid char(7) output

as

select @id = @id

select @empid = @empid

select id,empid from tbl_user  where id = @id

go

When I execute this in SQL Server Management Studio, everything is ok.

My PBscript(PB Classic 12.0):

int id

id = integer(parent.sle_1.text)

DECLARE getid_1 PROCEDURE FOR dbo.getid_1

@id = :id OUTPUT

USING SQLCA_POA ;

execute getid_1 @id = :id;

And this gives me a syntax error and Invalid character value for cast specification.

What I want to simply achieve is to create a SP with arguments(SQL Server 2012)

and during runtime, my user will have to key in the argument for my sp then execute it under clicked event of a button

and populate my datawindow with the result from my SP.

Thank you so much, Chris!

[Former Member]

I am not sure combining result sets and output variable is a good idea.

If you

1) create just one stored procedure:

     create procedure [dbo].[getempid] @id int

          as

     select id, empid from tbl_user  where id = @id

2) create a new datawindow with the stored procedure as data source

3) modify the button clicked event:

     dw_1.SetTransObject ( SQLCA )

     dw_1.Retrieve ( long ( sle_1.text ))

That should do?

[Former Member]

Ok. I'll try it.

Thank you so much, Lars!

[Former Member]

Hi Ryan;

  Here is what I used in my SS2012 SP code as a test ....

Create Procedure GetID 

@employee_id            Integer

AS

  SELECT emp_id,  salary

    FROM Employee

       WHERE emp_id = @employee_id 

  Here is the SP based DataWindow I built to test it ...

HTH

Regards ... Chris

[Former Member]

Here is a little fancier version of the SP. The advantage to this approach is that you can cast, compute, derive, etc your columns, names & values with more flexibility.

----------------------------------------------------------------------------

Create Procedure GetID 

@employee_id            Integer

AS

  BEGIN

      DECLARE  @EmpID as Integer

      DECLARE  @EmpSal as Numeric (8,2)

      SET @EmpID    = (  SELECT emp_id   FROM Employee   WHERE emp_id = @employee_id  )

      SET @EmpSal    = (  SELECT salary     FROM Employee   WHERE emp_id = @employee_id  )

      SELECT @EmpID, @EmpSal

  END

;

------------------------------------------------------------------------------------

Food for thought

PS: this also worked 100% with my SP based DW. 

[Former Member]

I'm lost.

I created a new SP in SQL Server :

Create Procedure GetID_new

@gid Integer

AS

  SELECT id, empid,  username

    FROM tbl_user

       WHERE id = @gid 

I execute it. Here is the result:

Everything is ok.

Now, in my PB 12 Classic, error occured:

In Datawindow Painter:

I tried to retrieve, entered 1 in value field:

Here is what I got:

Thank you, Chris!

[Former Member]

Ahhhh ... I'm using SNC to connect to SS not OBDC. I wonder if that is causing your SP issues. I never use any Middleware DB drivers - always the native client.