webobjects-dev@wocommunity.org Mailing List Archive

Hello Jérémy,

I don’t have the answer you expect, but I’m pretty sure it’s not a good idea to let the database “round” your values, because each database (or maybe even versions) could have different way of rounding things.

You should set the scale/precision yourself inside your code and save exactly what you expect in the database.

Could you add a cover method for rounding in your code so every number would get through this method before being save and so getting the expected rounding mechanism?

Xavier

On 22 Aug 2023, at 13:38, Jérémy DE ROYER <jeremy.deroyer@ingencys.net> wrote:

Hi all,

I’m currently switching from FrontbaseSQL to PostgreSQL for one of our application which works perfectly with FrontbaseSQL (but our customer required it).

In one table , I have a BigDecimal (Java) field named « soldeTtc » wich is converted into Numeric(12,4) (PostgreSQL) or decimal(12,4) (FrontbaseSQL).

When saving my context to record an order, I’m facing the error below :

[2023-8-22 13:5:37 CEST] <WorkerThread3> evaluateExpression: <com.webobjects.jdbcadaptor.JDBCExpression: "INSERT INTO IDX_WEBCO_COMMANDE_CLIENT(DATE_SOLDE, CUSTOMED_FACTURATION_PRENOM, CODE_STRUCTURE, MONTANT_TTC_PORT, CUSTOMED_LIVRAISON_CIVILITE, POINTS_AJOUTES_PARRAIN, DATE_EXPEDITION_PREVISIONNELLE_ACTUALISEE, CODE_STATUT_PAIEMENT, DATE_STATUT_PAIEMENT, EN_COURS_FACTURE_TTC, ID_PARTITION, DATE_ANNULATION, SOLDE_TTC, CUSTOMED_FACTURATION_CODE_PAYS, EST_ENLEVEMENT, CUSTOMED_FACTURATION_VILLE, CODE_TRI_SERVICE_ARRIVEE, CUSTOMED_LIVRAISON_CODE_POSTAL, AVEC_SUIVI, ID, NIVEAU_PRIORITE, EST_CLIENT_PASSAGE, CUSTOMED_FACTURATION_ADRESSE_1, CUSTOMED_FACTURATION_ADRESSE_2, MONTANT_HT, TITRE_UNITE_OPERATIONNELLE, ORDRE_COMMANDE_CLIENT_VALIDE, CUSTOMED_LIVRAISON_MOBILE, EXISTE_ACHAT_NON_ARTICLE, MONTANT_FACTURE_FOU_HT, CODE_PRODUIT_POINT_RELAIS, ETAT_PREPARATION, CONTIENT_BILLET, EST_COMMANDE_APRES_SAUVEGARDE_PANIER, MONTANT_HT_AUTRES_TAXES, DATE_RELANCE_3, REFERENCE_AFFILIE, REPORTED_COMMERCIAL_DURATION, CUSTOMED_LIVRAISON_CODE_POINT_RELAIS, CODE_PROMOTION, TITRE_DIRECTION_REGIONALE, TITRE_VALIDEUR_CENTRE_COUT, EN_COURS_FACTURE_FOU_TTC, DATE_DERNIERE_FACTURE_FOU, CONTIENT_BON_CADEAU, DATE_ECHEANCE_PAIEMENT, DEVISE, DATE_COMMANDE, TERRITORIALITE, reference, DATE_CONFIRMATION_FACTURATION, DATE_CONFIRMATION_LIVRAISON, POIDS_TRANSPORT_KG, ORDRE_COMMANDE_CLIENT_MONTANT_SUPERIEUR_ZERO, CODE_CENTRE_COUT, CODE_ERREUR_PAIEMENT, DATE_ERREUR_PAIEMENT, CUSTOMED_FACTURATION_ENTREPRISE, EXISTE_ACHAT_ARTICLE, EST_POINT_RELAIS, CUSTOMED_FACTURATION_MOBILE, CUSTOMED_FACTURATION_EMAIL, SOLDE_TTC_BONS_CADEAUX, DATE_CONFIRMATION_EXPEDITION, CUSTOMED_LIVRAISON_INSTRUCTIONS, CUSTOMED_LIVRAISON_NATURE_ADRESSE, MONTANT_FACTURE_HT, ID_MODE_TRANSPORT, POINTS_CONSOMMES_PARRAIN, DATE_DERNIERE_FACTURE_CLI, MODE_PAIEMENT, REPORTED_PRODUCTION_DURATION, SOLDE_TTC_BONS_CADEAUX_SANS_UTILISATION, MONTANT_TTC_REMISE_COMMERCIALE, NATURE_PAIEMENT, DATE_CONFIRMATION_MISE_EN_PRODUCTION, RETARD_EXPEDITION_ACTUALISE, MONTANT_TTC, NUMERO_TVA_INTRACOM, EN_COURS_FACTURE_CLI_TTC, CODE_ACTION, LISTE_EMAIL_NOTIFICATION_CLIENT_PASSAGE, TYPE_PAIEMENT, CODE_COMMANDE, JAVA_CLASS_NAME_EXTENSION_MODE_TRANSPORT, TITRE_ERREUR_PAIEMENT, MONTANT_HT_PORT, CODE_CONFIRMATION_PAIEMENT, PLANNED_PRODUCTION_QUANTITY, CUSTOMED_FACTURATION_NOM, DATE_EXPEDITION_PREVISIONNELLE, DATE_CONFIRMATION_PREPARATION, CODE_POINT_RELAIS, NUMERO_IDENTIFICATION_PERSONNEL, DATE_RELANCE_1, DATE_RELANCE_2, CUSTOMED_LIVRAISON_CODE_PAYS, EST_SOLDEE, PRIX_AVEC_TVA, CUSTOMED_LIVRAISON_ENTREPRISE, PLANNED_PRODUCTION_DURATION, MONTANT_TVA, ADRESSE_IP_CLIENT, CUSTOMED_LIVRAISON_ADRESSE_1, CUSTOMED_LIVRAISON_ADRESSE_2, ID_AUTEUR_DERNIERE_MODIFICATION, ORDRE_COMMANDE_CLIENT_VALIDE_MONTANT_ZERO, CUSTOMED_LIVRAISON_CODE_PRODUIT_POINT_RELAIS, POINTS_AJOUTES_FILLEUL, DATE_CREATION, ORDRE_COMMANDE_CLIENT_MONTANT_ZERO, DESACTIVER_AVIS_CLIENTS, CUSTOMED_LIVRAISON_VILLE, NUMBER_OF_DECIMAL_DIGITS, NOMBRE_TOTAL_COLIS_CONFIRMATION_PREPARATION, DATE_EXPEDITION_SOUHAITEE, MONTANT_ACHAT_HT, POIDS_TOTAL_KG, DATE_EXPEDITION_CALCULEE, MONTANT_FACTURE_CLI_HT, LISTE_DATE_ENVOI_EMAIL_PAIEMENT, CODE_PRODUIT_MODE_TRANSPORT, CUSTOMED_MESSAGE_CLIENT, CUSTOMED_FACTURATION_CIVILITE, DELAI_EXPEDITION_CALCULE, ID_CLIENT, DATE_VALIDATION, CUSTOMED_FACTURATION_TELEPHONE, CUSTOMED_LIVRAISON_PRENOM, CUSTOMED_FACTURATION_NATURE_ADRESSE, DATE_CONFIRMATION_PAIEMENT, MONTANT_HT_REMISE_COMMERCIALE, INITIALES_CONFIRMATION_PREPARATION, TITRE, TITRE_DIRECTION, CUSTOMED_FACTURATION_CODE_POSTAL, ID_ACTEUR, DATE_DERNIERE_MODIFICATION, LISTE_EMAIL_NOTIFICATION_FACTURATION_CLIENT_PASSAGE, MONTANT_HT_DON, ORDRE_COMMANDE_CLIENT, MONTANT_TTC_DON, CODE_CENTRE_DE_GESTION, REPORTED_PRODUCTION_QUANTITY, CUSTOMED_LIVRAISON_TELEPHONE, POIDS_TOTAL_COLIS_KG_CONFIRMATION_PREPARATION, REPORTED_SUPPORT_DURATION, CUSTOMED_LIVRAISON_NOM, TITRE_STATUT_PAIEMENT, STATUT, REFERENCE_PROCESS, CUSTOMED_LIVRAISON_CODE_PORTE, CODE_PARRAINAGE, PERCENTAGE_OF_ELAPSED_PRODUCTION_DURATION, CUSTOMED_LIVRAISON_EMAIL, ID_AUTEUR, ORDRE_COMMANDE_CLIENT_VALIDE_MONTANT_SUPERIEUR_ZERO) VALUES (NULL, NULL, NULL, ?, NULL, NULL, NULL, NULL, NULL, NULL, ?, NULL, ?, NULL, ?, NULL, NULL, NULL, ?, ?, NULL, NULL, NULL, NULL, ?, NULL, NULL, NULL, ?, NULL, NULL, NULL, NULL, ?, ?, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ?, ?, ?, ?, NULL, NULL, ?, NULL, ?, NULL, NULL, NULL, ?, ?, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ?, NULL, NULL, ?, NULL, NULL, ?, NULL, NULL, NULL, ?, ?, NULL, NULL, NULL, ?, NULL, NULL, NULL, ?, ?, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ?, ?, NULL, NULL, ?, ?, NULL, NULL, ?, NULL, NULL, NULL, ?, NULL, NULL, NULL, ?, NULL, NULL, ?, ?, ?, NULL, NULL, NULL, NULL, NULL, NULL, ?, NULL, NULL, NULL, NULL, NULL, ?, NULL, NULL, NULL, NULL, ?, ?, NULL, ?, NULL, ?, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ?, NULL)" withBindings: 1:0(montantTtcPort), 2:1000384(idPartition), 3:15.050000000000000710542735760100185871124267578125(soldeTtc), 4:false(estEnlevement), 5:false(avecSuivi), 6:1001430(id), 7:15.050000000000000710542735760100185871124267578125(montantHt), 8:false(existeAchatNonBien), 9:false(estCommandeApresSauvegardePanier), 10:0(montantHtAutresTaxes), 11:"EUR"(devise), 12:2023-08-22 13:05:36(dateCommande), 13:"Export"(territorialite), 14:"78371e17-e7e2-48d3-9228-4e01c9888345"(reference), 15:0(poidsTransportKg), 16:"Cout2020"(codeCentreCout), 17:true(existeAchatBien), 18:false(estPointRelais), 19:1000007(idModeTransport), 20:2(modePaiement), 21:0(montantTtcRemiseCommerciale), 22:15.050000000000000710542735760100185871124267578125(montantTtc), 23:"FR53433321841"(numeroTvaIntracommunautaire), 24:1(typePaiement), 25:0(montantHtPort), 26:18(codeConfirmationPaiement), 27:false(estSoldee), 28:false(prixAvecTva), 29:0(montantTva), 30:"127.0.0.1"(adresseIpClient), 31:1000002(idAuteurDerniereModification), 32:2023-08-22 13:05:36(dateCreation), 33:2(numberOfDecimalDigits), 34:10(montantAchatHt), 35:0(poidsTotalKg), 36:2019-05-31 00:00:00(dateExpeditionCalculee), 37:1000163(idClient), 38:0(montantHtRemiseCommerciale), 39:1000002(idActeur), 40:2023-08-22 13:05:36(dateDerniereModification), 41:0(montantHtDon), 42:0(montantTtcDon), 43:1000002(idAuteur)>
[2023-8-22 13:5:37 CEST] <WorkerThread3> === Rollback Internal Transaction

