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

Resolved Problem Creating Stored procedure Datawindow

  1. How-to
0
Votes
Undo
  1. Pepe Cuenca
    Pepe Cuenca
  2. PowerBuilder
  3. Monday, 8 March 2021 15:04 PM UTC

Hi EveryOne, 

I'm trying to create a Datawindow from an stored procedure, I've done it lots of times but this one is not working properly.

The stored procedure is in an SQL Server Database. 

As always, I do a NEW - Datawindow - Grid - Stored procedure.

It shows me all the stored procedures in the DB, and when I select the one I want, it gives me an error:

"Error converting a character string in data and/or time"

I've activate the trace an the log file shows the sentence used to build the DW:

 

execute dbo.InformeRegistroAnalisisActuacion;1 @DesdeProveedor = '0', @HastaProveedor = '0', @RangoProveedores = '0', @DesdeFinca = '0', @HastaFinca = '0', @RangoFincas = '0', @DesdeCultivo = 0, @HastaCultivo = 0, @RangoCultivos = '0', @RangoTipoAnalisisFinca = '0', @RangoTipoAnalisisCultivo = '0', @DesdeFecha = '1990-07-14 00:00:00.000', @HastaFecha = '1990-07-14 00:00:00.000', @RangoFechas = '0', @DesdeTecnico = '0', @HastaTecnico = '0', @RangoTecnicos = '0', @ObtenerEsquema = 0

The problem is in the Datetime format that must be yyyymmdd and in the variable @RangoFechas, that doesn't like to be 0 it seems.

The question is, can I create the Datawindow assigning manually the data, and then changing it when I want to use it?

If I rune the procedure from the code, It works ok:

Declare procedimiento Procedure For InformeRegistroAnalisisActuacion '', '', '', '', '','',0,0,'','', '1067', NULL, NULL, '', '','', '',1
USING sqlsrv;
EXECUTE procedimiento;

But I prefer to populate the DW directly than doing all the stuff...

Is this possible?

 

 

PowerBuilder 2019 R2 - import pbd extension
PB2019R3 random crash
Responses (4)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 9 March 2021 15:02 PM UTC
  2. PowerBuilder
  3. # 1

This does not make a lot of sense... I cannot help but think there are some key factors that you have not disclosed. Is the information in this database imported from and IBM DB2 database? When I search for "SQL Server SQLSTATE 22007" I get hits related to data imported from DB2 databases. And you still have not told me what version of SQL Server you are running, so I'm wondering what else you have not told me.

Ignoring PB for a second, in SQL Server Management Studio, can you execute the SQL statement:

execute dbo.InformeRegistroAnalisisActuacion @DesdeProveedor = '0', @HastaProveedor = '0', @RangoProveedores = '0', @DesdeFinca = '0', @HastaFinca = '0', @RangoFincas = '0', @DesdeCultivo = 0, @HastaCultivo = 0, @RangoCultivos = '0', @RangoTipoAnalisisFinca = '0', @RangoTipoAnalisisCultivo = '0', @DesdeFecha = '1990-07-14 00:00:00.000', @HastaFecha = '1990-07-14 00:00:00.000', @RangoFechas = '0', @DesdeTecnico = '0', @HastaTecnico = '0', @RangoTecnicos = '0', @ObtenerEsquema = 0

Does this work? If not, what error(s) do you get?

Still in SQL Server Management Studio, if you then revise the Execute statement to be:

execute dbo.InformeRegistroAnalisisActuacion @DesdeProveedor = '', @HastaProveedor = '', @RangoProveedores = '', @DesdeFinca = '', @HastaFinca = '', @RangoFincas = '', @DesdeCultivo = 0, @HastaCultivo = 0, @RangoCultivos = '', @RangoTipoAnalisisFinca = '', @RangoTipoAnalisisCultivo = '', @DesdeFecha = '19900714', @HastaFecha = '19900714', @RangoFechas = '', @DesdeTecnico = '', @HastaTecnico = '', @RangoTecnicos = '', @ObtenerEsquema = 0

