Hello all,

I am working on a project where I am doing an ETL of data from a Firebird database, using the iBase functions of PHP.

I'm doing this:

$dbh = ibase_connect($host, $dbuser, $dbpass);
$fbquery="some really complex query";
$p_sql = ibase_prepare($fbquery);
$fbresult = ibase_execute($p_sql);
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {die("Connection failed: " . $conn->connect_error);}
while($fbrow = ibase_fetch_assoc($fbresult)){
    $insert="INSERT INTO table (fields) VALUES ('";
    $foo=0;
    foreach($fbrow as $fbvalue) {
	if ($foo==1) { $insert.=", "; }
	$insert.= "'" . $fbvalue . "'";
	$foo=1;
    }
    $insert.=")";
    echo ".";
    if ($conn->query($insert) === TRUE) {$c++;} 
}
echo $c . " records added. <br />";

Which should put all results from the ibase query into a mysql table, transformed into a format where I can use for my application.

My issue is that when I run the initial query directly against the firebird database (using dbvisualizer), I get 147450 records. If I use ODBC and pull this same query into an Excel file, I get the same number of results.

However, when my loop runs, I'm only getting 142516 records. Somehow, I'm losing 4900ish records.

I've looked for an ibase_count function, but the only thing I've found is ibase_affected_rows, which doesn't apply to select statements.

Am I making some stupid error, or is something else wrong?

Any help would be greatly appreciated!

    I'd start by sticking in a else block after the last if block to error_log() or just echo out the last mysql error when the query fails.

    if ($conn->query($insert) === TRUE) {
    $c++;
}
else {
    error_log($conn->error.PHP_EOL.$insert); 
}

      Just a suggestion: the process might go smoother if you use a prepared statement for your MySQLi INSERTs.

        NogDog;11047321 wrote:

        I'd start by sticking in a else block after the last if block to error_log() or just echo out the last mysql error when the query fails.

        if ($conn->query($insert) === TRUE) {
    $c++;
}
else {
    error_log($conn->error.PHP_EOL.$insert); 
}

        SO, I did this, but didn't know where to go look at the error, so I took a slightly different approach and did:

        if ($conn->query($insert) === TRUE) {$c++;} 
			else {
				echo $insert . "<br />"; 
			}

        Which showed me that someone in our engineering department used an apostrophe in some of our descriptions, thereby breaking the string I was inserting.

        A simple addition of

        $fbvalue = mysqli_real_escape_string($conn,$fbvalue);

        resolved the issue. Thank you for putting me on the right path.

          Weedpacket;11047323 wrote:

          Just a suggestion: the process might go smoother if you use a prepared statement for your MySQLi INSERTs.

          What is the advantage of the prepared statement? Is it just safer against injection, or is there a performance benefit as well?

            davenok wrote:

            What is the advantage of the prepared statement? Is it just safer against injection, or is there a performance benefit as well?

            It tends to be safer because the SQL statement is handled separately from the possibly unsafe data, whereas concatenating escaped data to build the SQL statement poses a higher risk of a mistake. There can be a performance benefit in the event that you are executing the same prepared statement over and over again with different values bound to the parameters, which happens to be what you can do here.

              As laserlight said; when using a prepared statement the driver can appropriately escape values automatically.

                Write a Reply...