1. 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!!!

 

 

 

Accepted Answer
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
  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
  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.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 19 November 2020 13:47 PM UTC
  2. PowerBuilder
  3. # 1

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.
Pepe Cuenca Accepted Answer Pending Moderation
  1. Thursday, 19 November 2020 14:26 PM UTC
  2. PowerBuilder
  3. # 2

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
  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.
  • Page :
  • 1


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