This works?

Two observations:

1. Your assumption that '0' is some kind of default or wild-card is incorrect; It appears that the stored procedure expects an empty string to be used instead.

2. The normal format for date/datetime values in SQL Server does not apply here, which seems to point back to how the data was imported from DB2, if the info regarding SQLSTATE 22007 is correct.

Back in PB, you might try specifying the datetime format in the SQLCA.DBParm setting:

SQLCA.DBParm = "Provider='SQLNCLI11',Database='ERPHispatec',DateTimeFormat='yyyymmdd'"

and maybe also Date='yyyymmdd' will be needed... I don't know as I have never encountered this kind of issue before.

If these suggestions don't work, regrettably I have no other ideas I can offer you.

Comment
Pepe Cuenca
  1. Pepe Cuenca
  2. Tuesday, 9 March 2021 16:31 PM UTC
Hi John!

The DW is now created.

The stored procedure creates the table result columns dinamically depending on the entry values and the field @RangoFechas = '0' should be ' '. The owner has made a simple change and it is working now.

Thank you for everything!!
  1. Helpful
John Fauss
  1. John Fauss
  2. Tuesday, 9 March 2021 16:40 PM UTC
That is terrific, Pepe! I'm really glad to hear this news. Would you so please be so kind as to mark this issue as resolved?
  1. Helpful
There are no comments made yet.
Pepe Cuenca
Pepe Cuenca Accepted Answer Pending Moderation
  1. Monday, 8 March 2021 17:03 PM UTC
  2. PowerBuilder
  3. # 2

Hi John, 

I can't obtain a resultset because the Stored procedure is not mine, is for another company.

I don't have anything in my dbparm string, just Provider='SQLNCLI11',Database='ERPHispatec', nothing about the datetime format. 

 

This is the version of PB

And this is my connection string:

 

// Profile sql_hispatec
SQLCA.DBMS = "TRACE SNC SQL NATIVE CLIENT(OLE DB)"
SQLCA.LogPass = <*******>
SQLCA.ServerName = "SRVHAMSQL01\ERP"
SQLCA.LogId = "xxxxx"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Provider='SQLNCLI11',Database='ERPHispatec'"

 

Thank you very much!

 

Comment
John Fauss
  1. John Fauss
  2. Monday, 8 March 2021 17:30 PM UTC
Thank you for the additional information, Pepe.

Can you execute the stored procedure manually from the ISQL pane in PB's Database Painter?

If you can inspect the stored procedure code in SQL Server, why can you not determine the result set?

If the stored procedure result set is returning very long character strings, be aware the DataWindow cannot handle string values longer than 32766 characters in length.

