Datastore dynamic with SyntaxFromSql, Failed to retrieve the response status

Resolved Datastore dynamic with SyntaxFromSql, Failed to retrieve the response status

How-to

Votes

Undo

Jose Alberto Ochoa Medina
PowerBuilder
Tuesday, 28 February 2023 22:24 PM UTC

Hi everyone,

I need help, I have a powerbuilder 2022 and it is mounted on a server with windows server 2019, when I am running the application on Windows 11, I have an error when creating a dynamic datastore, it sends me the error: "Failed to retrieve the response status", but this only happens once in a while and I don't know why it happens, I add the code and the error.

It is also important to mention that after the error it starts having problems with database queries and then it resets without problem, do you know why it happens?

//Se crea la instancia para el Data Store
ids_data = Create DataStore

//Si no se indico una santencia
If Len(Trim(as_sq)) <= 0 Then
MessageBox("Error", "Error al crear el DataStore: Asignar sentencia válida", StopSign!)
Return -1
End If

//Validar conexión
IF Not SQLCA.of_IsConnected() AND len(gs_usuario) > 0 AND len(gs_pass) > 0 THEN
ls_usuario_cc = gs_usuario
ls_pwd_usuario_cc = gs_pass
ll_ret = f_coduserpass (ls_usuario_cc,ls_pwd_usuario_cc)
lb_ret = gnv_app.of_Connect (ls_usuario_cc,ls_pwd_usuario_cc)

IF lb_ret = False THEN
Return -1
ELSE
ib_transobject = False
END IF
END IF

IF ib_transobject = True Then
ls_sintax = itr_object.SyntaxFromSQL(as_sq,'Style(Type=Grid)', ls_error_sintax)
Else
ls_sintax = SQLCA.SyntaxFromSQL(as_sq,'Style(Type=Grid)', ls_error_sintax)
End If

IF Len(Trim(ls_error_sintax)) > 0 THEN
f_MessageBox("Human Error 1-2", "Error al crear el DataStore: "+ String(ib_transobject)+ "-" + ls_error_sintax + "-" + as_sq, StopSign!, ok!,1)
Return -1
ELSE
ids_data.Create(ls_sintax, ls_error_ds)
If Len(ls_error_ds) > 0 Then
f_MessageBox("Human Error 2", "Error al crear el DataStore: " + ls_error_ds, StopSign!, ok!,1)
Return -1
END If
END If

is_select = as_sq
is_sintax = ls_sintax

Responses (6)

Miguel Leeuwe Accepted Answer Pending Moderation

Thursday, 9 March 2023 18:21 PM UTC
PowerBuilder
# 1

Hi,

Try setting FormatArgsAsExp to 'No':

// Profile Wave 1 dev.ora19
SQLCA.DBMS = "ORA Oracle"
SQLCA.LogPass = <******>
SQLCA.ServerName = "W1"
SQLCA.LogId = "credit"
SQLCA.AutoCommit = False
SQLCA.DBParm = "PBCatalogOwner='credit',DelimitIdentifier='No',FormatArgsAsExp='No'"

Comment

There are no comments made yet.

Miguel Leeuwe Accepted Answer Pending Moderation

Thursday, 9 March 2023 00:23 AM UTC
PowerBuilder
# 2

Hi,

Unless I've missed it, I don't see your code doing an "ids_data.SetTransObject(sqlca)".

Also, I your ids_data is an instance variable, if you repeatedly do a CREATE of it, then you might want to first DESTROY it if it's already been created.

If isvalid(ids_data) then destroy ids_data

ids_data = CREATE ......

Comment

There are no comments made yet.

Olan Knight Accepted Answer Pending Moderation

Thursday, 2 March 2023 00:50 AM UTC
PowerBuilder
# 3

This is just a guess, but it >>> SOUNDS <<< like there is an incompatibility or a bug in the Windows 2019 server interacting with the Oracle interface.

You might try using a difference Windows server, OR a different interface to Oracle.

And finally, if all else fails, see if an old copy of the code in PowerBuilder 2019 works with the Windows 2019 Server.

Comment

Load more comments

Olan Knight
Thursday, 9 March 2023 00:13 AM UTC

I agree, John.

Joes, try this:

1) Create a variable of the type required (INT, FLOAT) - whatever is used by the database,

2) Use that variable instead of a generic string variable in your SQL.

Change this: WHERE HUMAN.TA135.TA135_CIA = 6125E0

to this:

LONG LS_PARM_VALUE

LS_PARM_VALUE = 6125

....

WHERE HUMAN.TA135.TA135_CIA = LS_PARM_VALUE

...;

From Wikipedia (https://en.wikipedia.org/wiki/Scientific_notation)

E notation

Most calculators and many computer programs present very large and very small results in scientific notation, typically invoked by a key labelled EXP (for exponent), EEX (for enter exponent), EE, EX, E, or ×10x depending on vendor and model. Because superscripted exponents like 107 cannot always be conveniently displayed, the letter E (or e) is often used to represent "times ten raised to the power of" (which would be written as "× 10n") and is followed by the value of the exponent; in other words, for any real number m and integer n, the usage of "mEn" would indicate a value of m × 10n. In this usage the character e is not related to the mathematical constant e or the exponential function ex (a confusion that is unlikely if scientific notation is represented by a capital E). Although the E stands for exponent, the notation is usually referred to as (scientific) E notation rather than (scientific) exponential notation. The use of E notation facilitates data entry and readability in textual communication since it minimizes keystrokes, avoids reduced font sizes and provides a simpler and more concise display, but it is not encouraged in some publications.[2]

Helpful 1

Jose Alberto Ochoa Medina
Tuesday, 14 March 2023 16:55 PM UTC

Hi Olan,

But how can I remove it?

Regards.

Helpful 0

Olan Knight
Tuesday, 14 March 2023 20:53 PM UTC

Jose, I am referring to the stored procedure: modify it as suggested and examine the results.

1) Create a variable of the type required (INT, FLOAT) - whatever is used by the database,

2) Use that variable instead of a generic string variable in your SQL.

Change this: WHERE HUMAN.TA135.TA135_CIA = 6125E0

to this:

LONG LS_PARM_VALUE

LS_PARM_VALUE = 6125

....

WHERE HUMAN.TA135.TA135_CIA = LS_PARM_VALUE

...;

Helpful 1

There are no comments made yet.

Jose Alberto Ochoa Medina Accepted Answer Pending Moderation

Wednesday, 1 March 2023 17:50 PM UTC
PowerBuilder
# 4

Hi Chris, Is your PB App using the Oracle native driver ( SQLCA.DBMS = "ORA" )? Yes Did you install the O19C Instant Client software version that matches your O19C server edition, Yes, i have it installed. Regards

Comment

Load more comments

Jose Alberto Ochoa Medina
Wednesday, 1 March 2023 18:40 PM UTC

Hi Miguel,

It is not solved, continue with the problem

Regards

Helpful 0

Chris Pollach @Appeon
Thursday, 2 March 2023 00:32 AM UTC

Hi Jose;

Thank you for those answers. That all sounds good. Unfortunately since this is an intermittent problem, it's hard to diagnose. However, it sounds like somehow the Oracle driver gets "out of whack" once in a while. So I am not sure even how to advise you on tracing this problem (ie using an SQL Trace) as 99% of the time, it works 100%. :-(

Regards ... Chris

Helpful 0

Jose Alberto Ochoa Medina
Monday, 6 March 2023 20:20 PM UTC

Hi Chris,

We have found that the error occurs when the datastore is executed and in the arguments we don't know why it adds an 'EO' suffix to only numeric arguments.

SELECT DISTINCT HUMAN.V_PROCESOS.TA010_PROCESO, HUMAN.V_PROCESOS.TA020_DESCR

FROM HUMAN.V_PROCESOS, HUMAN.HU_CAT_CIA_CONSOLIDADORA

WHERE     (HUMAN.HU_CAT_CIA_CONSOLIDADORA.NUM_CIA_CONSOLIDADA =

            HUMAN.V_PROCESOS.TA010_CIA)

       AND (HUMAN.HU_CAT_CIA_CONSOLIDADORA.NUM_CIA = 6125E0)

       AND (HUMAN.V_PROCESOS.TA010_MODULO = 0E0)

