Calendar trunk@29403 error with PostgreSQL query

Anthony_Messina 2010-03-05 23:11:51 UTC #1

Using the trunk@29403, I get the following error after accessing the calendar.
I use PostgreSQL. There were no errors during the update.

How might I go about fixing this?

The error occurs due to:
egw_api_content_history.sync_contentid=egw_cal.cal_id

WARNING: nonstandard use of \ in a string literal at character 88
HINT: Use the escape string syntax for backslashes, e.g., E’\’.
WARNING: nonstandard use of \ in a string literal at character 88
HINT: Use the escape string syntax for backslashes, e.g., E’\’.
WARNING: nonstandard use of \ in a string literal at character 88
HINT: Use the escape string syntax for backslashes, e.g., E’\’.
ERROR: operator does not exist: character varying = integer at character 423
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
STATEMENT: (SELECT
egw_cal_repeats.,egw_cal.,cal_start,cal_end,cal_recur_date,NULL AS
participants,NULL AS icons FROM egw_cal JOIN egw_cal_dates ON
egw_cal.cal_id=egw_cal_dates.cal_id JOIN egw_cal_user ON
egw_cal.cal_id=egw_cal_user.cal_id LEFT JOIN egw_cal_repeats ON
egw_cal.cal_id=egw_cal_repeats.cal_id JOIN egw_api_content_history ON
egw_api_content_history.sync_appname = ‘calendar’ AND
egw_api_content_history.sync_contentid=egw_cal.cal_id WHERE (cal_user_type=‘u’
AND cal_user_id IN (‘3’,’-2’,’-1’,’-25’,’-27’,’-6’,’-16’)) AND sync_deleted IS
NULL AND 1267336800 < cal_end AND cal_start < 1267941599 AND recur_type IS
NULL AND cal_recur_date=0)
UNION
(SELECT
egw_cal_repeats.,egw_cal.,cal_start,cal_end,cal_recur_date,NULL AS
participants,NULL AS icons FROM egw_cal JOIN egw_cal_dates ON
egw_cal.cal_id=egw_cal_dates.cal_id JOIN egw_cal_user ON
egw_cal.cal_id=egw_cal_user.cal_id LEFT JOIN egw_cal_repeats ON
egw_cal.cal_id=egw_cal_repeats.cal_id JOIN egw_api_content_history ON
egw_api_content_history.sync_appname = ‘calendar’ AND
egw_api_content_history.sync_contentid=egw_cal.cal_id WHERE (cal_user_type=‘u’
AND cal_user_id IN (‘3’,’-2’,’-1’,’-25’,’-27’,’-6’,’-16’)) AND sync_deleted IS
NULL AND 1267336800 < cal_end AND cal_start < 1267941599 AND
cal_recur_date=cal_start)
ORDER BY cal_start

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

ngegw 2010-03-06 02:41:37 UTC #2

Using the trunk@29403, I get the following error after accessing the calendar.
I use PostgreSQL. There were no errors during the update.

How might I go about fixing this?

The error occurs due to:
egw_api_content_history.sync_contentid=egw_cal.cal_id

That’s mine. Please try 29404, it should work better.
Nathan

Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

Anthony_Messina 2010-03-06 04:32:53 UTC #3

Using the trunk@29403, I get the following error after accessing the
calendar. I use PostgreSQL. There were no errors during the update.

How might I go about fixing this?

The error occurs due to:
egw_api_content_history.sync_contentid=egw_cal.cal_id

That’s mine. Please try 29404, it should work better.
Nathan

Thanks, but there is no UNSIGNED type in PostgreSQL. Googling tells me that
most substitute “bigint” instead since but the values in
egw_api_content_history.sync_contentid are stored as varchar(60) and the
values in egw_cal.cal_id are stored as integer.

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

Anthony_Messina 2010-03-06 04:39:07 UTC #4

Using the trunk@29403, I get the following error after accessing the
calendar. I use PostgreSQL. There were no errors during the update.

How might I go about fixing this?

The error occurs due to:
egw_api_content_history.sync_contentid=egw_cal.cal_id

That’s mine. Please try 29404, it should work better.
Nathan

Thanks, but there is no UNSIGNED type in PostgreSQL. Googling tells me
that most substitute “bigint” instead since but the values in
egw_api_content_history.sync_contentid are stored as varchar(60) and the
values in egw_cal.cal_id are stored as integer.

The following works for me in PostgreSQL:

egw_api_content_history.sync_contentid::bigint=$this->cal_table.cal_id

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

Ralf_Becker_Stylite 2010-03-06 07:19:53 UTC #5

Afaik MySQL does not support ::type Syntax, what about a standard cast:

CAST(egw_api_content_history.sync_contentid AS
BIGINT)=$this->cal_table.cal_id

Ralf

Anthony Messina schrieb:

Using the trunk@29403, I get the following error after accessing the
calendar. I use PostgreSQL. There were no errors during the update.

