PDO/OOP Execute SQL file

[Korferer]

I have written a Validation class that checks to see if a file being uploaded to the server meets certain conditions. That works a treat. The next step is to actually upload it to the server and I have an Upload class that can do that. Again, that works perfectly fine.

Once the file uploads, I am passing the $location of that of that file to my DB class. The DB class is full of methods that prepare and then execute strings that are SQL queries that are required in other areas of my application. Nothing I have at the moment is suitable for just running an SQL file so I don't know what to do now..

My procedural code, that works, looks like this;

$dbh2 = new PDO("mysql:host=localhost;dbname=DB360transfer", $login_user, 
$login_password);
$sql = 
file_get_contents($path.$new_file_name);
$qr = $dbh2->exec($sql);

I'm not sure how to replicate this up in a PDO/OOP application.

My DB Class script is attached. The run_from_file code starts on line 98. I've left in the other stuff as I suspect the answer has something to do with using the $this or the self:: - but really I have no idea.

 

So my question is, what is the correct syntax for executing a file in OOP?

 

DB.php

[requinix]

You said you already have stuff in the class that can execute queries, right? Any reason you can't use them?

[Korferer]

You said you already have stuff in the class that can execute queries, right? Any reason you can't use them?

 

I'm not sure. That's part of the problem as I'm new to OOP in general. Almost all the stuff in that Class is someone elses code which I can broadly understand.

 

In any case, I do believe I have solved this isse!

 

In the DB Classes file;

// THE METHOD TO EXECUTE A FILE public function 
run_from_file($location) 
{    try{   $sql = 
file_get_contents($location);   $sth = 
$this->_pdo->prepare($sql);   $sth->execute();    } 
catch(Exception $e){     echo 
$e->getMessage();    
 }   }

I didn't realise that prepare() and execute() are built in functions and would work here.

 

I guess my next problem is, how am I absolutely sure that my table has been updated? I guess I'll have to compare the count of rows before and after this method and see if there is a difference.... That doesn't sound fun.

[Ch0cu3r]

 

 

 guess my next problem is, how am I absolutely sure that my table has been updated?

You'd use PDOStmt::rowCount

Edited June 18, 2014 by Ch0cu3r

[Jacques1]

A lot of the code doesn't really make sense.

 

First of all, you don't seem to understand what exceptions are for and how they work:

 

Catching instances of Exception means that you're catching any exception which could possibly happen, including those you may not even know. This obviously makes no sense. If PHP throws a, say, TotallyOutOfMemory exception, it's probably a good idea to not interfere with it, because there's no way you could handle this.

 

Your “error handling” consists of printing the error message on the screen and then continuing the script. Do you really want everybody to see your database errors in the live system? Do you realize that the default behaviour of exceptions is much smarter than this? By default, exceptions either print the message on the screen (suitable for development) or write it to an error log (suitable for the live system) depending on your PHP configuration. And do you really want to continue the script after the query you need has just blown up?

 

So the try-catch block is not only useless, it's actually harmful. You should remove it. In general, don't catch exceptions unless you can actually handle them. Just let them bubble up so that either a higher level or PHP itself can take care of the problem. If you do catch an exception, always catch a specific class like PDOException.

 

I'm also not sure why you're using a prepared statement. The point of a prepared statement is to pass dynamic values to the query in a secure fashion or speed up repeated execution of the same query. You have neither. So why not just call query() or exec() like before?

 

 

 

I guess my next problem is, how am I absolutely sure that my table has been updated?

 

That depends on what exactly you mean by “updated”.

 

Do you want to check for actual data changes? Why? If the data simply didn't have to be changed, what's wrong with that?

 

Or do you want to know how many rows have been “touched” by the update? That's somewhat more useful.

[Korferer]

Ch0cu3r - Thanks for the PDOStmt::rowCount sugestion. I will look in to that. And actually, Jacques1 makes a really good point. Ideally, no one would be running this application if they didn't have rows to actually add to the table. However, I really should be aiming to make something more "versatile", therefore a "touch" sounds like a good solution too.

 

And you're right Jacques1 - I have NO IDEA how exceptions work. Ha! I have since read a little bit about them and to be honest it still confuses me.

 

I also have no idea what I'm doing using the prepare statment. I knew right from the start it wasn't a great idea because I had nothing to "prepare" really. It was already prepared. But I struggled initially with using just exec(); That was the reason behind my original post.

 

So when I change to contents of my run_from_file() method to;

$sql = file_get_contents($location);
exec($sql);

My code just says it was successful but my database hasn't actually changed when I run this. I assume that's because the exec statement isn't joined to any kind of PDO database connection.... Don't know how to do that yet either.

[kicken]

So when I change to contents of my run_from_file() method to;

$sql = file_get_contents($location);
exec($sql);

My code just says it was successful but my database hasn't actually changed when I run this. I assume that's because the exec statement isn't joined to any kind of PDO database connection.... Don't know how to do that yet either.

 

exec is for executing an external program, not running SQL queries. When Jacques1 mentioned using exec, he was talking about the PDO::exec method. This is a method on your PDO object that is for running a SQL statement which does not return a result set. You were using this method in your procedural example. Just use it in your class as well.