sql parser problem

Archived from the Library DB Discussion forum.

raz3k — Thu Aug 18, 2011 3:27 am

Hello,

I recently updated librariandb from version 0.2 309 to 0.3.2 2530, after i added readded conf.ini and files under tables/books to fit my needs. The reason was php 4 -> php 5.3.6 and mysql 4.x -> mysql 5 .

./tables/books/valuelists.ini contains:

Code: Select all

`[book_categories]
sql = “select category_id, category_name from books_categories order by category_name”

[book_media]
sql = “select medium_id, medium_name from books_media order by medium_name”

[users]
sql = “select Personal.Personal_ID as userid, SUBSTRING_INDEX(mailbox.username,’@’,1) as username from postfix.mailbox, condica.Personal where (LOWER(CONCAT(Personal.prenume,’.’,Personal.nume))= LOWER(SUBSTRING_INDEX(mailbox.username,’@’,1)) and mailbox.username != ‘.’) order by username”`

./tables/books/fields.ini contains:

Code: Select all: [borrower_id] widget:type=select vocabulary=users ---

Problem:

I don’t have any problem logging in(the sql parses ok), but clicking edit on a book i get the error below.

It seems like there’s some kind of problem with the dataface mysql connector that the old version did not have regarding Otherdb.othertable.
..btw virtual_lib is the db name of librariandb.

Code: Select all: Fatal error: Error performing mysql query to get column information from table 'Personal'. The mysql error returned was : 'Table 'virtual_lib.Personal' doesn't exist'. On line 547 of file /arhive/www/internal/library/dataface/Dataface/Table.php in function printStackTrace() On line 472 of file /arhive/www/internal/library/dataface/Dataface/Table.php in function Dataface_Table(Personal,Resource id #13,) On line 470 of file /arhive/www/internal/library/dataface/Dataface/Relationship.php in function loadTable(Personal,Resource id #13) On line 377 of file /arhive/www/internal/library/dataface/Dataface/Relationship.php in function _normalizeColumns() On line 109 of file /arhive/www/internal/library/dataface/Dataface/Relationship.php in function _init(array(select Personal.Personal_ID as userid, SUBSTRING_INDEX(mailbox.username,'@',1) as username fro) On line 3425 of file /arhive/www/internal/library/dataface/Dataface/Table.php in function Dataface_Relationship(books,users__valuelist,array(select Personal.Personal_ID as userid, SUBSTRING_INDEX(mailbox.username,'@',1) as username fro) On line 208 of file /arhive/www/internal/library/dataface/Dataface/ValuelistTool.php in function addRelationship(users__valuelist,array(select Personal.Personal_ID as userid, SUBSTRING_INDEX(mailbox.username,'@',1) as username fro) On line 45 of file /arhive/www/internal/library/dataface/Dataface/FormTool/select.php in function asRelationship(Dataface_Table Object,users) On line 426 of file /arhive/www/internal/library/dataface/Dataface/FormTool.php in function buildWidget(Dataface_Record Object,array(borrower_id,int(11),YES,,,,books,array(Borrower id,,books.borrower_id.label,books.borrower_id.,Dataface_QuickForm Object,borrower_id,) On line 347 of file /arhive/www/internal/library/dataface/Dataface/QuickForm.php in function buildWidget(Dataface_Record Object,array(borrower_id,int(11),YES,,,,books,array(Borrower id,,books.borrower_id.label,books.borrower_id.,Dataface_QuickForm Object,borrower_id,,array(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,) On line 488 of file /arhive/www/internal/library/dataface/Dataface/QuickForm.php in function _buildWidget(array(borrower_id,int(11),YES,,,,books,array(Borrower id,,books.borrower_id.label,books.borrower_id.,array(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,) On line 68 of file /arhive/www/internal/library/dataface/actions/edit.php in function _build() On line 1309 of file /arhive/www/internal/library/dataface/Dataface/Application.php in function handle(array(array(Edit,some-url) On line 1756 of file /arhive/www/internal/library/dataface/Dataface/Application.php in function handleRequest() On line 22 of file /arhive/www/internal/library/index.php in function display() in /arhive/www/internal/library/dataface/Dataface/Table.php on line 547

Can you help?

Thanks.

shannah — Thu Aug 18, 2011 9:38 am

Looks like you don’t have a table called “Personal” in your database.

raz3k — Thu Aug 18, 2011 11:54 am

[users]
__sql__ = “select Personal.Personal_ID as userid, SUBSTRING_INDEX(mailbox.username,’@’,1) as username from postfix.mailbox, condica.Personal where (LOWER(CONCAT(Personal.prenume,’.’,Personal.nume))= LOWER(SUBSTRING_INDEX(mailbox.username,’@’,1)) and mailbox.username != ‘.’) order by username”

I have a database called Personal and a table called Personal_ID, but instead of Personal.Personal_ID it searches for virtual_lib.Personal .

shannah — Thu Aug 18, 2011 12:45 pm

Code: Select all: select Personal.Personal_ID from ...

This SQL statement says:
“Select the ‘Personal_ID’ column from the ‘Personal’ table in the current database”.

You would need to do something like:

Code: Select all: select Personal.Personal_ID.username from ...

to specify a column in another database.

That said I’m not certain if the parse will handle this sort of multi-database query.

What I generally do with an application that needs to access information from other databases is create a view in the main database that includes data from the other databases. That way, at the application level, everything is accessible as a single database.

-Steve

raz3k — Thu Aug 18, 2011 1:02 pm

Very odd, this sql query worked like a charm before the update, i will try to rewrite it and post back.

Thanks.

shannah — Thu Aug 18, 2011 2:57 pm

Personal_ID is a very strange name for a table. Are you sure that this is not actually a column, and Personal is not actually a table? The SQL query suggests this… otherwise there’s no way it ever ran correctly.