#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13

    Using dbExpress for Non Visual queries


    I've tried and failed using the TMySql component for accessing Mysql Database. Although I got it to complie, it crashed. Coupled with the fact that there are nmo documents for it I have given up on it to use dbExpress instead. dbExpress seems to be more of a visual component which I am not quite getting. Can someone point me in the right direction based on the PHP code I wrote.

    PHP Code:
    <?php
            
    include('pageconnect.php');
            
    $connection = @mysql_connect($host$user$pass) or die ("Unable to connect to database");
            
    mysql_select_db($db) or die ("Unable to select database: $db ");
            
    for (
    $i strlen($callno); $i != 0$i--) { 
            
    $areacode substr($callno0,$i); 
            
    $query "SELECT *  
    FROM stdcodes 
    WHERE areacode = '
    $areacode' ";
    $queryResult mysql_query($query);
    $numrows =mysql_num_rows($queryResult);

    if(
    $numrows >0)  {
            break;
        }
    }
    $query "SELECT *  
    FROM stdcodes 
    WHERE areacode = '
    $areacode' ";
    $queryResult mysql_query($query);

    if (
    $queryResult) {
    while(
    $row =mysql_Fetch_array($queryResult)) {

    echo 
    "the country for $row[areacode] is $row[country];
           }
        } else {
            echo 
    "Query failed.  SQL=$selectresult  error=".mysql_error();
        }                
            
    ?>
    WhaDaYaTink?
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,625
    Rep Power
    4247
    What is TMySQL -- a third party component? Also, query objects are non-visual components so I'm not sure what you mean by dbExpress being a visual component. Do you not have a form on this project?
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13
    TmySQL is a third party component. Which while it can do the job has has no documentaion and the newsgroup seems a bit quite, so I gave up.
    Yes there is a form for this project but it will not use any of the data aware components. All I want to do is to compare some numbers (froma text file) agaisnt the database and then based on that insert some calculated results into a table.
  6. #4
  7. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,625
    Rep Power
    4247
    Here's how I would do it:
    1. Drop a TSQLConnection component on the form. Set the ConnectionName to MySQLConnection and set LoginPrompt to False. Set the Params property to the connection property and then set Connected to True.

    2. Drop a TSQLQuery component on the form and set its SQLConnection property to the TSQLConnection component in step 1. Then click on the SQL property and type:
    SELECT *
    FROM stdcodes
    WHERE areacode = :areacode

    Then click on the Params property and set the DataType and ParamType properties of the areacode param.

    Then, in your for loop:
    Code:
    for i := .... 
    begin
       .... 
       .... do something to parse the string
       .....
        with SQLQuery1 do
        begin
             Close;
             ParamByName('areacode').Value := somevalue;
             Open;
             // Loop thru the result set.
             while not Eof do 
             begin
                 // Get the values of individual fields in the result set.
                 x := FieldByName('country').AsString;
                 y := FieldByName('areacode').AsInteger;
                 Next;
             end;
        end;
    end;
    Hope this helps
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13
    Hi Scorpions,
    I have got a bit further, but now need to sort another problem I am getting with this app. The following now goes through a for loop until a match is met. But the break line seems to be causing an error and the while loop does not continue to go though each line of the text file. Can you help?
    The error I get is:
    Code:
    Project datacollex1.exe raised exception class EInOutError with message 'I/O error 104. Process stopped.
    this is the code I am working on which uses the Zeos SQL Component.
    Code:
    procedure TMainForm.CdrButtonOKClick(Sender: TObject);
    var
    i : integer;
    areacode : string;
    begin
    Assignfile(CdrText, CdrImportEdit.Text);
    Reset(CdrText);
    while not EOF(CdrText) do begin //error point seems to be here
    ReadLn (CdrText, Buffer1);
    ExtVar := PhoneNum(buffer1);
    for i := strlen(Pchar(ExtVar)) downto 1 do
    begin
    areacode := copy(ExtVar,0,i);
    ZReadOnlyQuery1.sql.Clear;
    ZReadOnlyQuery1.Sql.Add('SELECT * FROM stdcodes WHERE areacode = :AREACODE');
    ZReadOnlyQuery1.ParamByName('AREACODE').AsString := areacode;
    ZReadOnlyQuery1.open;
    if  ZReadOnlyQuery1.RecordCount > 0 then
    break;
    edit1.Text := inttostr(i);
    end;
    CloseFile(CdrText);
    end;
    end;
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13
    sorted it. Used 'continue' outside of the loop which uses 'break'.
  12. #7
  13. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,625
    Rep Power
    4247
    Two things here:
    1. It's probably better to use Eof instead of RecordCount, because RecordCount isn't valid if the recordset is cached, so do this:
    if not ZReadOnlyQuery1.Eof then
    break;

    Secondly, if you have a query open already and attempt to open it a second time, you will have an error thrown. So you might want to put a Close up there first.
    Code:
    for i := strlen(Pchar(ExtVar)) downto 1 do
    begin
    areacode := copy(ExtVar,0,i);
    ZReadOnlyQuery1.Close;
    ZReadOnlyQuery1.sql.Clear;
    ZReadOnlyQuery1.Sql.Add('SELECT * FROM stdcodes WHERE areacode = :AREACODE');
    Also, it is not necessary to keep clearing and readding the SQL statement each time around. You can move those two statements to the top of the procedure. All you need to do in the loop is call Close, ParamByName and Open.

    Also, you should insert a breakpoint in your code and see if the exception gets thrown on the first iteration of the loop or later on and try to locate the exact statement that it happens on. That will help in solving this issue.
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13
    Scorpion,
    I think I had solved the problem I was having just before you posted your reply, But I am interest in what you mean by moving the SQL statements to the top of the procedure.
    Please bear in mind that the 'for' loop is checking a number against the database and if there is no match one digit gets knock of until the loop finds a match and inserts a break, at which point the 'while' loop continues to look at the next line in a text file and starts another 'for' loop and so on.

    Thanx for you help.
  16. #9
  17. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,625
    Rep Power
    4247
    The reason I said move it to the top of the function is for optimization purposes. In your case, since you're using a parametrized query, the SQL statement doesn't change at all. The only thing that changes is the value of the parameter. This is roughly the sequence of internal steps that happens for most SQL engines (now we're going behind the object):
    1. Application calls the SQL engine with a SQL statement.
    2. SQL engine parses the statement and converts it to internal byte code. It then returns a handle back to the application. This step is called preparing the query and can take a fairly decent amount of computing cycles. From now onwards, any reference to the SQL statement is done via this handle.
    3. Application passes the handle along with parameters back to the SQL engine and tells it to execute the statement associated with the handle, using the passed parameters.
    4. SQL engine executes the prepared query with the passed params and returns the results back to the application.

    These steps happen on pretty much any SQL engine, though the actual functions used are different for each engine. This is the sort of details that Delphi's components hide you from.

    Clearly, if you are calling the same SQL statement within a loop, it is necessary to execute steps 1 and 2 only once. All you need to do is execute steps 3 and 4 multiple times, by passing a different parameter each time. This makes things run faster also, since step 2 isn't always a trivial step (depends on engine and actual statement).

    In your current code, you're executing steps 1 - 4 each time you loop through a line in the file, when you could have just executed steps 1 and 2 only once. Thus your code could be rearranged like this:
    Code:
    procedure TMainForm.CdrButtonOKClick(Sender: TObject);
    var
    i : integer;
    areacode : string;
    begin
    
    ZReadOnlyQuery1.sql.Clear;
    ZReadOnlyQuery1.Sql.Add('SELECT * FROM stdcodes WHERE areacode = :AREACODE');
    ZReadOnlyQuery1.Prepare; 
    // Optional, since Delphi's object automatically calls Prepare before Open, the first time around,
    // if you haven't explicitly done it yourself
    
    Assignfile(CdrText, CdrImportEdit.Text);
    Reset(CdrText);
    while not EOF(CdrText) do begin //error point seems to be here
    ReadLn (CdrText, Buffer1);
    ExtVar := PhoneNum(buffer1);
    for i := strlen(Pchar(ExtVar)) downto 1 do
    begin
    areacode := copy(ExtVar,0,i);
    
    ZReadOnlyQuery1.Close;
    ZReadOnlyQuery1.ParamByName('AREACODE').AsString := areacode;
    ZReadOnlyQuery1.open;
    
    if  ZReadOnlyQuery1.RecordCount > 0 then
    break;
    edit1.Text := inttostr(i);
    end;
    CloseFile(CdrText);
    end;
    Hope this helps
    Last edited by Scorpions4ever; December 15th, 2003 at 08:23 PM.
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13
    Scorpion,
    Only just got round to playing with that as I am doing some php stuff as well. I need help with one more thing. I need to assign the result of the query to a var. Can I do this without additional components? If not, which ones do I need and how do I use.

    code so far.
    Code:
    begin
        CdrButtonOK.Enabled := False;
        CdrImportEdit.Enabled := False;
    ZReadOnlyQuery1.sql.Clear;
    ZReadOnlyQuery1.Sql.Add('SELECT * FROM stdcodes WHERE areacode = :AREACODE');
    
    Assignfile(CdrText, CdrImportEdit.Text);
    Reset(CdrText);
    while not EOF(CdrText) do begin
    ReadLn (CdrText, Buffer1);
    ExtVar := PhoneNum(buffer1);
    Exten := ExtNum(buffer1);
    for i := strlen(Pchar(ExtVar)) downto 1 do
    begin
    areacode := copy(ExtVar,0,i);
    ZReadOnlyQuery1.Close;
    ZReadOnlyQuery1.ParamByName('AREACODE').AsString := areacode;
    ZReadOnlyQuery1.open;
    if  ZReadOnlyQuery1.RecordCount > 0 then
    break;
    band := ZReadOnlyQuery1.result; I need to create the var here 
    end;
    continue;
    CloseFile(CdrText);
    end;
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13
    Gave up and used the TmySQL component instead
  22. #12
  23. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,625
    Rep Power
    4247
    Sorry, I was on vacation for a few days. Looks like you've got the problem resolved, but just out of curiosity, what exactly do you mean by "result" of a query. Does that mean that the routine found a line which is not in the DB already? If so, why not just do something like this:
    Code:
    begin
        CdrButtonOK.Enabled := False;
        CdrImportEdit.Enabled := False;
        band := false;
    ... your loops here ....
        // If we have success, flag it here.
        if not band then
             band := true;
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2002
    Location
    UK
    Posts
    563
    Rep Power
    13
    When I get a result from the database I wished to assign these to a Var. The problem I was having was even getting the results from the Database.
    All academic now as I am using the TmySQL component, which my Non-visual mind understands more than the other SQL components. I am stuck on other things, but will post new question.

IMN logo majestic logo threadwatch logo seochat tools logo