How might I go about fixing this?

The error occurs due to:
egw_api_content_history.sync_contentid=egw_cal.cal_id

That’s mine. Please try 29404, it should work better.
Nathan
Thanks, but there is no UNSIGNED type in PostgreSQL. Googling tells me
that most substitute “bigint” instead since but the values in
egw_api_content_history.sync_contentid are stored as varchar(60) and the
values in egw_cal.cal_id are stored as integer.

The following works for me in PostgreSQL:

egw_api_content_history.sync_contentid::bigint=$this->cal_table.cal_id

Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

–
Ralf Becker
Director Software Development

Stylite GmbH
[open style of IT]

Morschheimer Strasse 15
67292 Kirchheimbolanden

fon +49 (0) 6352 70629-0
fax +49 (0) 6352 70629-30
mailto: rb@stylite.de

www.stylite.de
www.egroupware.org

Geschäftsführer Andre Keller,
Gudrun Müller, Ralf Becker
Registergericht Kaiserslautern HRB 30575
Umsatzsteuer-Id / VAT-Id: DE214280951

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

Ralf_Becker_Stylite 2010-03-06 07:37:58 UTC #6

Sorry, MySQL does NOT allow bigint here

That code fragment is from projectmanager:

$pm_id = “$this->table_name.pm_id”;
// postgres 8.3 requires cast as link_idx is varchar and pm_id an integer
if ($this->db->Type == ‘postgres’) $pm_id = “CAST($pm_id AS VARCHAR)”;
$join .= " JOIN $this->links_table ON link_app2=‘projectmanager’ AND
link_app1=‘projectmanager’ AND link_id2=$pm_id";

The only cast I have found working in both MySQL and Postgres is:

select cast(1 as char);

I tried:

varchar (only postgres)
integer or bigint (only Postgres)
unsigned integer or unsigned (only MySQL)

So I’d say, do the same as PM and make the cast only for Postgres

Ralf

Ralf Becker schrieb:

Afaik MySQL does not support ::type Syntax, what about a standard cast:

CAST(egw_api_content_history.sync_contentid AS
BIGINT)=$this->cal_table.cal_id

Ralf

Anthony Messina schrieb:

Using the trunk@29403, I get the following error after accessing the
calendar. I use PostgreSQL. There were no errors during the update.

How might I go about fixing this?

The error occurs due to:
egw_api_content_history.sync_contentid=egw_cal.cal_id

That’s mine. Please try 29404, it should work better.
Nathan
Thanks, but there is no UNSIGNED type in PostgreSQL. Googling tells me
that most substitute “bigint” instead since but the values in
egw_api_content_history.sync_contentid are stored as varchar(60) and the
values in egw_cal.cal_id are stored as integer.
The following works for me in PostgreSQL:

egw_api_content_history.sync_contentid::bigint=$this->cal_table.cal_id

Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

–
Ralf Becker
Director Software Development

Stylite GmbH
[open style of IT]

Morschheimer Strasse 15
67292 Kirchheimbolanden

fon +49 (0) 6352 70629-0
fax +49 (0) 6352 70629-30
mailto: rb@stylite.de

www.stylite.de
www.egroupware.org

Geschäftsführer Andre Keller,
Gudrun Müller, Ralf Becker
Registergericht Kaiserslautern HRB 30575
Umsatzsteuer-Id / VAT-Id: DE214280951

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

ngegw 2010-03-06 15:53:08 UTC #7

Sorry, MySQL does NOT allow bigint here
…
So I’d say, do the same as PM and make the cast only for Postgres

Thanks Ralf, I would have been at that for hours.
Does r29412 work for you Anthony?

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

Anthony_Messina 2010-03-07 21:47:34 UTC #8

Thanks Ralf, I would have been at that for hours.
Does r29412 work for you Anthony?

That doesn’t work, but this does:

diff --git a/calendar/inc/class.calendar_so.inc.php
b/calendar/inc/class.calendar_so.inc.php
index ac4a215…6159b08 100644
— a/calendar/inc/class.calendar_so.inc.php
+++ b/calendar/inc/class.calendar_so.inc.php
@@ -423,7 +423,7 @@ class calendar_so
// For deleted history
$history_id = $this->cal_table.’.cal_id’;
// Postgres needs a cast

                  if($this->db->Type == 'postgres') {

                  if($this->db->Type == 'pgsql') {
                          $history_id = "CAST($history_id AS VARCHAR)";
                  }

The difference is that the dbtype is ‘pgsql’, not ‘postgresql’ in the
header.inc.php.

Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

ngegw 2010-03-07 22:11:20 UTC #9

Does r29412 work for you Anthony?

That doesn’t work, but this does:
…
The difference is that the dbtype is ‘pgsql’, not ‘postgresql’ in the
header.inc.php.

Thank you.

Nathan

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers