Date: Fri, 26 Jan 2007 18:12:05 +0100 From: "Philippe Lang" <philippe.lang@attiksystem.ch> To: <freebsd-questions@freebsd.org> Subject: Postgresql 8.1: plperl code works with LATIN1, fails with UTF8 Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CD4D@poweredge.attiksystem.ch>
next in thread | raw e-mail | index | archive | help
Hi,
I've got plperl code that works just fine when the database is encoded =
using LATIN1, but fails as soon as I switch to UTF8.
I've been testing PG 8.1.4 under Linux, and PG 8.1.6 under FreeBSD, both =
behave exactly the save.
I'm sorry I'm not able to strip down the code, and show you a small =
test, but if anyone need the full script, feel free to ask me per email.
The code is made up of plperl routines, all structured in the same way, =
but only one of them fails in UTF8. It is:
#------------------------------------------------------------------------=
----
CREATE OR REPLACE FUNCTION public.volets_fiche_fab_1
(
IN id_commande int4,
OUT pos int4,
OUT quant int4,
OUT nbre_vtx int4,
OUT nbre_vtx_total int4,
OUT larg_maconnerie int4,
OUT haut_maconnerie int4,
OUT larg_vtx varchar(20),
OUT haut_vtx int4,
OUT ouv int4,
OUT couvre_joints text,
OUT coupe_verticale text,
OUT vide_interieur varchar(20),
OUT typ varchar(20)
)
RETURNS SETOF record
AS
$$
BEGIN { strict->import(); }
=
#------------------------------------------------------------------------=
----
#-- Lexical variables
=
#------------------------------------------------------------------------=
----
my @i;
my @io;
my @o;
my $i;
my $io;
my $o;
my %input;
my %output;
my $fab;
my $fab_nrows;
my $lignes_query;
my $lignes;
my $lignes_nrows;
my $lignes_rn;
my $c;
my $j;
my $key;
my $value;
my $ordre;
my $vtxg;
my $vtxd;
=
#------------------------------------------------------------------------=
----
#-- Helper functions
=
#------------------------------------------------------------------------=
----
my $init =3D sub
{
$c =3D 0;
foreach $i (@i) {$input{$i} =3D @_[$c++]};
foreach $io (@io) {$input{$io} =3D @_[$c]; $output{$io} =3D =
@_[$c++]};
foreach $o (@o) {$output{$o} =3D @_[$c++]};
};
my $start_sub =3D sub
{
&$init(@_);
};
my $end_sub =3D sub
{
return undef;
};
my $ret =3D sub
{
while (($key, $value) =3D each %output) {if (!defined($value)) =
{elog(ERROR, 'Valeur ind=E9finie pour ' . $key)}};=20
return_next \%output;
&$init(@_);
};
=
#------------------------------------------------------------------------=
----
#-- Configuration des param=E8tres de la fonction
=
#------------------------------------------------------------------------=
----
@i =3D ( 'id_commande'
);
=20
@io =3D ();
=20
@o =3D ( 'pos',
'quant',=20
'nbre_vtx',
'nbre_vtx_total',
'larg_maconnerie',=20
'haut_maconnerie',
'larg_vtx',=20
'haut_vtx',
'ouv',
'couvre_joints',
'coupe_verticale',
'vide_interieur',
'typ'
);
=
#------------------------------------------------------------------------=
----
#-- Pr=E9paration des param=E8tres de la fonction
=
#------------------------------------------------------------------------=
----
&$start_sub(@_);
=
#------------------------------------------------------------------------=
----
#-- Cr=E9ation de la fiche de fabrication
=
#------------------------------------------------------------------------=
----
$lignes_query =3D 'SELECT * FROM lignes WHERE id_commande =3D ' . =
$input{'id_commande'} . ' ORDER BY pos;';
$lignes =3D spi_exec_query($lignes_query);
$lignes_nrows =3D $lignes->{processed};
foreach $lignes_rn (0 .. $lignes_nrows - 1)=20
{
# Fabrication de la ligne
$fab =3D spi_exec_query('SELECT * FROM volets_fab(' . =
$lignes->{rows}[$lignes_rn]->{'id'} . ');');
$fab_nrows =3D $fab->{processed};
# Recherches des =E9ventuels vantaux de gauche et droite
for ($j =3D 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail =
gauche') and ($j < $fab_nrows); $j =3D $j + 1) {};
if ($j < $fab_nrows) { $vtxg =3D $fab->{rows}[$j]->{'larg'}; }
for ($j =3D 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail =
droite') and ($j < $fab_nrows); $j =3D $j + 1) {};
if ($j < $fab_nrows) { $vtxd =3D $fab->{rows}[$j]->{'larg'}; }
# Position
$output{'pos'} =3D $lignes->{rows}[$lignes_rn]->{'pos'};
# Quantit=E9
$output{'quant'} =3D $lignes->{rows}[$lignes_rn]->{'quant'};
# Nombre de vantaux
$output{'nbre_vtx'} =3D $lignes->{rows}[$lignes_rn]->{'nbre_vtx'};
=20
# Nombre de vantaux total
$output{'nbre_vtx_total'} =3D $lignes->{rows}[$lignes_rn]->{'nbre_vtx'} =
* $lignes->{rows}[$lignes_rn]->{'quant'};
# Largeur de ma=E7onnerie
for ($j =3D 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de =
ma=E7onnerie') and ($j < $fab_nrows); $j =3D $j + 1) {};
if ($j < $fab_nrows) { $output{'larg_maconnerie'} =3D =
$fab->{rows}[$j]->{'larg'}; }
else { $output{'larg_maconnerie'} =3D ''; };
# Hauteur de ma=E7onnerie
for ($j =3D 0; ($fab->{rows}[$j]->{'article'} ne 'Hauteur de =
ma=E7onnerie') and ($j < $fab_nrows); $j =3D $j + 1) {};
if ($j < $fab_nrows) { $output{'haut_maconnerie'} =3D =
$fab->{rows}[$j]->{'haut'}; }
else { $output{'haut_maconnerie'} =3D ''; };
=20
# Largeur de vantail
if (defined($vtxg) and defined($vtxd))
{
# Vantaux asym=E9triques
$output{'larg_vtx'} =3D $vtxg . " / " . $vtxd;
}
else
{
# Vantaux sym=E9triques
for ($j =3D 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de =
vantail') and ($j < $fab_nrows); $j =3D $j + 1) {};
if ($j < $fab_nrows) { $output{'larg_vtx'} =3D =
$fab->{rows}[$j]->{'larg'}; }
else { $output{'larg_vtx'} =3D ''; };
}
# Hauteur de vantail
for ($j =3D 0; ($fab->{rows}[$j]->{'article'} ne 'Hauteur de =
vantail') and ($j < $fab_nrows); $j =3D $j + 1) {};
if ($j < $fab_nrows) { $output{'haut_vtx'} =3D =
$fab->{rows}[$j]->{'haut'}; }
else { $output{'haut_vtx'} =3D ''; };
=20
# Type d'ouverture
$output{'ouv'} =3D $lignes->{rows}[$lignes_rn]->{'ouv'};
# Image des couvre-joints
for ($j =3D 0; ($fab->{rows}[$j]->{'article'} ne 'Couvre-joints') and =
($j < $fab_nrows); $j =3D $j + 1) {};
if ($j < $fab_nrows) { $output{'couvre_joints'} =3D =
$fab->{rows}[$j]->{'image'}; }
else { $output{'couvre_joints'} =3D ''; };
# Image de la coupe verticape
for ($j =3D 0; ($fab->{rows}[$j]->{'article'} ne 'Coupe verticale') and =
($j < $fab_nrows); $j =3D $j + 1) {};
if ($j < $fab_nrows) { $output{'coupe_verticale'} =3D =
$fab->{rows}[$j]->{'image'}; }
else { $output{'coupe_verticale'} =3D ''; };
# Vide int=E9rieur
if (defined($vtxg) and defined($vtxd))
{
# Vantaux asym=E9triques
$output{'vide_interieur'} =3D ($vtxg - 106) . " / " . ($vtxd - =
106);
}
else
{
# Vantaux sym=E9triques
for ($j =3D 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de =
vantail') and ($j < $fab_nrows); $j =3D $j + 1) {};
if ($j < $fab_nrows) { $output{'vide_interieur'} =3D =
$fab->{rows}[$j]->{'larg'} - 106; }
else { $output{'vide_interieur'} =3D ''; };
}
# Type de volet
$output{'typ'} =3D $lignes->{rows}[$lignes_rn]->{'typ'};
=09
# Sortie
&$ret(@_);
}
=
#------------------------------------------------------------------------=
----
#-- Fin de la fonction
=
#------------------------------------------------------------------------=
----
&$end_sub(@_);
$$
=20
LANGUAGE 'plperl' VOLATILE;
#------------------------------------------------------------------------=
----
When running:
-------------
select * from volets_fiche_fab_1(1)
Database replies:
-----------------
ERROR: error from Perl function: invalid input syntax for integer: "" =
at line 54.
SQL state: XX000
Does anyone have a small idea where to search?
Thanks
Philippe Lang
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?6C0CF58A187DA5479245E0830AF84F4218CD4D>