Regards

Helpful 0

There are no comments made yet.

Chris Pollach @Appeon Accepted Answer Pending Moderation

Wednesday, 1 March 2023 15:17 PM UTC
PowerBuilder
# 5

Hi Jose;

Can you tell us ...

Is your PB App using the Oracle native driver ( SQLCA.DBMS = "ORA" )?
Did you install the O19C Instant Client software version that matches your O19C server edition

Regards ... Chris

Comment

Jose Alberto Ochoa Medina
Wednesday, 1 March 2023 22:00 PM UTC

Hi Chris,

Is your PB App using the Oracle native driver ( SQLCA.DBMS = "ORA" )? Yes

Did you install the O19C Instant Client software version that matches your O19C server edition, Yes, i have it installed.

Regards

Helpful 0

There are no comments made yet.

John Fauss Accepted Answer Pending Moderation

Wednesday, 1 March 2023 01:35 AM UTC
PowerBuilder
# 6

Hi, Jose -

Please clarify: Is the PB 2022 Integrated Development Environment (IDE) installed on the Windows 2019 server, or are you simply running the compiled application on the Windows 2019 server?

If you have the IDE installed on the server, please note it is not supported on that platform; The IDE is supported only on Windows 8.1, Windows 10 and Windows 11.

Also, what DBMS vendor and version is your application using, and what PB database interface (SQLCA.DBMS="???") are you using?

Best regards, John

Comment

Load more comments

Miguel Leeuwe
Thursday, 2 March 2023 02:03 AM UTC

It's not clear to me whether your Powerbuilder IDE is installed on your server or on your windows 11. As John said already, PB applications can "run" on a server, but the installation of powerbuilder itself cannot be on a server. (at least, it's not supported and no guarantees can be given in that case).

Helpful 0

Jose Alberto Ochoa Medina
Monday, 6 March 2023 20:20 PM UTC

Hi Miguel,

We have found that the error occurs when the datastore is executed and in the arguments we don't know why it adds an 'EO' suffix to only numeric arguments.

SELECT DISTINCT HUMAN.V_PROCESOS.TA010_PROCESO, HUMAN.V_PROCESOS.TA020_DESCR

FROM HUMAN.V_PROCESOS, HUMAN.HU_CAT_CIA_CONSOLIDADORA

WHERE     (HUMAN.HU_CAT_CIA_CONSOLIDADORA.NUM_CIA_CONSOLIDADA =

            HUMAN.V_PROCESOS.TA010_CIA)

       AND (HUMAN.HU_CAT_CIA_CONSOLIDADORA.NUM_CIA = 6125E0)

       AND (HUMAN.V_PROCESOS.TA010_MODULO = 0E0)

SELECT HUMAN.TA135.TA135_ZONA,

       HUMAN.TA135.TA135_AREA,

       HUMAN.TA135.TA135_CENTRO,

       HUMAN.TA135.TA135_LINEA,

       HUMAN.TA135.TA135_TIPO_EMPL

FROM HUMAN.TA135

WHERE     HUMAN.TA135.TA135_CIA = 6125E0

       AND HUMAN.TA135.TA135_USUARIO = 'pphseg1'

       AND HUMAN.TA135.TA135_STS = 'A'

[viernes 01:37 p. m.] Raul Franco

SELECT "HUMAN"."HU_CAT_PNEMONICO_DET"."SCVE_PNEM",

       "HUMAN"."HU_CAT_PNEMONICO_DET"."DESCRIPCION"

FROM "HUMAN"."HU_CAT_PNEMONICO_DET"

WHERE (    (HUMAN.HU_CAT_PNEMONICO_DET.NUM_CIA = 6125E0)

        AND (HUMAN.HU_CAT_PNEMONICO_DET.CVE_PNEM = 'MOTIVO AUT RHD'))

Regards

Helpful 0

Olan Knight
Thursday, 9 March 2023 00:43 AM UTC

Take a look at my uggestion above. The idea is to use a LOCAL FIXED VARIABLE of the type required with the SQL.

It might help.

Helpful 0

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.

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

Resolved Datastore dynamic with SyntaxFromSql, Failed to retrieve the response status

Find Questions by Tag