Home » RADICORE » How To » LIST2 Pattern Problem
LIST2 Pattern Problem [message #2120] |
Thu, 18 June 2009 04:52 ![Go to next message Go to next message](/theme/default/images/down.png) |
ajwid01
Messages: 26 Registered: June 2009 Location: UK
|
Junior Member |
|
|
Hi,
I have 2 tables (parent/child). I have PKs defined on both of them and an FK defined on the child.
I have set up a relationship between parent/child based on the defined DB foreign key links.
However, when I attempt to setup a LINK2 pattern on the child table, the 'drop-down' box for the outer table designation is empty.
I've reviewed the example, and I think I'm doing the right thing... but accept I might be being stupid somewhere and have missed something..
I initially had composite field PK and FK designators, but I changed these to an auto-increment (sequence) IDs, to see if this would make a difference, but it hasn't.
Is there something I am missing?
Best regards,
- Tony.
|
|
|
|
|
Re: LINK2 Pattern Problem [message #2123 is a reply to message #2122] |
Thu, 18 June 2009 06:17 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
The contents of the dropdown list for 'Outer Table Name' is populated by extracting all entries on DICT_TABLE for the database_id identified as 'Outer Database', but only those tables where the count of entries on DICT_COLUMN is greater than zero. So, if you have not yet imported any columns for those tables then they will not appear in the dropdown list.
If you go to the List Table by Database screen and do not see a value in the 'Columns' column then you have missed a step. Select each table then press the 'Columns' button to activate the List Columns by Table, then press the 'Import Columns' button.
It is only AFTER you have imported a table's columns that it will appear in a dropdown list.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
Re: LINK2 Pattern Problem [message #2126 is a reply to message #2125] |
Thu, 18 June 2009 09:36 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Ever since you brought this problem with the Oracle database to my attention via private email I have been seeking a proper solution. After playing around with tasks which use either the GROUP BY or HAVING clause I have made changes to the 'dml.oracle.php4/5.class.inc' files so that they can construct valid SQL statements using the information provided.
The problem is that each database vendor uses a different interpretation of the SQL standard, so what works in MySQL may not work in Oracle.
Take, for example, the GROUP BY clause. Oracle insists that every column in the SELECT list is also in the GROUP BY clause whereas MySQL is less strict. Although the Oracle implemetation was true in the SQL standard of 1991 this was changed in 1999 so that any column in the GROUP BY clause which is functionally dependent on another column in the GROUP BY clause does *NOT* have to be included. According to relational theory any non-key column on a table is functionally dependent on the primary key of that table, so if the GROUP BY clause contains the primary key then any non-key columns do not have to be specified.
The HAVING clause is used when the result set needs to be filtered by an aggregated column. This cannot be referenced in the WHERE clause as this is used before the results of the aggregation are known, so it has to be moved to the HAVING clause. MySQL is intelligent in that where the SELECT list contains an entry such as '<expression> AS aliasname' (where <expression> is an aggregation, a function or a subquery) it will allow the HAVING clause to reference that entry by 'aliasname' instead of '<expression>'. This is intelligent because it prevents <expression> from having to be defined and evaluated again. Oracle, on the other hand, is not so intelligent. You cannot use 'aliasname' in the HAVING clause, it has to be '<expression>', but ONLY if it is an aggregation, and not a function or a subquery. The only way around this is to put the SQL statement (without the HAVING clause) in a subquery, then enclose this in an outer query with the HAVING clause switched to the WHERE clause.
The attached file contains updates which work in MySQL, and the 'dml.oracle.php4/5.class.inc' file will automaically make changes to the GROUP BY and HAVING clauses which are required for Oracle.
This means that any changes which you made to either 'dict_table.class.inc' or 'std.table.class.inc' can be reversed out as they are no longer needed.
Try it out and let me know how you get on.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
Re: LINK2 Pattern Problem [message #2127 is a reply to message #2120] |
Thu, 18 June 2009 09:40 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
ajwid01
Messages: 26 Registered: June 2009 Location: UK
|
Junior Member |
|
|
Hmm,
Now I seem to be encountering a new problem.
The LIST2 works fine, having successfully inserted the additional navigation button into the LIST1 form for the parent record(s).
However, the associated child records are not being correctly selected. Every entry from the child table is being returned into the child frame.
Now, initially I thought this would be due to the requirement to ammend the $where string, as the parent PK field is named differently from the child PK field.
I went through the process of trying to trap this, with the use of the recommended additional class function under "Associations" documentation (from an earlier thread).
This still didn't seem to work.
I looked at making the PK/FK field names match, but this wouldn't work either, and I started to run into some 'ambiguous' column errors on SQL selects, so reverted back to different names again.
I have now switched on the SQL Logging for the form, and the output is this:
ALTER SESSION SET CURRENT_SCHEMA = "WAREHOUSE"
SELECT count(*) FROM olap_delivery_data3 WHERE olap_delivery_data3.seq_number='138376' =>Count=1
select * from ( select a.*, rownum as rnum from ( SELECT olap_delivery_data3.* FROM olap_delivery_data3 WHERE olap_delivery_data3.seq_number='138376' ) a where rownum <= 1 ) where rnum >= 1=>Count=1
ALTER SESSION SET CURRENT_SCHEMA = "WAREHOUSE"
ALTER SESSION SET CURRENT_SCHEMA = "WAREHOUSE"
SELECT count(*) FROM olap_delivery_notes3 LEFT JOIN olap_delivery_data3 ON (olap_delivery_data3.seq_number=olap_delivery_notes3.delivery_data_seq) =>Count=2076
select * from ( select a.*, rownum as rnum from ( SELECT olap_delivery_notes3.*, olap_delivery_data3.seq_number FROM olap_delivery_notes3 LEFT JOIN olap_delivery_data3 ON (olap_delivery_data3.seq_number=olap_delivery_notes3.delivery_data_seq) ) a where rownum <= 25 ) where rnum >= 1=>Count=2076
ALTER SESSION SET CURRENT_SCHEMA = "MENU"
SELECT role_id,global_access FROM mnu_role WHERE role_id='GLOBAL' ORDER BY role_id =>Count=1
ALTER SESSION SET CURRENT_SCHEMA = "MENU"
SELECT x.task_id, x.script_id, x.pattern_id, mnu_nav_button.button_text, mnu_nav_button.context_preselect FROM mnu_nav_button LEFT JOIN mnu_task ON (mnu_task.task_id = mnu_nav_button.task_id_snr) LEFT JOIN mnu_task x ON (x.task_id = mnu_nav_button.task_id_jnr) WHERE mnu_task.task_id='mops_olap_delivery_notes3(list2)' AND (x.is_disabled='N') ORDER BY mnu_nav_button.sort_seq =>Count=6
I understand that a where clause should be passed through to the child query from the associated parent record, but there doesn't seem to be any 'where' clause parameter being set.
Do I have to configure this somewhere else?
T.
|
|
|
|
|
Re: LINK2 Pattern Problem [message #2130 is a reply to message #2127] |
Thu, 18 June 2009 10:10 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
If you are saying that in the LIST2 screen the child entries are not being filtered by the primary key of the parent entry then there are two things to look out for:
(1) The field list for the parent/outer table MUST contain all the primary key fields, otherwise there is nothing which can be extracted into the WHERE clause for the child/inner table.
(2) If the fieldname in the WHERE clause extracted from the parent table does not match the corresponding fieldname in the child table then you will have to modify the WHERE clause after it has been received in the child table object.
Please take a look at FAQ97 and FAQ98 for more details.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
Re: LINK2 Pattern Problem [message #2134 is a reply to message #2133] |
Thu, 18 June 2009 10:47 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
In the LIST2 task what table is the outer/parent entity? What is its primary key?
What table is the inner/child entity? What is its corresponding foreign key?
What is the WHERE string which is being extracted from the parent table and passed down to the child table? If this is empty it means that the fieldarray for the parent table does not include the primary key, therefore there is nothing to extract.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
Re: LINK2 Pattern Problem [message #2135 is a reply to message #2120] |
Thu, 18 June 2009 11:01 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
ajwid01
Messages: 26 Registered: June 2009 Location: UK
|
Junior Member |
|
|
The outer table is OLAP_DELIVERY_DATA3, it has a PK of SEQ_NUMBER. Historically, when I failed to properly specify the PK, I couldn't select any row to READ/UPDATE, and I am assuming it would similarly fail to propagate through to the LIST2 task.
The inner table is OLAP_DELIVERY_NOTES3, it has a PK of NOTE_SEQ, and a foreign key field of DELIVERY_DATA_SEQ, which maps to the parent.SEQ_NUMBER.
This parent/child relationship is mapped on that field, as a RESTRICTED relationship.
Within the header form on the LIST2 output, the parent record is displayed along with it's PK field (SEQ_NUMBER), but there does not appear to be any WHERE clause being passed down to the child record retrieval process.
So, still a bit stumped really. Something seems to be amiss in the population of a where clause, but I can't figure out what it might be; what underpins it that is missing.
|
|
|
Re: LINK2 Pattern Problem [message #2136 is a reply to message #2135] |
Thu, 18 June 2009 11:44 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Can you step through with your debugger to see what happens at line 266 of file std.list2.inc where it contains:
$pkeynames = $dbouter->getPkeyNamesAdjusted();
$inner_where = array2where($outer_data, $pkeynames, $dbouter);
This should contain the primary key of the outer entity which is passed to the getData() method of the inner entity.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
Re: LINK2 Pattern Problem [message #2137 is a reply to message #2120] |
Thu, 18 June 2009 12:02 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
ajwid01
Messages: 26 Registered: June 2009 Location: UK
|
Junior Member |
|
|
Tony,
I'm going to wait until tomorrow, when I can implement the changed code you have sent through to the installation, and just double-check that nothing I have done (and failed to UNDO properly) in the code is causing issues.
I just started to look at a fresh install, and came across an anomaly on the MNU_TASK_FIELD table inserts.
Table is defined as:
Name Null? Type
------------------------- -------- ----------------------------
TASK_ID NOT NULL VARCHAR2(80 CHAR)
FIELD_ID NOT NULL VARCHAR2(40 CHAR)
CREATED_DATE NOT NULL TIMESTAMP(6)
CREATED_USER NOT NULL VARCHAR2(16 CHAR)
FIELD_DESC VARCHAR2(255 CHAR)
REVISED_DATE TIMESTAMP(6)
REVISED_USER VARCHAR2(16 CHAR)
Inserts are attempted like:
INSERT INTO mnu_task_field VALUES('mnu_control(upd3)', 'DEFAULT_LANGUAGE', NULL, '2006-04-10 09:36:18', 'AJM', '2007-09-10 10:44:37', 'AJM')
*
ERROR at line 1:
ORA-12899: value too large for column "MENU"."MNU_TASK_FIELD"."CREATED_USER"
(actual: 19, maximum: 16)
So, looks like a field is being inserted 'out of order' with that expected by the table in this instance.
I spooled everything out to logfiles from SqlPlus initially, but obviously missed the odd error.
So, as per the above, I'll continue tomorrow with a fresh install from your newly-worked Oracle code elements, double-check that there aren't some other errors creeping in along the way, and then at least I'll be debugging from a "clean" install position.
Hopefully I'll end up with a good ending to the week, come close of play tomorrow.
Failing that, I'll have time to mull some things over whilst traipsing down to Heathrow and back tomorrow evening.
T.
[Updated on: Thu, 18 June 2009 12:03] Report message to a moderator
|
|
|
|
|
Re: LIST2 Pattern Problem [message #2140 is a reply to message #2139] |
Fri, 19 June 2009 09:11 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
What problems are you having with the queries generated from dict_table.class.inc for the Oracle database? I have tried them with both MySQL and Oracle without any problems, so I would like to know what your failure is. What is the query which fails?
As for your second problem, if it works when you manually insert a value for $inner_where after line 267 of std.list2.inc then something is wrong with the input to the array2where() function.
Does $pkey_names identify the primary key for this table?
Does $outer_data contain a value for each of the fields identified in $pkey_names?
The quicker you can get a debugger installed and running the quicker you will be able to track down the cause of this problem.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
Re: LIST2 Pattern Problem [message #2141 is a reply to message #2120] |
Fri, 19 June 2009 09:58 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
ajwid01
Messages: 26 Registered: June 2009 Location: UK
|
Junior Member |
|
|
I'll park the primary key problem for right now, until I can properly debug it.
I did have the query pasted in notepad for you, until a scheduled update rebooted my PC (when apparently it shouldn't have), and I lost it.
Will be easy enough to recreate. I'll put back the original query, let it error and then post you the results.
Roughly, from what I recall it's:
I think the HAVING clause (count(dict_table.table_id {?}) gets converted to a WHERE clause, but when it gets re-written by Oracle in the separate oracle php class, it's no longer a COUNT(), as the outer SELECT has converted it to a designated column id from the inner select's alias.
[ Hope that makes sense. ]
And I think Oracle is still griping about the inner select's in-line query not being in the 'group by'.
[ This is the bit that I rewrote as a function call, to avoid the in-line query and so allow a group by on it. ]
This is my modified section from the db_table.class.inc:
if ($item == 'table_id') {
// get data from the database, ignoring any tables which have no entries on DICT_COLUMN
$this->sql_select = 'dict_table.table_id, table_desc, count(dict_column.table_id) as column_count';
$this->sql_orderby = 'dict_table.table_id';
$this->sql_ordery_seq = 'asc';
$this->sql_from = 'dict_table '
. 'LEFT JOIN dict_column ON (dict_table.database_id=dict_column.database_id AND dict_table.table_id=dict_column.table_id) ';
$this->sql_having = 'column_count > 0';
$this->sql_groupby = 'dict_table.table_id, table_desc,AJW_RAD_GET_REL_TABLE_COUNT(DICT_TABLE.DATABASE_ID, DICT_TABLE.TABLE_ID) ';
$data = $this->getData($where);
// convert each row into 'id=id' in the output array
foreach ($data as $row => $rowdata) {
$rowvalues = array_values($rowdata);
$array[$rowvalues[0]] = $rowvalues[0];
} // foreach
return $array;
} // if
The main bits are the function call for REL_COUNT, and the changing of the HAVING clause away from the count. I think yours USED to be like this, before avoiding the aliased column that Oracle didn't like.
But now, by the time it gets converted from HAVING to WHERE to the Oracle specific PHP class, it can reference the column directly again.
The simple (rough and ready) code for the REL_COUNT replacement was just:
CREATE OR REPLACE FUNCTION AJW_RAD_GET_REL_TABLE_COUNT(IN_DB VARCHAR2, IN_TAB VARCHAR2) RETURN NUMBER
IS
V_RETURN NUMBER;
BEGIN
BEGIN
SELECT COUNT(*)
INTO V_RETURN
FROM DICT_RELATIONSHIP
WHERE
DATABASE_ID_SNR = IN_DB AND
TABLE_ID_SNR = IN_TAB;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
RETURN V_RETURN;
END;
/
I think that kinda works.
Don't think I changed anything else.
|
|
|
Re: LIST2 Pattern Problem [message #2142 is a reply to message #2141] |
Fri, 19 June 2009 10:55 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
You are not using the new verson of dict_table.class.inc that I sent you. In it I replaced
count(dict_column.table_id) as column_count
with
(SELECT COUNT(table_id) FROM dict_column WHERE dict_column.database_id=dict_table.database_id AND dict_column.table_id=dict_table.table_id GROUP BY dict_table.table_id) AS column_count
This new code does not produce the error that you are having.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
Re: LIST2 Pattern Problem [message #2144 is a reply to message #2120] |
Mon, 22 June 2009 09:30 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
ajwid01
Messages: 26 Registered: June 2009 Location: UK
|
Junior Member |
|
|
Hi Tony,
Apologies, I misread and thought it was only the oracle php classes that had changed, so didn't extract anything additional.
Admittedly, now invoking your new ammendments to the dict table class resolves all earlier problems.
Regarding the debugging... I still don't have that installed and configured. Am hoping to get to that later today.
In the interim though, I thought I would give you this additional piece of information I have discovered. [ Never know, it might help...]
Originally, I had a 2 field composite PK on the Outer/Parent table but for simplicity converted this to a sequence-based numeric primary key.
When using this in the List2/Multi4 tasks, the where clause against the Inner table ends up null.
However, I reverted back to the 2-field composite key reference this morning and found it PARTIALLY worked!
I ended up with an Inner table 'where' clause based on the leading field in the primary key, which was a massive difference from where I was at before... but as you will appreciate, still brings me back unrelated records from the missing additional composite field.
I figured mentioning this here might allow something to "click" for you, if not entirely backed up with a debug output just yet.
Certainly something is being processed for the pkeynames array, but not quite how it should.
Hopefully will get back to you more fully later, if I can get some debugging working properly.
T.
|
|
|
Re: LIST2 Pattern Problem [message #2145 is a reply to message #2144] |
Mon, 22 June 2009 09:51 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
The last zip file I sent you contained several files, all of which had been changed, so it is not a good idea to only extract those files which you *think* have changed.
You say you have changed the primary key for that table, but have you re-exported the table's details from the data dictionary? Does the contents of the <table>.dict.inc file agree with the table structure? Does the fieldarray (determined by the select list) contain a value for every part of the primary key?
If the array2where($data, $pkeynames) function is not producing the right results then it can only be caused by either the list of $pkeynames is incorrect, or there is no value in $data for each of those fields.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Re: LINK2 Pattern Problem [message #2147 is a reply to message #2130] |
Mon, 22 June 2009 11:19 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Take a look at what I write in message 2130:
AJM wrote on Thu, 18 June 2009 15:10 | If you are saying that in the LIST2 screen the child entries are not being filtered by the primary key of the parent entry then there are two things to look out for:
(1) The field list for the parent/outer table MUST contain all the primary key fields, otherwise there is nothing which can be extracted into the WHERE clause for the child/inner table.
(2) If the fieldname in the WHERE clause extracted from the parent table does not match the corresponding fieldname in the child table then you will have to modify the WHERE clause after it has been received in the child table object.
Please take a look at FAQ97 and FAQ98 for more details.
|
Take a look also at Associated/Related Rows.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
Re: LIST2 Pattern Problem [message #2148 is a reply to message #2120] |
Mon, 22 June 2009 11:35 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
ajwid01
Messages: 26 Registered: June 2009 Location: UK
|
Junior Member |
|
|
![Sad](images/smiley_icons/icon_sad.gif)
This is exactly what I tried to follow previously.
I think what maybe sidetracked me, is I was maybe expecting to at least SEE a where clause in the generated SQL, but that it would likely generate an error when parsed - to suggest that the column_name in the where clause was invalid.
In actual fact, for a reason I'm not following right now, the 'where' clause just doesn't get to the parse stage, as presumably some code logic recognises it as being an 'invalid' column prior to that.
If I'd seen a parse error, I would have known what was happening and then looked to see where I needed to tranlate it.
As I didn't see a parse error, I wasn't sure, and presumed there was something going wrong somewhere else.
![Sad](images/smiley_icons/icon_sad.gif)
I've attempted, as per someone else's suggestion, that a call to _cm_pre_getData should be placed within the inner table's class file like:
require_once 'std.table.class.inc';
class olap_delivery_notes3 extends Default_Table
{
// ****************************************************************************
function olap_delivery_notes3 ()
{
// save directory name of current script
$this->dirname = dirname(__file__);
$this->dbms_engine = $GLOBALS['dbms'];
$this->dbname = 'warehouse';
$this->tablename = 'olap_delivery_notes3';
// call this method to get original field specifications
// (note that they may be modified at runtime)
$this->fieldspec = $this->getFieldSpec_original();
} // olap_delivery_notes3
function _cm_pre_getData ($where, $where_array, $fieldarray=null){
$where = str_replace('line_no=', 'order_line_no=', $where);
return $where;
}
// ****************************************************************************
} // end class
// ****************************************************************************
This doesn't seem to work for me though. The additional field still isn't picked up.
I'm presuming I just don't know how to write this bit of code, or where exactly to put it!
Hmm!
|
|
|
Re: LIST2 Pattern Problem [message #2149 is a reply to message #2120] |
Mon, 22 June 2009 11:38 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
ajwid01
Messages: 26 Registered: June 2009 Location: UK
|
Junior Member |
|
|
Aah sheesh,
What a long and convoluted problem I created for myself.
I could have probably solved this a whole half-week ago, if I'd known to persevere a little more in one direction. (I.e knew definately that it was a translation issue).
I've turned around the replace string patterns, and it's working.
T.
[Updated on: Mon, 22 June 2009 11:39] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sat Aug 17 03:12:38 EDT 2024
Total time taken to generate the page: 0.07986 seconds
|