Using Select & Where clause for mySQL

Posted on 2006-06-02
Last Modified: 2010-04-05
I don't know whats wrong with my code, why my query returns empty always (No record found). I checked my table and it has records, but when ever i run the code below it will return empty.
I suspect that there is something wrong with the "Where.." part. My table has the valid dates inside so i don't believe these will not be seen whenever the query will be executed.

 l_Query := TQuery.Create(fAppointmentScheduling);
 l_Query.Active := False;
 l_Query.DatabaseName := fMain.pSQLAlias;
 l_Query.SQL.Add('Select APNTDATE from '+const_Appointments);
 fromdate  := datetostr(;//+' 00:00:01';
 todate    := datetostr(;//+' 23:59:59';
 l_Query.SQL.Add('where ((DATE(APNTDATE) >= DATE('+fromdate+')) OR (DATE(APNTDATE) <= DATE('+todate+')))');
 DataSource1.DataSet  := l_Query;
Question by:girlswants_me

    Author Comment

    Thanks in advance.
    LVL 4

    Expert Comment

    It could be that you have opened the query before assigning  Datasource.Dataset, try moving this to just after  l_Query.Active := False;
    LVL 11

    Accepted Solution

    Did you try placing the query (select statement) inside the SQL property of a query that has the connection string pointing to a MySQL Database? And try running it from design? Perhaps there is something wrong with your variables that you assign while building the query in runtime.
    So you should make your code showmessage to you before the Query.Active:=true
    Also try to assign the datasource's dataset to the query before Activating the query.
    It maybe your date format from your query. The best way to track your problem is to send the SQL build in runtime to a memobox before executing it and copy/paste it in a Query builder and analyze it from there.
       I strongly believe that your problem comes from the date format . check to see which date format is accepted by your mysql and what date format does your application use.



    Author Comment

    Still failed to display expected records. im 100% sure that there are dates that are in between my "where" clause. Is there something wrong with mySQL ver 5.1? i tried it manually in the mysql console and it does not return the records also.

    Anybody can help me solve my problem????
    LVL 4

    Expert Comment

    Have you tried stepping through the code in debug mode?

    First make sure that in the Project/Options/Compiler/Debugging panel all options are checked.

    If you set the screen layout to Debug mode instead of Standard and then set a breakpoint on the
    l_Query.SQL.Add('Select APNTDATE from '+const_Appointments); by clicking the mouse cursor
    on that line. you can then run the program, when it reaches that block of code execution is halted until you step through the code line by line by clicking on the 'Trace Into' icon or by pressing F7.

    After a line of code has been executed by pressing f7, you can place the mouse cursor over a variable in that line and check its current value which will be shown in a popup.

    Local variable values are displayed in a panel.

    This should enable you to check the values loaded in your where clause and see which one is playing up.
    LVL 10

    Assisted Solution

    As calinutz said, the problem is with dateformat. MySQL understands date time in 'YYYY-MM-DD HH:MM:SS' format only.

    So, in SQL instead of simply using
    datetostr(;//+' 00:00:01';

    FormatDateTime('yyyy-mm-dd hh:mm:ss',;

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
    In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now