Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » LIST2 Pattern Problem
LIST2 Pattern Problem [message #2120] Thu, 18 June 2009 04:52 Go to next message
ajwid01 is currently offline  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 #2121 is a reply to message #2120] Thu, 18 June 2009 05:25 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
Firstly, there is no LINK2 pattern. There is either a LINK1 or a LIST2.

Secondly, if the HTML control is showing as a dropdown list but the list is empty, then have you defined the contents of the list? Please refer to FAQ09.


Re: LINK2 Pattern Problem [message #2122 is a reply to message #2120] Thu, 18 June 2009 05:58 Go to previous messageGo to next message
ajwid01 is currently offline  ajwid01
Messages: 26
Registered: June 2009
Location: UK
Junior Member
My apologies.

The first attempt at posting failed, and I had to rewrite everything. In the interim period, I mistranslated the pattern I was intending to refer to.

The pattern I am attempting to invoke was the LIST2, via the 'Generate PHP' dialogue on the child table.

I was expecting that the drop-down box would be auto-populated with an available list of related outer tables (i.e. designated parents), but this was not so.

It's here where I am wondering if I am missing something.

T.
Re: LINK2 Pattern Problem [message #2123 is a reply to message #2122] Thu, 18 June 2009 06:17 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: LINK2 Pattern Problem [message #2124 is a reply to message #2123] Thu, 18 June 2009 06:24 Go to previous messageGo to next message
ajwid01 is currently offline  ajwid01
Messages: 26
Registered: June 2009
Location: UK
Junior Member
Aha,

This now explains it then.

It would appear in my attempts to overcome some of the quirks of the Oracle SQL engine, so as to circumvent some of the errors I was encountering earlier, I have inadvertently broken the functionality of another component.

Whilst I have correctly populated the column data for the appropriate tables, this is not being "picked up" in the overview of tables (list), although if you select the table and press the 'Columns' button, you can see all the columns have been successfully imported.

Sadly, I'll have to go and see if I can re-engineer some of the earlier sections of code so as to make the Oracle specific bits work but not break this!

Sad

I should have noted the missing columns totals there earlier!

Many thanks for pointing me there.

T.
Re: LINK2 Pattern Problem [message #2125 is a reply to message #2120] Thu, 18 June 2009 07:19 Go to previous messageGo to next message
ajwid01 is currently offline  ajwid01
Messages: 26
Registered: June 2009
Location: UK
Junior Member
Hi Tony,

I have now resolved the problem.

I ammended the code (dict_table.class.inc we spoke about yesterday) to make function calls for both COLUMN_COUNT and REL_COUNT, instead of making use of the in-line queries (which could not then be referred to in the group by section by Oracle).

I re-instated some lower down IF elements that were previously causing me some problems elsewhere, and all appears to function fine now.

I don't get errors on populating the TABLE list, and I can correctly generate the PHP elements for a LIST2 pattern, as the drop-down list for the OUTER TABLE is now correctly populated.

I suppose I might encounter some issues somewhere else, but for the moment things look A-OK.

Best regards,

- Tony.
Re: LINK2 Pattern Problem [message #2126 is a reply to message #2125] Thu, 18 June 2009 09:36 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: LINK2 Pattern Problem [message #2127 is a reply to message #2120] Thu, 18 June 2009 09:40 Go to previous messageGo to next message
ajwid01 is currently offline  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 #2128 is a reply to message #2120] Thu, 18 June 2009 09:52 Go to previous messageGo to next message
ajwid01 is currently offline  ajwid01
Messages: 26
Registered: June 2009
Location: UK
Junior Member
Many thanks Tony.

I'll try the new deliverable, and maybe it will resolve other things I am seeing, you never know.

With me "tinkering around", changing things to work with Oracle better, I just never know when/if I'm going to break something elsewhere.

If I do break something, it just wastes more of your time trying to reply to me on something that's just plain not your fault.

Surprised

Thank you for having the patience to bear with me through this.

T.
Re: LINK2 Pattern Problem [message #2129 is a reply to message #2120] Thu, 18 June 2009 09:54 Go to previous messageGo to next message
ajwid01 is currently offline  ajwid01
Messages: 26
Registered: June 2009
Location: UK
Junior Member
Boo hiss.

Looks like Corporate Network policy won't let me download the file, so I'll have to wait until tomorrow now to try out the modifications.

T.
Re: LINK2 Pattern Problem [message #2130 is a reply to message #2127] Thu, 18 June 2009 10:10 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: LINK2 Pattern Problem [message #2131 is a reply to message #2128] Thu, 18 June 2009 10:19 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
If any SQL statement generated by 'dml.oracle.class.inc' fails then it would be very helpful if you could identify the fault and show what the correct SQL should be, then I can make the necessary changes to this one file.

This just shows that my design decision to have a separate DML class for each database engine (MySQL, PostgreSQL and Oracle) is showing benefits as any differences between the different database engines can be isolated in a single file.

Aren't I the clever one Very Happy

Don't applaud, just throw money Laughing


Re: LINK2 Pattern Problem [message #2132 is a reply to message #2120] Thu, 18 June 2009 10:30 Go to previous messageGo to next message
ajwid01 is currently offline  ajwid01
Messages: 26
Registered: June 2009
Location: UK
Junior Member
I utilised the _cm_pre_getData function in the child_table.class.inc, to try and translate the where clause field names which were different.

Looking at the logged SQL though, the actual retrieval of the child data doesn't actually seem to put any delimiter on the returned data:

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


I would have expected to see in here something related to the parent primary key field (seq_number), such as

where olap_delivery_data3.seq_number=(the seq_number specified from the selected row in the list1 form of the parent table)


Or at least some SQL that cross-tranlates that to something like:

where child_table.seq_number=(parent row.seq_number)


- that would hopefully get translated by the _cm_pre_getData function in the class.

I'm feeling a little stumped here. I'm almost certainly missing something key.

Re: LINK2 Pattern Problem [message #2133 is a reply to message #2120] Thu, 18 June 2009 10:36 Go to previous messageGo to next message
ajwid01 is currently offline  ajwid01
Messages: 26
Registered: June 2009
Location: UK
Junior Member
Another couple of excerpts that confused me here too:

This bit of SQL is obviously checking we just get 1 row returned for the assumed primary key

SELECT count(*) FROM olap_delivery_data3 WHERE olap_delivery_data3.seq_number='138376'  =>Count=1


This SQL appears to be going and retrieving all the fields for the parent record, utilising the primary key sequence number:

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


This is where is gets confusing:

I don't understand why this piece of SQL goes and runs, effectively returning a count of the total number of child records that match ALL records in the parent table.

Shouldn't it just be delimiting here with an additional where clause on the SEQ_NUMBER?

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


These are the preceding steps to the earlier post, but it's obviously following a similar vane, in that it doesn't seem to want to pass through a where clause to the child selection query.

Surprised
Re: LINK2 Pattern Problem [message #2134 is a reply to message #2133] Thu, 18 June 2009 10:47 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: LINK2 Pattern Problem [message #2135 is a reply to message #2120] Thu, 18 June 2009 11:01 Go to previous messageGo to next message
ajwid01 is currently offline  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 messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: LINK2 Pattern Problem [message #2137 is a reply to message #2120] Thu, 18 June 2009 12:02 Go to previous messageGo to next message
ajwid01 is currently offline  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.

Shocked

T.

[Updated on: Thu, 18 June 2009 12:03]

Report message to a moderator

Re: LINK2 Pattern Problem [message #2138 is a reply to message #2137] Thu, 18 June 2009 12:52 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
Attached is a new version of file menu-data.sql which contains the column names as well as the values. This should solve that problem.
  • Attachment: menu-data.zip
    (Size: 21.62KB, Downloaded 1202 times)


Re: LINK2 Pattern Problem [message #2139 is a reply to message #2120] Fri, 19 June 2009 08:40 Go to previous messageGo to next message
ajwid01 is currently offline  ajwid01
Messages: 26
Registered: June 2009
Location: UK
Junior Member
Hi Tony,

Sorry I'm a bit late in getting around to replying today.

Some things now work better, with your updated Oracle class files, however there are still some issues with the Query formulation.

I've had to revert back to modifying the query generated in dict_table.class.inc to use 1 of my own functions for REL_COUNT and group by specification in order for it to work.

But, it's nearly there. I also didn't have to ammend anything anywhere else (which I had to do before).

I haven't managed to install a debugger at work, so I've had to perform some tests to try and work out at which point things are going wrong for the child query.

It's definately in the lines you mention, because if I manually set '$where' after the call to the array2where function, the rest of the code works perfectly.

So, something is definately going awry at that point.

T.
Re: LIST2 Pattern Problem [message #2140 is a reply to message #2139] Fri, 19 June 2009 09:11 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: LIST2 Pattern Problem [message #2141 is a reply to message #2120] Fri, 19 June 2009 09:58 Go to previous messageGo to next message
ajwid01 is currently offline  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 messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: LIST2 Pattern Problem [message #2144 is a reply to message #2120] Mon, 22 June 2009 09:30 Go to previous messageGo to next message
ajwid01 is currently offline  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 messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: LIST2 Pattern Problem [message #2146 is a reply to message #2120] Mon, 22 June 2009 11:10 Go to previous messageGo to next message
ajwid01 is currently offline  ajwid01
Messages: 26
Registered: June 2009
Location: UK
Junior Member
Ok,

Got a little further now...


I introduced a third 'pseudo' column in the table definitions which I introduced into the PK/FK relationship, to see if indeed it was just missing out on the last column of the PK array.

Result is:

It's not missing out the field at all. It's now missing out the MIDDLE field of the PK composite.

The is the only field that is 'named differently' between the Outer/Inner tables, whereas the other columns match.

I understood earlier that I needed to 'rewrite' the query definition somewhere, such that it would translate the columns automatically.

I believe I actually did this in an earlier effort, so will now attempt to "retrace my steps" and see where I implemented this: although if I do it like I did it before... it likely won't work still - so I'm presuming I did something wrong previously.

Maybe a retrace and reread will open my eyes a bit further.

Glad I have ruled out a problem with the array though.

Smile

T.
Re: LINK2 Pattern Problem [message #2147 is a reply to message #2130] Mon, 22 June 2009 11:19 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: LIST2 Pattern Problem [message #2148 is a reply to message #2120] Mon, 22 June 2009 11:35 Go to previous messageGo to next message
ajwid01 is currently offline  ajwid01
Messages: 26
Registered: June 2009
Location: UK
Junior Member
Sad

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


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 messageGo to next message
ajwid01 is currently offline  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.


Embarassed

T.

[Updated on: Mon, 22 June 2009 11:39]

Report message to a moderator

Re: LIST2 Pattern Problem [message #2150 is a reply to message #2149] Mon, 22 June 2009 11:48 Go to previous message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
It's always the little things that trip you up. Glad you got it sorted.

Previous Topic: Question about the session page stack
Next Topic: AUTO_INCREMENT PK Column for Oracle Table
Goto Forum:
  


Current Time: Thu Mar 28 15:19:29 EDT 2024

Total time taken to generate the page: 0.01986 seconds