From owner-freebsd-questions@FreeBSD.ORG Fri Jan 26 17:12:07 2007 Return-Path: X-Original-To: freebsd-questions@freebsd.org Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id A69F916A407 for ; Fri, 26 Jan 2007 17:12:07 +0000 (UTC) (envelope-from philippe.lang@attiksystem.ch) Received: from mail.attiksystem.ch (f29.attiksystem.ch [212.147.59.29]) by mx1.freebsd.org (Postfix) with ESMTP id 07F3D13C4B3 for ; Fri, 26 Jan 2007 17:12:06 +0000 (UTC) (envelope-from philippe.lang@attiksystem.ch) Received: from poweredge.attiksystem.ch (poweredge.attiksystem.ch [10.0.0.29]) by mail.attiksystem.ch (8.12.11/8.12.11) with ESMTP id l0QHC5TJ039362 for ; Fri, 26 Jan 2007 18:12:05 +0100 (CET) (envelope-from philippe.lang@attiksystem.ch) content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 Date: Fri, 26 Jan 2007 18:12:05 +0100 Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CD4D@poweredge.attiksystem.ch> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Postgresql 8.1: plperl code works with LATIN1, fails with UTF8 Thread-Index: AcdBbR1BwcZkxvAAS/Wp3OaL2bd6QA== From: "Philippe Lang" To: X-Spam-Score: -1.44 () ALL_TRUSTED X-Scanned-By: MIMEDefang 2.49 on 10.0.0.111 Subject: Postgresql 8.1: plperl code works with LATIN1, fails with UTF8 X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 26 Jan 2007 17:12:07 -0000 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