Migration de procédure de Sybase vers PostgreSQL

Alex386 · 02/04/2009 17:00:01

Bonjour,

Je procède actuellement au remplacement d'un serveur Sybase par un serveur PostgreSQL, et j'ai un gros problème de migration de mes procédures stockées.

Ma question est simple, comment faire pour qu'une procédure stockée postgresql exécute un "select" (dont le résultat peut être récupéré par un objet PGresult avec la commande PQexec dans mon code client), et retourne aussi un entier qui me sert de code erreur pour le traitement côté client ?

exemple de procédure Sybase qui me pose problème avec postgresql:

create procedure maproc
as
begin

select * from matable;
return(1);

end

Merci d'avance

Bonne journée

gleu · 02/04/2009 20:22:57

Une procédure peut peut renvoyer plusieurs lignes (comme dans le résultat d'un SELECT) ou un entier. Mais pas les deux. Ou alors j'ai mal compris ce que vous demandiez.

diablolight · 02/04/2009 22:01:15

Je crois que la question est de retourner un jeu de résultat avec gestion d'un code retour.
Pour votre cas, ce serait quelque chose comme ça:

CREATE FUNCTION maproc ()
RETURNS integer
AS
'
BEGIN
SELECT * FROM matable;
RETURN 1;
END;
'
LANGUAGE 'plpgsql';

diablolight · 02/04/2009 23:43:47

Rectification:

En lisant la documentation, il semble qu'une fonction renvoyant un SETOF ne peut se terminer qu'avec un RETURN sans argument:

CREATE FUNCTION maproc ()
RETURNS SETOF matable
AS
'
DECLARE r matable%rowtype;
BEGIN
FOR r IN SELECT * FROM matable LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
'
LANGUAGE 'plpgsql';

Il doit probablement avoir une autre manière de faire pour résoudre le problème.

gleu · 03/04/2009 07:06:34

Quel intérêt de renvoyer un code retour vu qu'on a le résultat de la requête ? si on y tient vraiment, le seul moyen est de renvoyer le code sur chaque ligne renvoyée. En dehors de ça, point de salut.

Marc Cousin · 03/04/2009 08:40:55

Si il s'agit vraiment de traiter et d'intercepter des erreurs, avec un RAISE, on doit pouvoir faire des choses sympa aussi (et plus dans l'esprit SGBD)

Alex386 · 03/04/2009 10:32:09

diablolight a écrit :

Rectification:
En lisant la documentation, il semble qu'une fonction renvoyant un SETOF ne peut se terminer qu'avec un RETURN sans argument

C'est malheureusement le problème

gleu a écrit :

Quel intérêt de renvoyer un code retour vu qu'on a le résultat de la requête ? si on y tient vraiment, le seul moyen est de renvoyer le code sur chaque ligne renvoyée. En dehors de ça, point de salut.

L'intérêt est de s'en servir pour un IF en appelant cette procédure dans une autre, suivant le code retour (cas 1 de la procédure ci-dessous). C'est aussi pour une exploitation du code retour par l'application cliente qui affiche un message particulier à l'utilisateur suivant le code retourné (cas 2).

exemple Sybase que je voudrais reproduire:
-----------------------------------------------------
CREATE PROCEDURE COU_Get
@id int
as
begin

declare @result smallint

begin transaction

/* Si la procédure COU_ExistId retourne 1, l'id existe, sinon elle retourne 0 */
exec @result = COU_ExistId @id /* CAS 1 */

/* En cas d'absence */
if (@result=0)
begin
rollback transaction
raiserror 21001 "", "Coup"
return (-101) /* CAS 2 */
end

/* Récupération des informations associées au coup */
select
CAM_ID,
BAL_ID,
COU_REF,
COU_COND_CHARGE,
COU_COND_TEMPERATURE,
COU_COND_HAUSSE,
COU_COND_GISEMENT,
from COU
where COU_ID=@id

commit transaction

return(1)
end

-----------------------------------------------------

Le code client est du style :

switch(status) /* 'status' correspondant au code retour récupéré par un 'exec' de la procédure */

case -101 : printf('Erreur sur la table COU, ID inexistant');
break;

case -102 : printf('Erreur sur la table COU, REF inexistante');
break;
....

Merci

gleu · 03/04/2009 11:01:31

Je vois quand vous renvoyez 101, mais pas quand vous renvoyez 102.

Si vous ne renvoyez qu'un code d'erreur, le fait de récupérer ou non une ligne permet d'émuler ce code d'erreur.

diablolight · 03/04/2009 12:20:45

Il doit manquer un morceau de code (si COU_REF n'existe pas alors on renvoie -102).
Il faudrait peut-être découper la procédure en 2, d'une part la gestion COU_ID et COU_REF dans une première fonction (en incluant le COMMIT TRANSACTION puisque celui-ci n'a aucun effet sur le SELECT).
Puis une deuxième exécutant le SELECT dans un SETOF (le COU_ID et COU_REF existeront puisque le test aura déjà été effectué) avec un RETURN sans argument.
Je ne comprend pas bien pourquoi la procédure se termine par un inconditionnel return(1) ?

gleu · 03/04/2009 12:24:20

Je ne comprend pas bien pourquoi la procédure se termine par un inconditionnel return(1) ?

Le premier RETURN rencontré termine la procédure. Donc si on se trouve avec le return -101, on n'exécutera jamais le return 1.

diablolight · 03/04/2009 12:39:21

gleu a écrit :

Le premier RETURN rencontré termine la procédure. Donc si on se trouve avec le return -101, on n'exécutera jamais le return 1.

Ce que je voulais dire c'est qu'un return(1) sous Sybase est normalement synonyme d'erreur.
Il semble que dans ce cas il devrait plutôt y avoir un return(0).

jpargudo · 03/04/2009 14:41:55

bonjour,

À toutes fins utiles:
http://virginie.quesnay.free.fr/index.p … postgresql

Cela avait été repris sur PGFr il y a quelques temps:
http://blog.postgresql.fr/index.php?post/drupal/29

Cordialement,

flo · 03/04/2009 15:08:03

Si je comprends bien, la procédure COU_ExistId fait un select sur la base pour voir si l'identifiant existe.
La procédure COU_Get appelle COU_ExistId (pour savois si l'id existe), puis, si l'id existe, sélectionne le 'cou'.

Pourquoi faire 2 fois la même chose?

Tu peux déjà commencer par simplifier en supprimant la procédure COU_ExistID et en faisant directement le 2ème select. Ca sera plus simple, plus performant (sans compter que si une transaction concurrente supprime la ligne entre les 2 requêtes, tu risques des surprises)

Sinon, pour en revenir au problème principal, il est beaucoup plus logique que ce soit le client qui vérifie s'il y a des lignes renvoyées (l'absence de résultat n'est pas un problème technique, mais fonctionnel).
Apparemment tu es en train d'adapter le client de toute façon? Pourquoi est-ce un problème de modifier légèrement le client? (avec ton exemple je ne comprend pas très bien). Si tu dois vraiment afficher ou loguer le même code d'erreur que dans l'application d'origine, au pire tu peux ajouter une couche au client, qui traite la requête et crée le code et le message d'erreur en fonction de la procédure appelée...

Alex386 · 03/04/2009 20:51:24

gleu a écrit :

Je vois quand vous renvoyez 101, mais pas quand vous renvoyez 102.
Si vous ne renvoyez qu'un code d'erreur, le fait de récupérer ou non une ligne permet d'émuler ce code d'erreur.

Le code client contient tous les messages de toutes les erreurs possibles de mes 650 procédures. Le -102 n'est simplement pas traité dans cette procédure.

diablolight a écrit :

Ce que je voulais dire c'est qu'un return(1) sous Sybase est normalement synonyme d'erreur.
Il semble que dans ce cas il devrait plutôt y avoir un return(0).

Le return(1) me sert uniquement de test de bon exécution de ma procédure dans le cas où elle est appelée dans une autre procédure stockée. Savoir si je peux poursuivre les traitements ou non.

jpargudo a écrit :

bonjour,
À toutes fins utiles:
http://virginie.quesnay.free.fr/index.p … postgresql
Cela avait été repris sur PGFr il y a quelques temps:
http://blog.postgresql.fr/index.php?post/drupal/29
Cordialement,

Bonjour,

Merci pour ce site, mais apparemment les procédures stockées ne sont pas migrées

flo a écrit :

Si je comprends bien, la procédure COU_ExistId fait un select sur la base pour voir si l'identifiant existe.
La procédure COU_Get appelle COU_ExistId (pour savois si l'id existe), puis, si l'id existe, sélectionne le 'cou'.
Pourquoi faire 2 fois la même chose?
Tu peux déjà commencer par simplifier en supprimant la procédure COU_ExistID et en faisant directement le 2ème select. Ca sera plus simple, plus performant (sans compter que si une transaction concurrente supprime la ligne entre les 2 requêtes, tu risques des surprises)
Sinon, pour en revenir au problème principal, il est beaucoup plus logique que ce soit le client qui vérifie s'il y a des lignes renvoyées (l'absence de résultat n'est pas un problème technique, mais fonctionnel).
Apparemment tu es en train d'adapter le client de toute façon? Pourquoi est-ce un problème de modifier légèrement le client? (avec ton exemple je ne comprend pas très bien). Si tu dois vraiment afficher ou loguer le même code d'erreur que dans l'application d'origine, au pire tu peux ajouter une couche au client, qui traite la requête et crée le code et le message d'erreur en fonction de la procédure appelée...

J'ai volontairement écourté la procédure qui faisait 50 lignes, c'était juste pour donner un exemple de cas problématique avec un select et un return.

Le client vérifie si des lignes sont renvoyées à l'aide des alias PGRES_COMMAND_OK etc.., et procède au traitement des données ensuite. Ce que j'appelle "le client", c'est l'application d'origine

gleu · 04/04/2009 09:08:42

Je ne sais pas si c'est la nuit ou le rasage qui vient de me porter conseil mais voici une solution. L'idée est d'utiliser les options personnalisées (voir http://docs.postgresql.fr/8.3/runtime-c … ustom.html pour les détails). Voici un exemple complet :

Je crée une base pour commencer, bien que ça ne soit pas obligatoire.

guillaume@laptop$ createdb yop

J'ajoute le namespace code mais vous pouvez changer son nom. Je vais utiliser la variable/le paramètre erreur dans ce namespace. Je l'initialise à 0 pour que chaque session commence avec cette valeur.

guillaume@laptop$ echo "custom_variable_classes = 'code'" >> /opt/postgresql-8.3/data/postgresql.conf
guillaume@laptop$ echo "code.erreur = 0" >> /opt/postgresql-8.3/data/postgresql.conf

Je demande au serveur de relire la configuration.

guillaume@laptop$ pg_ctl reload
envoi d'un signal au serveur

Je fais quelques tests pour m'assurer que PostgreSQL connait bien ce paramètre:

guillaume@laptop$ psql yop     
Bienvenue dans psql 8.3.7, l'interface interactive de PostgreSQL.

Saisissez:
    \copyright pour les termes de distribution
    \h pour l'aide-mémoire des commandes SQL  
    \? pour l'aide-mémoire des commandes psql 
    \g ou point-virgule en fin d'instruction pour exécuter la requête
    \q pour quitter                                                  

yop=# show code.erreur;
 code.erreur           
-------------          
 0                     
(1 ligne)              

yop=# set code.erreur to 10;
SET                         
yop=# show code.erreur;
 code.erreur           
-------------          
 10                    
(1 ligne)

yop=# show erreur;
ERROR:  unrecognized configuration parameter "erreur"
yop=# show code;                                     
ERROR:  unrecognized configuration parameter "code"  
yop=# \q
guillaume@laptop$ psql yop                           
Bienvenue dans psql 8.3.7, l'interface interactive de PostgreSQL.

Saisissez:
    \copyright pour les termes de distribution
    \h pour l'aide-mémoire des commandes SQL  
    \? pour l'aide-mémoire des commandes psql 
    \g ou point-virgule en fin d'instruction pour exécuter la requête
    \q pour quitter                                                  

yop=# show code.erreur;
 code.erreur           
-------------          
 0                     
(1 ligne)              

yop=# \q

En gros, cela nous indique bien que la variable/le paramètre est reconnu, qu'on peut modifier sa valeur mais qu'on ne peut pas l'utiliser sans préciser le namespace. Enfin, en sortant de psql et en y re-entrant, on constate bien que la valeur/le paramètre est réinitialisé (comme tout autre paramètre).

Maintenant, je vais créer une table et une fonction pour émuler votre fonctionnement.

guillaume@laptop$ psql yop
Bienvenue dans psql 8.3.7, l'interface interactive de PostgreSQL.

Saisissez:
    \copyright pour les termes de distribution
    \h pour l'aide-mémoire des commandes SQL  
    \? pour l'aide-mémoire des commandes psql 
    \g ou point-virgule en fin d'instruction pour exécuter la requête
    \q pour quitter                                                  

yop=# create table t(id serial);
NOTICE:  CREATE TABLE will create implicit sequence "t_id_seq" for serial column "t.id"
CREATE TABLE                                                                           
yop=# insert into t select i from generate_series(1,10) as i;
INSERT 0 10                                                  
yop=# create language plpgsql;
CREATE LANGUAGE
yop=# create or replace function f(debut integer, fin integer) returns setof t language plpgsql
as $$
declare
  r record;
begin
  set code.erreur to 1;
  for r in select * from t where id between debut and fin
  loop
    return next r;
  end loop;
  return;
end
$$;
CREATE FUNCTION

Là, tout est fait. J'ai une table et une fonction. Cette dernière va récupérer des infos de la première et renvoyer les lignes en question et on utilisera code.erreur comme code retour. Testons la fonction:

yop=# show code.erreur;
 code.erreur
-------------
 0
(1 ligne)

yop=# select * from f(2,6);
 id
----
  2
  3
  4
  5
  6
(5 lignes)

yop=# show code.erreur;
 code.erreur
-------------
 1
(1 ligne)

Ça marche.

Ça va donc vous demander de modifier un peu vos fonctions, et il vous faudra deux appels à PostgreSQL (une pour exécuter la fonction, l'autre pour récupérer le code d'erreur). Mais ça peut vous éviter d'avoir à faire un gros recodage de votre application.

gleu · 04/04/2009 09:14:05

Autres informations que j'allais oublier :

1. Ce paramètre est affecté pour toute la durée de la session. Il faudra donc le rebasculer à 0 à chaque lancement de la fonction. Vous devriez pouvoir le faire automatiquement en 8.3, voir http://docs.postgresql.fr/8.3/sql-createfunction.html pour les détails.

2. Je ne crois pas que les options personnalisées soient acceptées depuis longtemps. À priori, vous aurez besoin d'une version 8.0 au plus pour en profiter.

Alex386 · 08/04/2009 17:27:44

Bonjour,

Merci pour cette réponse ! J'avais pensé à une solution de la sorte mais je ne trouvais pas comment faire.

Par contre, un problème se pose. Mon application est multi utilisateurs et fonctionne en multithreading. Il est donc possible que le code.erreur soit modifié par un second utilisateur avant d'être récupéré par le premier utilisateur.

Entre temps j'ai réfléchi à une autre solution. Créer une table stockant les erreurs, et dans mon client, exécuter la procédure, et interroger la table pour récupérer l'erreur correspondant. Mais ici même problème, le cas du multi utilisateurs...

Une idée ?

Merci !

gleu · 08/04/2009 18:00:26

Les paramètres sont modifiables par session. Donc peu importe le multithreading. À partir du moment où un utilisateur conserve sa session, il n'y aura pas de problème.

C'est très facile à tester : un terminal avec un psql, un autre terminal avec un psql. Modifier la variable sur l'un ne la modifiera pas sur l'autre.

#1 02/04/2009 17:00:01

Migration de procédure de Sybase vers PostgreSQL

#2 02/04/2009 20:22:57

Re : Migration de procédure de Sybase vers PostgreSQL

#3 02/04/2009 22:01:15

Re : Migration de procédure de Sybase vers PostgreSQL

#4 02/04/2009 23:43:47

Re : Migration de procédure de Sybase vers PostgreSQL

#5 03/04/2009 07:06:34

Re : Migration de procédure de Sybase vers PostgreSQL

#6 03/04/2009 08:40:55

Re : Migration de procédure de Sybase vers PostgreSQL

#7 03/04/2009 10:32:09

Re : Migration de procédure de Sybase vers PostgreSQL

#8 03/04/2009 11:01:31

Re : Migration de procédure de Sybase vers PostgreSQL

#9 03/04/2009 12:20:45

Re : Migration de procédure de Sybase vers PostgreSQL

#10 03/04/2009 12:24:20

Re : Migration de procédure de Sybase vers PostgreSQL

#11 03/04/2009 12:39:21

Re : Migration de procédure de Sybase vers PostgreSQL

#12 03/04/2009 14:41:55

Re : Migration de procédure de Sybase vers PostgreSQL

#13 03/04/2009 15:08:03

Re : Migration de procédure de Sybase vers PostgreSQL

#14 03/04/2009 20:51:24

Re : Migration de procédure de Sybase vers PostgreSQL

#15 04/04/2009 09:08:42

Re : Migration de procédure de Sybase vers PostgreSQL

#16 04/04/2009 09:14:05

Re : Migration de procédure de Sybase vers PostgreSQL

#17 08/04/2009 17:27:44

Re : Migration de procédure de Sybase vers PostgreSQL

#18 08/04/2009 18:00:26

Re : Migration de procédure de Sybase vers PostgreSQL

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux