• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Datawindow Retrieve Error : 1722 - select Error : SQLstate = 22005

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

Resolved Datawindow Retrieve Error : 1722 - select Error : SQLstate = 22005

  1. Issue
0
Votes
Undo
  1. Stephane SHAN
    Stephane SHAN
  2. PowerBuilder
  3. Tuesday, 8 December 2020 10:43 AM UTC

Hello,

since I migrated an old app developped with PB 7 to PB 2019, I have a big problem with some SQL queries from my Datawindows when executed on a database under Oracle 11g. I get this message : 1722 - select Error : SQLstate = 22005. 

But the same queries work fine when run on the same database under MS SQL Server. I am developing my application with an MS SQL Server database. And the application works on a database under Oracle at my client's. it always worked well until the migration to PB 2019.

There is my query : 

SELECT ce_personnel.int_id,
ce_personnel.nom,
ce_personnel.prenom,
ce_personnel.situ_famille,
ce_personnel.conjoint_id,
ce_personnel.anonyme,
(Select c.adherent_actif from ce_cotisation c where c.id_personne = ce_personnel.int_id and c.id_budget = ? and c.adherent_actif = '1') as adherent_actif,
(select q.num_tranche from ce_vue_qf_adherent q where q.id_personnel = ce_personnel.int_id and q.id_budget = ?) as num_tranche_qf
FROM ce_personnel
ORDER BY ce_personnel.nom ASC,
ce_personnel.prenom ASC

 

Thank you for helping me !

Stephane

  1. DataWindow
  2. Oracle
  3. PowerBuilder
  4. Query
DataWindow issues passing arguments to nested repo...
Application will not launch from a command line bu...
Responses (7)
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 8 December 2020 14:08 PM UTC
  2. PowerBuilder
  3. # 1

Hi Stephane,

I guess error 1722 ist ORA-1722? This error is "Invalid number". 

Seems there is a string to number conversion in your SQL that not work for your data. Check the column data types of all columns (also in where clause) and the argument data types.

HTH,

René

Comment
Stephane SHAN
  1. Stephane SHAN
  2. Tuesday, 8 December 2020 14:22 PM UTC
Hi René,

Thank you for your interest in my problem.



I have checked data type of all fields. If I execute my query without retrieve argument, it works.

The field "id_budget" is an Integer. And the argument "arg_id_budget" is a number.



SELECT ce_personnel.int_id,

ce_personnel.nom,

ce_personnel.prenom,

ce_personnel.situ_famille,

ce_personnel.conjoint_id,

ce_personnel.anonyme,

(Select c.adherent_actif from ce_cotisation c where c.id_personne = ce_personnel.int_id and c.id_budget = 20201 and c.adherent_actif = '1') as adherent_actif,

(select q.num_tranche from ce_vue_qf_adherent q where q.id_personnel = ce_personnel.int_id and q.id_budget = 2021) as num_tranche_qf

FROM ce_personnel

ORDER BY ce_personnel.nom ASC,

ce_personnel.prenom ASC



I don't have problem when I execute my App with MS SQL Server. Apart from the declaration of the argument, I don't find the item that is the problem with Oracle.



Stéphane
  1. Helpful
There are no comments made yet.
David Peace (Powersoft)
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Tuesday, 8 December 2020 17:27 PM UTC
  2. PowerBuilder
  3. # 2

Hi

Can you reconstruct the DW and still get the problem?

regards

David

 

Comment
Stephane SHAN
  1. Stephane SHAN
  2. Tuesday, 8 December 2020 17:53 PM UTC
Hi David,



I have done it. I have a lot of datawindow with this error. They work perfectly on MS SQL Server. My development environment is based on MS SQL Server. The application is deployed on a Production environment based on Oracle. With the old version developed under PB 7, I don't have problem.



I suspect it is the nested selects in my queries that are the problem, like this :

SELECT ce_reglement.int_id,

ce_reglement.date_saisie,

ce_reglement.date_modif,

ce_reglement.id_utilisateur,

ce_reglement.libelle,

ce_reglement.montant,

ce_reglement.num_cheque,

ce_reglement.credit,

(select b.abreviation from ce_banques b where b.int_id = ce_reglement.id_banque) as abreviation,

ce_reglement.id_banque,

ce_reglement.date_ecriture,

ce_reglement.date_remise,

ce_reglement.en_banque,

ce_reglement.id_personnel,

(select p.anonyme from ce_personnel p where p.int_id = ce_reglement.id_personnel) as adherent_anonyme,

ce_reglement.id_budget,

ce_reglement.espece,

ce_reglement.titulaire_compte,

ce_reglement.date_echeance,

ce_reglement.annee_budget,

ce_reglement.id_poste_comptable,

ce_reglement.fournisseur,

ce_reglement.finance_budget,

ce_reglement.observations,

ce_reglement.id_type_finance,

ce_reglement.remboursement,

ce_reglement.type_operation,

ce_reglement.id_journal,

ce_reglement.mode_reglement,

ce_reglement.id_banque_remise