com.webobjects.eoaccess.EOGeneralAdaptorException: The attribute soldeTtc has a scale of 4, but the value 15.050000000000000710542735760100185871124267578125 has a scale of 48: Rounding necessary
at com.webobjects.eoaccess.EODatabaseContext._exceptionWithDatabaseContextInformationAdded(EODatabaseContext.java:4504)
at com.webobjects.eoaccess.EODatabaseContext.performChanges(EODatabaseContext.java:6216)
at com.webobjects.eocontrol.EOObjectStoreCoordinator.saveChangesInEditingContext(EOObjectStoreCoordinator.java:376)
at com.webobjects.eocontrol.EOEditingContext.saveChanges(EOEditingContext.java:3192)

When playing the SQL below directly in pgAdmin, it works great and the value is automatically rounding like in FrontbaseSQL.

INSERT INTO IDX_WEBCO_COMMANDE_CLIENT(ID, DATE_SOLDE, SOLDE_TTC) VALUES (1, NULL, 15.050000000000000710542735760100185871124267578125)

Acording to the archive (https://lists.apple.com/archives/webobjects-dev/2005/Mar/msg00022.html), I looked for another datatype (https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL) but numeric is the only one choice where we can set Precision and Scale (with pgAdmin) and the one I found in the JDBCInfo.plist of the last PostgresqlPlugIn.framework

Any idea how to solve the problem ?

Many thanks,

Jérémy