ADODB_Exception when page refreshes after a Table UPDATE has beensubmitted

Using D4PHP V2.0.0.1041.

Greeting all,
I'm playing with my first mySQL related php application and receive the 'ADODB_Exception' message below after submitting a page.

The page has a memo control for displaying the contents of a logfile (works ok), a dbGrid control for displaying a mySQL Table (dragged and dropped from Database Explorer), three Edit boxes, a blank Label object for status messages and two buttons (Log Refresh & Update Database).

When the page first loads, the dbGrid is populated with the table contents ok (text alignment Properties don't work, Heading colours & alignment Properties don't work either), I then enter new Record details in the three Edit boxes and press the "Update Database" button. The mySQL Database INSERT/UPDATE works ok (not using the MySQLQuery object of this, using hand written code) but when the page refreshes, the dbGrid area is replaced with:

Application raised an exception class ADODB_Exception with message 'mysql error: [: ] in EXECUTE("SHOW COLUMNS FROM `Addresses`")'

A sniffer trace of the mySQL connection shows not such command being sent? What is sent is a "SHOW INDEX FROM ADDRESSES" which is successful. Also, it may just be the way the error message is formatted, but " SHOW COLUMNS FROM `Addresses` " isn't valid, " SHOW COLUMNS FROM Addresses " is.

If I change my code so that the dbGrid related objects retrieve data from my production mySQL server and does the Updates to my test mySQL server, the problem goes away so I asume it's got something to do with using two different methods of communicating with the mySQL server that I using.... but I don't know why!

My PHP mySQL code is as follows:

#
# User input has been validated so now it's time to do a mySQL insert
# If the user is trying to insert a mask (primary key) value that already exists (1062 duplicate error),
# then change the INSERT into an UPDATE.
#
if ($connection = mysql_connect("$mySQLserverName","$userid","$password")) {
if ($db = mysql_select_db("MRTG", $connection)) {
$query = "INSERT INTO Addresses (Mask, Town, State, Text) VALUE ('$device_Name','$town_Name', '$state_Name','$device_Address')";
$result = mysql_query($query);
if (!$result) {
if (mysql_errno() == '1062') {
$query = "UPDATE Addresses SET Town = '$town_Name', State = '$state_Name', Text = '$device_Address' WHERE Mask = '$device_Name'";
$result = mysql_query($query);
if (!$result) {
$this->statusMessage->Caption = "mySQL UPDATE error: " . mysql_error() . " Error Number: " . mysql_errno();
} else { $this->statusMessage->Caption = "UPDATE for $device_Name was successful"; }
} else { $this->statusMessage->Caption = "mySQL INSERT error: " . mysql_error() . " Error Number: " . mysql_errno(); }
}
else { $this->statusMessage->Caption = "mySQL INSERT for $device_Name was successful. " . mysql_error(); }
}
else { $this->statusMessage->Caption = "Connect to DataBase MRTG failed"; }
mysql_close($connection);
}
else { $this->statusMessage->Caption = "Connect request to mySQL server failed"; }

If anyone has any examples of how to use the mySQL* D4PHP objects for doing simple selects/inserts & updates, that may help.

Also, anyone else noticed Property problems with the dbGrid object?

Thanks in advance,
Neil M

[405hp]

I don't know if this will make any difference but I never put my own connections in.

When you already have the vcl database connected you can just run your code starting at

$query =
$result = mysql_query($query);

and it will use the vcl connection.

> {quote:title=Delphi-PHP Forums wrote:}{quote}
> I don't know if this will make any difference but I never put my own
> connections in.
>
> When you already have the vcl database connected you can just run your
> code starting at
>
> $query =
> $result = mysql_query($query);
>
> and it will use the vcl connection.
>
>
> --
> 405hp
> ------------------------------------------------------------------------
> 405hp's Profile: 1089
> View this thread: ADODB_Exception when page refreshes after a Table UPDATE has beensubmitted

Thanks for the reply.
Interesting..... yes, this stops the error........

I restored my SQL code and commented out the "mysql_close($connection);" statement.... no more errors
I re-included the "mysql_close($connection);" and the error returns, so now I'm thinking that I'm actually closing the connection created by the vcl db object. So, just in case I accidently used the same variable name ($connection) as the vcl db object does, I changed my code to use "$connection2" instead of "$connection". Error still occurs but stops if I comment out the "mysql_close" statement again.

I've tried disabling the vcl db objects "connected" property to see how my code runs on it's own. The dbGrid is still being populated by the Table query attached to the vcl db object??

Thank's for the suggestion, I'll explore the 'connection closed" path some more.

Regards,
Neil M

[405hp]

From this thread (last post) dbgrid problem

It looks like they corrected a behavior that might solve your problem.

> {quote:title=Delphi-PHP Forums wrote:}{quote}
> From this thread (last post)
> dbgrid problem
>
> It looks like they corrected a behavior that might solve your problem.
>
>
> --
> 405hp
> ------------------------------------------------------------------------
> 405hp's Profile: 1089
> View this thread: ADODB_Exception when page refreshes after a Table UPDATE has beensubmitted


Yes.... looks like a direct hit on my problem Thanks for the info, I was just about ready to throw the vcl db stuff away and write code to do it all (still might).

Many thanks,
Neil M