FROM ce_reglement

WHERE ( CE_REGLEMENT.INT_ID = :arg_id_reg )

  1. Helpful
David Peace (Powersoft)
  1. David Peace (Powersoft)
  2. Tuesday, 15 December 2020 11:18 AM UTC
Hi



I'm pretty certain that the nested SQL i snot the problem, having recreated the DW and it works, can you look at what the difference is on the PB definition of column types. It might give you a clue.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 8 December 2020 21:23 PM UTC
  2. PowerBuilder
  3. # 3

Check your dbparm parameters.

Do you have to correct decimal separator specified?

Also look into this parameter "StripParmNames" set it to true?

What kind of connection are you using ODBC ?

Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 8 December 2020 21:48 PM UTC
Hi,

Have you tried using the ORA native db driver or is that not an option?
  1. Helpful
Stephane SHAN
  1. Stephane SHAN
  2. Thursday, 10 December 2020 23:15 PM UTC
Hi Miguel,

I tried to change the ODBC settings as you advised me, the error persists. The error occurs only when I pass an argument in a Select nested inside another select. But I can not find where when the execution of the query it hangs.



Finally, I give up the Oracle database. My client is OK to migrate their database to MS SQL Server.

Thanks for taking the time to help me find a solution.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 10 December 2020 23:22 PM UTC
Well, at least you have the luxury of being able to choose. So ... you did not try the native driver?

Regards
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 8 December 2020 22:19 PM UTC
  2. PowerBuilder
  3. # 4

Though the best option is to use the native oracle driver, check what's configured for the connection profile in your windows ODBC (32 bit) administrator, try changing the value of the parameter marked in yellow, see what happens changing its value.

Are you using the Oracle or the Microsoft ODBC driver for Oracle?

Comment
Stephane SHAN
  1. Stephane SHAN
  2. Tuesday, 8 December 2020 23:12 PM UTC
I will test your solution this Thursday when I have access to the system again.

Thank you for taking the time to help me.

I really appreciate it.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 8 December 2020 22:41 PM UTC
  2. PowerBuilder
  3. # 5

Another thing:

You say you develop (connected to MS Sql server) and some of those datawindows fail when running against Oracle.

"Hi René,
Thank you for your interest in my problem. I have checked data type of all fields. If I execute my query without retrieve argument, it works. The field "id_budget" is an Integer. And the argument "arg_id_budget" is a number."

 

If you export or edit the source code of that datawindow what type do you get when you look at the 

table(column....

column=(type=... updatewhereclause=.... name=id_buget dbname="...." )

?

I was thinking that maybe there could be a different value as in Oracle when using an integer in SQLServer to create a dw.

Wouldn't know anything else you could look at ...

regards

 

Comment
Stephane SHAN
  1. Stephane SHAN
  2. Tuesday, 8 December 2020 23:11 PM UTC
Miguel,



Datawindow using decimal(0) as data type for all my Integer fields. And that was not a problem with the PB7 version. It works perfectly with the same dbparam before I migate it to PB 2019. I haven't change my dbparam on prod side. I juste updated my application from PB7 to PB 2019. Nothing has changed on my prod database. Is there necessary to update ODBC driver for Oracle? Prod Application is installed on a Citrix environment.

  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 8 December 2020 23:21 PM UTC
decimal(0) should be ok. That's what I get in a new datawindow when I create a table in oracle with an integer field and am connected to oracle while creating the dw...
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 8 December 2020 23:28 PM UTC
The ODBC driver for Oracle should be ideally corresponding to the version of Oracle on your server, but I guess it might also work with lower or higher versions.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 9 December 2020 00:05 AM UTC
  2. PowerBuilder
  3. # 6

One last suggestion:

Use TRACE to see if the generated log gives any extra information on why things are failing.

set your sqlca.dbms = "TRACE ODBC" instead of "ODBC"

 

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 9 December 2020 02:51 AM UTC
  2. PowerBuilder
  3. # 7

Hi Stephane;

  All DBMS and OBDC client drivers are not the same even though they claim to be ANSI compliant.

  Personally, I would not mix & match DBMS. It's like running different tires on your car. It can lead to stability / control issues.

Regards... Chris

Comment
Stephane SHAN
  1. Stephane SHAN
  2. Thursday, 10 December 2020 23:16 PM UTC
Hi Chris,

Finally, I give up the Oracle database. My client is OK to migrate their database to MS SQL Server.

Thanks !
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Thursday, 10 December 2020 23:28 PM UTC
I personally like SQL Server much better than Oracle. We have quite a few customers migrating off of Oracle.
  1. Helpful
David Peace (Powersoft)
  1. David Peace (Powersoft)
  2. Tuesday, 15 December 2020 11:23 AM UTC
Oracle lost it's way when they changed the license model and increased our license costs by a factor of 10.

Oracle DB is very good and you can guarentee no data loss even in non clusered setups. With SQL Server you can only guarentee zero data loss with a cluster.



Having said that ALL our new development is on SQL Server.
  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.