Using dbExpress for Non Visual queries

Dev Shed Forums Sponsor:

December 11th, 2003, 02:27 AM

lloydie-t

Contributing User

Join Date: May 2002

Location: UK

Posts: 522

Time spent in forums: 1 Day 16 h 51 m 42 sec
Reputation Power: 7

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($callno, 0,$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?

December 12th, 2003, 03:28 PM

Scorpions4ever

Banned ;)

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Nov 2001

Location: Glendale, Los Angeles County, California, USA

Posts: 7,589

Time spent in forums: 1 Month 1 Day 22 h 40 m 58 sec
Reputation Power: 1001

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
Puzzle of the Month solved by Keath and KevinADC, superior perl programmers of the month
Looking for a perl job with kick-*** programmers in a well-known NASDAQ listed tech company with branches in the US and Europe? We're hiring. PM me for details. Requirements

December 12th, 2003, 06:04 PM

lloydie-t

Contributing User

Join Date: May 2002

Location: UK

Posts: 522

Time spent in forums: 1 Day 16 h 51 m 42 sec
Reputation Power: 7

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.

December 13th, 2003, 02:57 PM

Scorpions4ever

Banned ;)

Join Date: Nov 2001

Location: Glendale, Los Angeles County, California, USA

Posts: 7,589

Time spent in forums: 1 Month 1 Day 22 h 40 m 58 sec
Reputation Power: 1001

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

December 15th, 2003, 02:16 PM

lloydie-t

Contributing User

Join Date: May 2002

Location: UK

Posts: 522

Time spent in forums: 1 Day 16 h 51 m 42 sec
Reputation Power: 7

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;

December 15th, 2003, 03:28 PM

lloydie-t

Contributing User

Join Date: May 2002

Location: UK

Posts: 522

Time spent in forums: 1 Day 16 h 51 m 42 sec
Reputation Power: 7

sorted it. Used 'continue' outside of the loop which uses 'break'.

December 15th, 2003, 03:34 PM

Scorpions4ever

Banned ;)

Join Date: Nov 2001

Location: Glendale, Los Angeles County, California, USA

Posts: 7,589

Time spent in forums: 1 Month 1 Day 22 h 40 m 58 sec
Reputation Power: 1001

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.

December 15th, 2003, 07:26 PM

lloydie-t

Contributing User

Join Date: May 2002

Location: UK

Posts: 522

Time spent in forums: 1 Day 16 h 51 m 42 sec
Reputation Power: 7

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.

December 15th, 2003, 08:16 PM

Scorpions4ever

Banned ;)

Join Date: Nov 2001

Location: Glendale, Los Angeles County, California, USA

Posts: 7,589

Time spent in forums: 1 Month 1 Day 22 h 40 m 58 sec
Reputation Power: 1001

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.

#10

January 2nd, 2004, 07:30 PM

lloydie-t

Contributing User

Join Date: May 2002

Location: UK

Posts: 522

Time spent in forums: 1 Day 16 h 51 m 42 sec
Reputation Power: 7

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;

#11

January 4th, 2004, 09:04 AM

lloydie-t

Contributing User

Join Date: May 2002

Location: UK

Posts: 522

Time spent in forums: 1 Day 16 h 51 m 42 sec
Reputation Power: 7

Gave up and used the TmySQL component instead

#12

January 5th, 2004, 12:09 PM

Scorpions4ever

Banned ;)

Join Date: Nov 2001

Location: Glendale, Los Angeles County, California, USA

Posts: 7,589

Time spent in forums: 1 Month 1 Day 22 h 40 m 58 sec
Reputation Power: 1001

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;