DB error on egroupware 1.8 with (postgresql)

gustavoacm 2011-01-13 15:25:06 UTC #1

hi everybody

here again, a nice day to Klaus, Gray and Becker

there was a bug That I posted before but you didn’t reply about it.

using postgresql on debian and windows, and when you wants to create a distribution list in the addressbook, and then you enter to that distribution list, it show you a big DB error. I try it with mysql and is working fine,

this is the error:

Invalid SQL: SELECT egw_addressbook.*,n_family,n_family<>’’ FROM egw_addressbook JOIN egw_addressbook2list ON egw_addressbook.contact_id=egw_addressbook2list.contact_id AND list_id=1 LEFT JOIN egw_accounts ON egw_addressbook.account_id=egw_accounts.account_id WHERE contact_tid=‘n’ AND (egw_addressbook.contact_owner=9 OR contact_private=0 AND egw_addressbook.contact_owner IN (9,0) OR egw_addressbook.contact_owner IS NULL) AND (account_expires IS NULL OR account_expires = -1 OR account_expires > 1294932544) ORDER BY n_family<>’’ DESC,n_family ASC,n_given ASC,org_name ASC:
ERROR: ORDER BY Â«n_familyÂ» is ambiguous
LINE 1: …_expires > 1294932544) ORDER BY n_family<>’’ DESC,n_family A…
^

I will appreciate your answers.

thanks in advance.

Leithoff_Klaus 2011-01-13 15:35:42 UTC #2

problem was: I did not find a solution at the time I was looking, then I had no time, then I forgot.
Sorry

Leithoff_Klaus 2011-01-13 16:59:29 UTC #3

Try this patch:
Index: addressbook/inc/class.addressbook_sql.inc.php

— addressbook/inc/class.addressbook_sql.inc.php (Revision 33606)
+++ addressbook/inc/class.addressbook_sql.inc.php (Arbeitskopie)
@@ -437,6 +437,12 @@
if (!is_array($extra_cols)) $extra_cols = $extra_cols ? explode(’,’,$extra_cols) : array();
$extra_cols[] = $matches[1];
$extra_cols[] = $matches[1]."<>’’";

```
  		//_debug_array($matches[1]);
```

  		if (!empty($order_by) && $matches[1] != 'extra_order.contact_value' && stripos($matches[1],'.')===false) // postgres requires explizit order by

```
  		{
```

  			$order_by = str_replace($matches[1],'egw_addressbook.'.$matches[1],$order_by);

```
  		}
```

  		//_debug_array($order_by);
  	}
  }
  // add join to show only active accounts (only if accounts are shown and in sql and we not already join the accounts table, eg. used by admin)

Leithoff_Klaus 2011-01-14 13:03:27 UTC #4

just commited yesterdays patch. As far as my testcases where concerned it works now for postgres AND mysql

gustavoacm 2011-01-14 15:03:00 UTC #5

the solution was: a patch in class.addressbook_sql.inc.php file, then you finally reply, then you find the time, then you find the solution, then it was tested by me, then it works perfectly, then I appreciate it, then

thank you very much, Klaus, Leithoff

you’re just a so cool person.

gustavoacm 2011-03-03 18:13:13 UTC #6

Hi Klaus Leithoff

need your help again, there is another error when using postgresql:

Invalid SQL: SELECT cal_id FROM egw_cal_user WHERE cal_user_type=‘u’ AND cal_user_id=‘7’ AND cal_id IN (SELECT cal_id FROM egw_cal_user other WHERE other.cal_id=cal_id AND other.cal_user_id=5 AND cal_user_type=‘u’):
ERROR: the operator does not exist: character varying = integer
LINE 1: …er WHERE other.cal_id=cal_id AND other.cal_user_id=5 AND cal…
^
HINT: No operator matches the given name and type of arguments. You may want to add explicit type casts

sorry if it bothers you.

I will appreciate your support. thanks in advance.

gustavoacm 2011-03-03 18:14:41 UTC #7

Hi Klaus Leithoff

need your help again, there is another error when using postgresql:

sorry if it bothers you.

I will appreciate your support. thanks in advance.