Re: [Phpgroupware-developers] db insertion problems with pgsql

[Brian Johnson]

postgresql introduced this error in 7.3 since they decided to change how they
handled null values

I'm not criticizing their decision, however, phpgw decided to work the fix
into 0.9.16 instead of patching both .14 and .16 (which was already under
development at the time)


caveat: not all of the apps have been ported to .16



Chris Weiss (address@hidden) wrote:
>
> please take care that replies stay on the list...
>
> I think the "right" way is to not use '' for numbers, but use data validation
> and put 0's or nulls where they need to be.
>
> The fix will be released, in .16 which will be final very soon now, and is
> mostly stable for production.  If you are doing a new install I recomend going
> with .16RC2 with cvs update so you don't have to upgrade again in another 2
> weeks to be current.
>
> Mij (address@hidden) wrote:
> >
> > the fix won't be released, ok.
> > could you anyway post to me a quick and
> > dirty solution to that?
> > at least some approach like to stupid one
> > i used ( '' -> '0' ). If you used '' as correct
> > internal representation, this would probably
> > be a "don't care", then this wouldn't even be
> > a too dirty solution.
> >
> > thanks
> >
> >
> > Il giorno 20/gen/04, alle 15:46, Chris Weiss ha scritto:
> >
> > > another one caused by pg changing their default behaviours :/
> > >
> > > Although '' is legal in sql, its behaviour varys from db to db.
> > > Bookmarks is
> > > not a very used app so I'm not surprized that no one has found this
> > > bug until
> > > now.  It won't likely be fixed in .14, but if it's still a problem in
> > > .16 it
> > > will be, and may very well already have been.
> > >
> > > Mij (address@hidden) wrote:
> > >>
> > >> hi
> > >>
> > >> i will report a twofold problem here with
> > >> phpgw+bookmark app (phpgwapi).
> > >>
> > >> i'm not experienced on php, so be soft with it.
> > >> I get this error from phpgw-0.9.14.007, from
> > >> freebsd ports, on a freebsd 4.9 machine
> > >> with pgsql 7.4.1.
> > >>
> > >> that's the output when trying to *reach the "bookmarks"
> > >> page*:
> > >>
> > >>
> > >> ------
> > >> Database error: Invalid SQL: INSERT INTO phpgw_categories (
> > >> cat_parent,cat_owner,cat_access,cat_appname,cat_name,cat_description,c
> > >> at
> > >> _data,cat_main,cat_level) VALUES ( '0','4','','bookmarks','No
> > >> category','','','','')
> > >> PostgreSQL Error: 1 (ERROR: invalid input syntax for integer: "" )
> > >>
> > >> File:
> > >> /usr/local/www/data-dist/phpgroupware/phpgwapi/inc/
> > >> class.categories.inc.php
> > >> Line: 515
> > >>
> > >> Session halted.Template Error: loadfile: body is not a valid handle.
> > >> Halted.Template Error: loadfile: body is not a valid handle.
> > >> Halted.Template Error: loadfile: body is not a valid handle.
> > >> [...]
> > >> -------
> > >>
> > >> then php killed for memory slot exhaustion.
> > >> The incrimiated line:
> > >>
> > >> $this->db->query("INSERT INTO phpgw_categories ($id_col
> > >> cat_parent,cat_owner,cat_access,cat_appname,cat_name,cat_description,c
> > >> at
> > >> _data,"
> > >>                                  . "cat_main,cat_level) VALUES
> > >> ($id_val
> > >> '" . $cat_values['parent'] . "','" . $this->account_id . "','" .
> > >> $cat_values['acc
> > >> ess']
> > >>                                  . "','" . $this->app_name . "','" .
> > >> $cat_values['name'] . "','" . $cat_values['descr'] . "','" .
> > >> $cat_values['data']
> > >>                                  . "','" . $cat_values['main'] .
> > >> "','" .
> > >> $cat_values['level'] . "')",__LINE__,__FILE__);
> > >>
> > >>
> > >> but I think the php output is more interesting.
> > >>
> > >> I don't know your internal representation. Maybe it's valid for you,
> > >> but the thing anyway is that $cat_values['access'] seems not to be set
> > >> to a valid value. This causes a type error by the dbms.
> > >>
> > >> however, I tried to manually insert a line of that kind, replacing the
> > >> invalid '' with nulls.
> > >> The table does not permit any null. I tried with zeroes then, in place
> > >> of ''. It works this way.
> > >> I can reach the page, and push new bookmarks with the form.
> > >>
> > >>
> > >> even with this, anyway, there's a second problem. I get errors even on
> > >> the preferences page,
> > >> when trying to create new categories. The error is more polite this
> > >> time:
> > >>
> > >> -------------
> > >> Database error: Invalid SQL: INSERT INTO phpgw_categories (
> > >> cat_parent,cat_owner,cat_access,cat_appname,cat_name,cat_description,c
> > >> at
> > >> _data,cat_main,cat_level) VALUES (
> > >> '7','4','public','bookmarks','foo','bar','N;','','1')
> > >> PostgreSQL Error: 1 (ERROR: invalid input syntax for integer: "" )
> > >>
> > >> File:
> > >> /usr/local/www/data-dist/phpgroupware/phpgwapi/inc/
> > >> class.categories.inc.php
> > >> Line: 515
> > >>
> > >> Session halted.
> > >> -------------
> > >>
> > >> Same problem. Same solution: manually insert replacing '' with '0'.
> > >> The entry inserted is listed, but I still can't create new entries
> > >> from
> > >> the web interface.
> > >>
> > >
> > >> as said, I don't know php. Unfortunately, i don't even have time to
> > >> spend on this
> > >> problem right at the moment, and at the same time i'd also need this
> > >> interface working.
> > >> any collaboration will be appreciated.
> > >>
> > >> bye
> > >>
> > >>
> > >>
> > >> _______________________________________________
> > >> Phpgroupware-developers mailing list
> > >> address@hidden
> > >> http://mail.gnu.org/mailman/listinfo/phpgroupware-developers
> > >>
> > >
> > >
> > >
> > > _______________________________________________
> > > Phpgroupware-developers mailing list
> > > address@hidden
> > > http://mail.gnu.org/mailman/listinfo/phpgroupware-developers
> > >
> >
> >
>
>
>
> _______________________________________________
> Phpgroupware-developers mailing list
> address@hidden
> http://mail.gnu.org/mailman/listinfo/phpgroupware-developers
>