sql parser problem

A place to discuss and receive support for the Library DB application.

sql parser problem

Postby 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.
raz3k
 
Posts: 3
Joined: Thu Aug 18, 2011 2:57 am

Re: sql parser problem

Postby shannah » Thu Aug 18, 2011 9:38 am

Looks like you don't have a table called "Personal" in your database.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: sql parser problem

Postby 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 .
raz3k
 
Posts: 3
Joined: Thu Aug 18, 2011 2:57 am

Re: sql parser problem

Postby 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
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: sql parser problem

Postby 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.
raz3k
 
Posts: 3
Joined: Thu Aug 18, 2011 2:57 am

Re: sql parser problem

Postby 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.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm


Return to Library DB Discussion

Who is online

Users browsing this forum: Google [Bot] and 2 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved