2. PHPRunner General questions
  3. [SOLVED]  Before SQLQuery event problems
This topic is locked
[SOLVED]

 Before SQLQuery event problems

 		8/4/2010 10:00:17 AM
PHPRunner General questions
K
 kkiboo author

Hello!
[The service for this product is beyond outstanding, by the way. Thanks for all the help I've recieved thus far.]

Onto the problem...
I am trying to edit the BeforeSQL Query event on the List page so that it ONLY returns records with today's date.
Here is the function:


$strWhereClause = whereAdd($strWhereClause, "RECORD_DATE=to_char(sysdate)");


When I issue this at the SQLPlus command line (Using Oracle 11g):

SQL> select * from MYTABLE where RECORD_DATE=to_char(sysdate);



I get all the records returned from today (too many to list).
Also, I triple checked, in my table under the RECORD_DATE column, todays records have these values:

04-AUG-10

Select sysdate from dual;
SYSDATE

-------

04-AUG-10


Ok with all that said, when I go to my list page, it just says No Records Found. There is no PHP error, no Oracle error, it just says that it didn't find any records.
What's going wrong?
J
 		Jane 8/5/2010

Hi,
it's hard to tell you what's wrong without seeing actual SQL.

Try to turn on debug mode in the include/appsettings.php file (change $dDebug value to 'true'), run this page in browser and ppost resulted SQL query here.
K
 		kkiboo author 8/5/2010

Thanks for the reply.
Here are the SQLs: Plural because I get different results now if I change to_char to to_date.
When using to_char, I get no error but the page returns nothing. The SQL the debug says is:

select count(*) FROM ADT where RECORD_DATE=to_char(sysdate, 'DD-MON-YY')

SELECT AUDIT_ID, AUDITOR, RECORD_DATE, CATEGORY, OV_CONDITION, PHYS_CONDITION, AUDIT_WAS, TEAM, EMPLOYEE, AUDIT_TIME, NOTES, LOT, COACH FROM ADT where RECORD_DATE=to_char(sysdate, 'DD-MON-YY') ORDER BY 1 ASC


When I use to_date, I get an oracle error. Please NOTE this: When I submit this query at the command line in SQLPlus, I get the desired effect (todays records are returned). There is no error. There is only an error when PHPRunner tries to execute this:

select count(*) FROM ADT where RECORD_DATE=to_date(sysdate, 'DD-MON-YY')


Thanks in advance!
A
 		ann 8/6/2010

Hi,
please publish your database creation script or a screenshot of the 'ADT' table.
K
 		kkiboo author 8/6/2010



Hi,
please publish your database creation script or a screenshot of the 'ADT' table.


Can you be a bit more specific as to what you are looking for? Just so I know what parts to screenshot, otherwise it will take several images.
K
 		kkiboo author 8/9/2010

Just checking again to see what information you needed precisely. Thanks....
J
 		Jane 8/10/2010

Hi,
please post structure of your ADT table or RECORD_DATE field type.

Also you can zip and send database creation script to support@xlinesoft.com so we can troubleshoot this error on the test project.
K
 		kkiboo author 8/10/2010



Hi,
please post structure of your ADT table or RECORD_DATE field type.

Also you can zip and send database creation script to support@xlinesoft.com so we can troubleshoot this error on the test project.


Here is a link to an image of my table in SQL Developer. In previous posts I had changed the names of some columns to make it more ambiguous. I assure you I have triple checked all the names of tables and columns.
http://tinyurl.com/2fl4nst
Thanks
J
 		Jane 8/11/2010

Hi,
your record_date(audit_date) field type is date not char.

Try the following code:

$strWhereClause = whereAdd($strWhereClause, "RECORD_DATE=sysdate");



or

$strWhereClause = whereAdd($strWhereClause, "to_char(RECORD_DATE, 'DD-MON-YY')=to_char(sysdate, 'DD-MON-YY')");
K
 		kkiboo author 8/12/2010

Hi,
I input both of your suggestions and I got the same problem both times: There wasn't an error, but it returned 'No Records Found' and for some reason showed empty columns. I have a screenshot here:
https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=51828&image=1&table=forumreplies
K
 		kkiboo author 8/12/2010

If we could even get this work, would it apply to everytime the table is queried? I only want this to apply to the first time the list page loads, no on subsequent searches or whatnot. I assume this is across the board, which is not good.
I tried checking the 'No Record on the First Page' button, and it gave me the same results as above - emptied out my columns, I can't even search now.
Maybe I should try simplifying my request: I'm trying to find a function that will cut down on load times for users. There is no need for the users to see all of the data from the table when they first log in, but as they add and update records, they need to see those. Also, they need to be able to search and view any record.
If there's not an option for this, please let me know. Thanks for all your help - all of you.
K
 		kkiboo author 8/13/2010

Thanks to people who wracked their brains trying to help me with this!
I finally got it to work. Like I said in the post above, though, its not exactly what I needed: I figured out a way to make it work, for anyone who is interested.
Make a copy of the table (another view) and have 1 select only from a certain date, and one select everything. Then just switch between the two as needed.
<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=51862&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />