Radicore Forum: How To » override auto rdcaccount_id clause in a manually extended SQL

Home » RADICORE » How To » override auto rdcaccount_id clause in a manually extended SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

override auto rdcaccount_id clause in a manually extended SQL [message #1990]

Sun, 19 April 2009 11:07

gpatti
Messages: 283
Registered: August 2008

Senior Member

Tony,

In a _cm_getInitialDataMultiplefunction within a table class I have manually extended $this->sql_from and $this->sql_where to include data from a second table to achieve some desired functionality. Both the tables include the rdcaccount_id column.

I'm using getData_raw to retrieve the data.

Unfortunately this is now generating an ambiguous reference to rdcaccount_id (the table name is not explicitly stated in the generated SQL).

Can you give me a pointer to resolve this please?

Thanks,

Graham

[Updated on: Sun, 19 April 2009 11:15]

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #1991 is a reply to message #1990]

Sun, 19 April 2009 18:36

AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK

Senior Member

When you say "ambiguous reference" what exactly do you mean? What is the generated statement?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #1992 is a reply to message #1990]

Sun, 19 April 2009 19:37

gpatti
Messages: 283
Registered: August 2008

Senior Member

The error message displayed is: Fatal Error: MySQL error: 1052 - Column 'rdcaccount_id' in where clause is ambiguous (# 1052).
It's because I am joining to a 2nd table, and both tables contain rdcaccount_id, but the generated SQL has " AND rdcaccount_id in ('1', 'n')" as part of the WHERE clause. It would need to have " AND table_name.rdcaccount_id IN.."

Let me know if you need further info on how I have actually set up the $where clause, although the rdcaccount_id reference is being automatically added to the where clause by the framework. I can't see how I can change it.

Graham

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #1993 is a reply to message #1992]

Mon, 20 April 2009 05:25

AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK

Senior Member

Could you provide a copy of the actual SQL statement that was generated? I need to see exactly what the problem is.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #1994 is a reply to message #1990]

Mon, 20 April 2009 08:51

gpatti
Messages: 283
Registered: August 2008

Senior Member

Because the error crashes the system the SQL is not written out to the log file. However, the following is written out to the screen:

Fatal Error: MySQL error: 1052 - Column 'rdcaccount_id' in where clause is ambiguous (# 1052).

SQL query: SELECT SQL_CALC_FOUND_ROWS * FROM exam, candidate WHERE exam.candidate_id = candidate.candidate_id AND candidate.active_ind = 'Y' AND candidate.exam_ind = 'Y' AND session_id = '12' AND branch_id='F' AND rdcaccount_id IN ('1', '7') ORDER BY exam.session_id, exam.syllabus_type_id, exam.branch_id

Error in line 466 of file 'C:\xampp\includes\dml.mysqli.class.inc'.

The transaction is an ADD4 pattern.

Please let me know if there is any further info I need to provide.

Graham

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #1995 is a reply to message #1994]

Mon, 20 April 2009 11:03

AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK

Senior Member

You need to modify the contents of the getData() and getData_raw() methods in your version of 'std.table.class.inc' so that the lines containing

$account_id_string = "rdcaccount_id='1'";
- and -
$account_id_string = "rdcaccount_id IN ('1', '$account_id')";

are changed to include the table name, as in:

$account_id_string = "$this->tablename.rdcaccount_id='1'";
- and -
$account_id_string = "$this->tablename.rdcaccount_id IN ('1', '$account_id')";

Let me know if this solves your problem.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #1996 is a reply to message #1990]

Mon, 20 April 2009 11:59

gpatti
Messages: 283
Registered: August 2008

Senior Member

That has resolved the crash due to the ambiguous reference, but I'm not getting the expected results now. However, that's more likely to be my coding! I'm going to spend some time testing and will get back to you if I require further assistance.

Thanks for your help resolving the initial problem.

Graham

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #1997 is a reply to message #1990]

Mon, 20 April 2009 12:46

gpatti
Messages: 283
Registered: August 2008

Senior Member

Of course it WAS my coding - my SQL is a bit rusty and I was getting a cartesian product across the two tables. I needed to specify manually a join between the rdcaccount_id on each table. Everything working perfectly now.

Thanks again.

Graham

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #2009 is a reply to message #1990]

Tue, 21 April 2009 08:57

gpatti
Messages: 283
Registered: August 2008

Senior Member

I am doing regression testing prior to releasing this change into my production environment and have come across a side effect. I'm not sure yet whether this is because my original code was not robust, or whether the change to std.table.class.inc has introduced a bug.

I have a couple of LIST1 transactions from which I make a selection before navigating to a child form. The child form access a table for which rdcaccount_id is part of the primary key (but the initial table does not use rdcaccount_id as it is shared data), and with the updated std.table.class.inc the error message

Primary key (rdcaccount_id) is not complete - check selection

is displayed.

Any ideas?

Graham

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #2010 is a reply to message #2009]

Tue, 21 April 2009 09:15

AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK

Senior Member

Are you saying that the table accessed by the child task is not the same as the table accessed by the LIST1 task? And that the child table contains rdcaccount_id as part of its primary key while the LIST1 table does not?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #2011 is a reply to message #1990]

Tue, 21 April 2009 09:34

gpatti
Messages: 283
Registered: August 2008

Senior Member

Yes. I get the feeling you're gonna tell me that's not a good idea!

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #2012 is a reply to message #2011]

Tue, 21 April 2009 09:45

AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK

Senior Member

I'm just conducting a test with my development environment and I can see where this error is being generated. In the 'isPkeyComplete()' method it is looking for an entry with the name 'rdcaccount_id' and not finding it because it exists as '<tablename>.rdcaccount_id'. I am in the process of amending my code to deal with both possibilities. I will upload the fix shortly.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #2013 is a reply to message #2012]

Tue, 21 April 2009 10:11

AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK

Senior Member

The attached file contains all the updates which will be in the next release, plus the fix for your problem. Try it out and see if it works.

Attachment: radicore-2009-04-21-update.zip
(Size: 293.78KB, Downloaded 1236 times)

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #2014 is a reply to message #1990]

Tue, 21 April 2009 11:07

gpatti
Messages: 283
Registered: August 2008

Senior Member

This version solves the problem as described. During testing I found another similar variation, which gave the primary key not complete error. This version solves the error but the transaction behaviour has changed.

The scenario is a LIST1 transaction using a table WITH rdcaccount_id in the primary key navigating to a LINK transaction linking to a table WITHOUT rdcaccount_id. The link table itself has rdcaccount_id and the primary key of the second table as its key.

Previously the form displayed all entries from the secondary table, with the linked rows marked as selected.

Now the form only displays the linked entries, so I've lost the ability to link and unlink entries.

I suspect the problem is as a result of the design of the keys I have used being impacted by the correction in the framework. I was linking to the outer table using ONLY rdcaccount_id because my application forces that only one entry can exist in the outer table per rdcaccount_id. I probably need to add another field as key (in fact I have, but didn't make any app changes as it worked ok).

If you think it might still be a bug in the framework please let me know. I'll hold off changing my app till you've had chance to consider it.

Graham

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #2015 is a reply to message #2014]

Thu, 23 April 2009 04:47

AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK

Senior Member

I've found the problem. When constructing the SELECT statement for the LINK table it was adding an extraneous

AND <link_table>.rdcaccount_id='whatever'

to the WHERE clause. By removing it I got it to select the correct records. The attached file contains the fix.

Attachment: std.table.class.zip
(Size: 47.17KB, Downloaded 1243 times)

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: override auto rdcaccount_id clause in a manually extended SQL [message #2016 is a reply to message #1990]

Thu, 23 April 2009 05:16

gpatti
Messages: 283
Registered: August 2008

Senior Member

Excellent thanks. That seems to work fine now.

Report message to a moderator

Previous Topic:	Use Messages[] in a LIST1 transaction
Next Topic:	Dynamic Task Selection

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

PDF

]

Current Time: Thu Nov 28 18:39:54 EST 2024

Total time taken to generate the page: 0.01594 seconds