I use postgres as my database back end, and when I try to set permissions on a node, I get a couple of warnings on the INSERT INTO node_access statements saying "... syntax error near SET ..." I believe the problem is that postgres only knows about the old INSERT INTO tblname (var1, var2...) VALUES (val1, val2...) syntax.

Comments

emptyvoid’s picture

emptyvoid commented
Assigned: Unassigned » emptyvoid

Great someone with postgres!
If you would be so kind I need help debugging postgres as I don't have a local test environment using that database. Everything was built using Mysql 5.0 so if I can change my queries to be more database agnostic that would be preferred.

If you could provide more information and or recommendations on changing the syntax of my SQL to work both in postgres and Mysql that would be great.

I will work on a few patches, and if you would be so kind as to test the patches for me we should be able to fix this issue.

emptyvoid’s picture

emptyvoid commented
Version: 6.x-1.2 » 6.x-1.3

Updating version, this issue is still unresolved.. Once to fix the include bug I will focus on making the modules PostGRESS compliant.

dave reid’s picture

dave reid
he/him
Primary language English
Location Nebraska USA
commented

From geo27 in #drupal:

db_query("INSERT INTO {node_access_role} (nid, rid, realm, grant_view, grant_update, grant_delete) values (%d, %d, '%s', %d, %d, %d)", $node->nid, $role, $realm, $grant_view, $grant_edit, $grant_delete);

Tabecka’s picture

Tabecka commented

I am using the most recent version of all three modules.
Using PostgreSQL 8.3, Apache 2.2.11, PHP Version 5.2.9-1, XP Pro SP3.
These are the lines i edited and everything seems to be working just fine with PostGreSQL now.

menu_item_access.module line 1207
db_query("INSERT INTO {menu_item_access_role} (mlid, rid, realm, grant_view, grant_update, grant_delete) values (%d, %d, '%s', %d, %d, %d)", $menu_id, $role, $realm, $grant_view, $grant_edit, $grant_delete);

node_access.module line 1320
db_query("INSERT INTO {node_access_role} (nid, rid, realm, grant_view, grant_update, grant_delete) values (%d, %d, '%s', %d, %d, %d)", $node->nid, $role, $realm, $grant_view, $grant_edit, $grant_delete);

menu_access.module line 1836
db_query("INSERT INTO {menu_access_role} (menu_name, rid, realm, grant_view, grant_update, grant_delete) values ('%s', %d, '%s', %d, %d, %d)", $menu, $role, $realm, $grant_view, $grant_edit, $grant_delete);

I have been able to add menu and set their permissions, i have not had anymore missing page errors or any errors at all for that fact.
Thanks for the module.
Tootls

emptyvoid’s picture

emptyvoid commented
Version: 6.x-1.3 » 6.x-1.7
Status: Active » Fixed

Thanks I tested your sql changes and they do indeed work on mysql and postgress! Great work. Release 1.7 have your patches.. thanks.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.