Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Datawindow from Stored Procedure

  1. How-to
0
Votes
Undo
  1. Pepe Cuenca
    Pepe Cuenca
  2. PowerBuilder
  3. Thursday, 19 November 2020 08:59 AM UTC

Good Morning Everyone!

I'm trying to populate a Datastore directly from a Stored Procedure (first time that I do this)

I create the DW as a Grid, then select Stored procedure and choose it from the list, everything fine. 

It asks me for the retrieval arguments when I try to retrieve data from the DW, so I understand that I've done everything OK...

 

Then, in the code, I declare the DW

Datastore lds_jornadas

lds_jornadas = create Datastore
lds_jornadas.dataobject = 'd_jornadas_hispatec'
lds_jornadas.settransobject(sqlsrv) 

and then I do:

ll_jornadas = lds_jornadas.Retrieve('11',01/01/2020,17/11/2020,'','','','')

And ll_jornadas becomes -1

but sqlrsv.sqlcode is 0.

Could anybody tell me what I'm doing wrong, or which is the best way to obtain data(multiple rows) from an stored procedure?

Thank you very much!!!

 

 

 

Applytheme function 2019 R2 Build 2328
Opened sheet has been covered by the Ribbonbar win...
Accepted Answer
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 19 November 2020 16:15 PM UTC
  2. PowerBuilder
  3. # Permalink

Greetings, Pepe -

I believe the problem is the way you are supplying the datetime argument values as only date constants in the Retrieve. I suggest you try the following alternatives:

1. Use datetime variables and assign the date AND time values using the Datetime function:

Datetime ldt_FechaInicio, ldt_FechaFin

ldt_FechaInicio = Datetime(Date('01/01/2020'),Time('00:00:00'))
ldt_FechaFin    = Datetime(Date('11/17/2020'),Time('23:59:59'))

ll_jornadas = lds_jornadas.Retrieve('11',ldt_FechaInicio,ldt_FechaFin,'','','','')

2. Supply the date AND time value in a string constant in the Retrieve call:

ll_jornadas = lds_jornadas.Retrieve('11','01/01/2020 00:00:00','17/11/2020 23:59:59','','','','')

Regards, John

Comment
Pepe Cuenca
  1. Pepe Cuenca
  2. Thursday, 19 November 2020 16:29 PM UTC
Hi John!!

Tried this way and now ll_jornadas is 0 (it's a good thing becouse there is no data yet...)

Thank you very much!!!!
  1. Helpful
John Fauss
  1. John Fauss
  2. Thursday, 19 November 2020 17:09 PM UTC
Terrific news! You're welcome!
  1. Helpful
There are no comments made yet.
Responses (3)
Pepe Cuenca
Pepe Cuenca Accepted Answer Pending Moderation
  1. Thursday, 19 November 2020 14:26 PM UTC
  2. PowerBuilder
  3. # 1

Hi, 

Thank you for the reply. 

If I execute this in the management studio, it does work:

 

EXEC ObtenerCosteHistorico_Prenomina '11','01/01/2020','17/11/2020','','','',''

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 19 November 2020 17:17 PM UTC
Aha, so that's different from your powerbuilder call: Here you pass the dates as a string and in the powerbuilder retrieve your were passing datetime values.

Glad it's solved!
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 19 November 2020 13:47 PM UTC
  2. PowerBuilder
  3. # 2

Hi,

If I have to guess, probably something to do with the dates or its formats.

Try opening the datawindow in the IDE and do a 'preview'. 

Does it work when you run the stored procedure in SQL Server Management Studio?

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.