For testing purposes, can you create a mostly empty stored procedure (I'll call it MyTest) that has the same argument parameters and contains only a very simple SELECT statement that returns a single row with a single column ... something like: S"ELECT MyValue = 123 FROM (SELECT a=1) AS test'? That would help determine if the problem is in the retrieval arguments or in the result set description or values.

Do you know if the stored procedure is returning any columns that use the SQL Server datetime2 datatype?
  1. Helpful
Pepe Cuenca
  1. Pepe Cuenca
  2. Tuesday, 9 March 2021 11:48 AM UTC
Hi John!

I can execute the stored procedure from ISQL panel, but same thing happens.

When I create the DW, the dbtrace file shows:



execute dbo.InformeRegistroAnalisisActuacion;1 @DesdeProveedor = '0', @HastaProveedor = '0', @RangoProveedores = '0', @DesdeFinca = '0', @HastaFinca = '0', @RangoFincas = '0', @DesdeCultivo = 0, @HastaCultivo = 0, @RangoCultivos = '0', @RangoTipoAnalisisFinca = '0', @RangoTipoAnalisisCultivo = '0', @DesdeFecha = '1990-07-14 00:00:00.000', @HastaFecha = '1990-07-14 00:00:00.000', @RangoFechas = '0', @DesdeTecnico = '0', @HastaTecnico = '0', @RangoTecnicos = '0', @ObtenerEsquema = 0



This doesn´t work in the painter



If I change all the '0' values to '', and the datetime fields '19900714', it works:



execute dbo.InformeRegistroAnalisisActuacion @DesdeProveedor = '', @HastaProveedor = '', @RangoProveedores = '', @DesdeFinca = '', @HastaFinca = '', @RangoFincas = '', @DesdeCultivo = 0, @HastaCultivo = 0, @RangoCultivos = '', @RangoTipoAnalisisFinca = '', @RangoTipoAnalisisCultivo = '', @DesdeFecha = '19900714', @HastaFecha = '20210714', @RangoFechas = '', @DesdeTecnico = '', @HastaTecnico = '', @RangoTecnicos = '', @ObtenerEsquema = 0;





  1. Helpful
There are no comments made yet.
Pepe Cuenca
Pepe Cuenca Accepted Answer Pending Moderation
  1. Monday, 8 March 2021 16:03 PM UTC
  2. PowerBuilder
  3. # 3

Hi @John !

Here is the SP code:

 

ALTER PROCEDURE [dbo].[InformeRegistroAnalisisActuacion]
--DECLARE
@DesdeProveedor VARCHAR(20)
, @HastaProveedor VARCHAR(20)
, @RangoProveedores VARCHAR(MAX)
, @DesdeFinca VARCHAR(20)
, @HastaFinca VARCHAR(20)
, @RangoFincas VARCHAR(MAX)
, @DesdeCultivo INT
, @HastaCultivo INT
, @RangoCultivos VARCHAR(MAX)
, @RangoTipoAnalisisFinca VARCHAR(MAX)
, @RangoTipoAnalisisCultivo VARCHAR(MAX)
, @DesdeFecha DATETIME
, @HastaFecha DATETIME
, @RangoFechas VARCHAR(MAX)
, @DesdeTecnico VARCHAR(20)
, @HastaTecnico VARCHAR(20)
, @RangoTecnicos VARCHAR(MAX)
, @ObtenerEsquema BIT
AS
-- Establecemos los valores por defecto
DECLARE @MaxChar VARCHAR(3), @MinChar VARCHAR(1), @MaxDate DATETIME, @MinDate DATETIME, @MinInt INT, @MaxInt INT
SELECT @MaxChar = 'zzz', @MinChar = '', @MaxDate = '30000101', @MinDate = '19000101', @MinInt = 0, @MaxInt = 2147483647

 

I can't edit the source of the Datawindow because I can't even create it...

 

Comment
John Fauss
  1. John Fauss
  2. Monday, 8 March 2021 16:39 PM UTC
Thanks for this information. I understand you are unable to create the DataWindow. Can you please supply your SQLCA.DBParm string and SQL Server DBMS version?

Can you please also supply a description of the result set returned by the stored procedure (the data type of each column)? I suspect this is where the problem may be.
  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Monday, 8 March 2021 15:27 PM UTC
  2. PowerBuilder
  3. # 4

Greetings, Pepe - 

You should be able to use datetime argument values in a SQL Server stored procedure that is the data source for a DataWindow. The problem you are having is likely related to one or more data type mismatches. Can you please post the initial portion of the SQL source code for the stored procedure that defines the argument list (Declare Procedure dbo.InformeRegistroAnalisisActuacion @DesdeProveedor=???, @HastaProveedor = ???, etc.) so that we may see the SQL data type for each argument parameter?

Also edit the source for the DataWindow and extract the just the part of the syntax where the DataWindow's retrieval arguments are defined and include that information.

Lastly, please tell us the SQLCA.DBParm string you are using to connect to the SQL Server database with the SNC driver. Please mask out any sensitive information.

What version of SQL Server are you using? What version, release, build of PB are you using?

I'm a little concerned with your statement: "the Datetime format that must be yyyymmdd...". Can you please explain what you mean by this, because the display format you use in your PB app should really not affect data retrieval, unless I am misunderstanding what you are saying.

Regards, John

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.