Running out of Memory

English Programming/Scripting
#1

Hi All,

Running the following in PHP on a rather large database table and getting the message that I’m running out of memory. I know I could just allocate more memory to PHP but I’m wondering if there’s a more elegant solution?

What I’m going for is to pull the data out of an MSSQL database and insert it into a MySQL database running on a different machine.


function get_Transaction_Tender() {
$sql = "
SELECT 
    *
FROM
    Transaction_Tender
";

$db = connect_to_wp();
$q = send_query($sql, $db);

$db2 = connect_mysql();
while ($q->fetchInto($row)){
    $sql2 = "INSERT INTO prism_transactions.Transaction_Tender VALUES ( '$row[0]', '$row[1]', '$row[2]', '$row[3]', '$row[4]', '$row[5]', '$row[6]', '$row[7]', '$row[8]', '$row[9]', '$row[10]', '$row[11]', '$row[12]', '$row[13]', '$row[14]', '$row[15]' )";
    //print "
Inserting	$row[0]	$row[1]	$row[2]	$row[3]	$row[4]	$row[5]	$row[6]	$row[7]	$row[8]	$row[9]	$row[10]	$row[11]	$row[12]	$row[13]	$row[14]	$row[15] into local Transaction_Types";
    send_query($sql2, $db2);
} // end while
disconnect($db);
disconnect($db2);
return 0;
} // end function definition for get_Transaction_Tender

Where the function connect_to_wp() is defined as:


function connect_to_wp() {
    require_once('DB.php');
    $db = DB::connect("odbc://XXXXXXXX:XXXXXXXXX@/Winprism");

    // ...and check for errors.
    if (DB::iserror($db)) {
        die($db->getDebugInfo());
    }
    return $db;
}

The function connect_mysql() is:


function connect_mysql() {
    require_once('DB.php');
    $db2 = DB::connect("mysql://XXXXXX:XXXXXX@localhost/prism_transactions");

    // and error check this connection
    if (DB::iserror($db2)) {
        die($db2->getDebugInfo());
    }
    return $db2;
} // end function definition for connect_mysql()

And, the function send_query($sql, $db) is defined as:


function send_query($sqlText, $db) {

    // prepare the query
    $sql = $db->prepare("$sqlText");

    if (PEAR::isError($sql)) {
            die($sql->getDebugInfo());
    }
    // and execute it on the database at $db
    $q =& $db->execute($sql);
    if (PEAR::isError($q)) {
            die($q->getDebugInfo());
    }
    return $q;
}

Any thoughts?

Thanks in advance.
kev.

#2

The problem is the way you do your query against MySQL, you are fetching all the results at once. If you want to avoid running out of memory on large result sets, you should use an interface that returns the result one at a time, e.g.

PHP: mysqli::use_result - Manual

BTW, you didn’t do an escape_string on the values that you insert. You will run into problems with values containing the single quote. Either use escape_string or use a prepared query.

#3

Thanks I’ll have a go at that. I wasn’t too concerned about escaping the strings as they’d already be escaped when they went into the MSSQL database but you’re right I should be doing it anyway. :slight_smile:

#4

You’re missing the point. It’s not stored in escaped form in MSSQL. You will run into problems if one of your strings contains a single quote, e.g. “four o’clock”. You will end up with a query string that contains a syntax error if you don’t escape the single quote. Worse still, if the string is of a particular form, e.g. “four’ and name='fred”, you might end up with a query string that is syntactically correct but doesn’t do what you want. That’s the principle behind how SQL injection attacks work.

#5

Ahh. So if I do something like:


	$row[0] = mysql_real_escape_string($row[0]);
	$row[1] = mysql_real_escape_string($row[1]);
	$row[2] = mysql_real_escape_string($row[2]);
	$row[3] = mysql_real_escape_string($row[3]);
	$row[4] = mysql_real_escape_string($row[4]);
	$row[5] = mysql_real_escape_string($row[5]);
	$row[6] = mysql_real_escape_string($row[6]);
	$row[7] = mysql_real_escape_string($row[7]);
	$row[8] = mysql_real_escape_string($row[8]);
	$row[9] = mysql_real_escape_string($row[9]);
	$row[10] = mysql_real_escape_string($row[10]);
	$row[11] = mysql_real_escape_string($row[11]);
	$row[12] = mysql_real_escape_string($row[12]);
	$row[13] = mysql_real_escape_string($row[13]);
	$row[14] = mysql_real_escape_string($row[14]);
	$row[15] = mysql_real_escape_string($row[15]);

before the:


$sql2 = "INSERT INTO prism_transactions.Transaction_Tender VALUES ( '$row[0]', '$row[1]', '$row[2]', '$row[3]', '$row[4]', '$row[5]', '$row[6]', '$row[7]', '$row[8]', '$row[9]', '$row[10]', '$row[11]', '$row[12]', '$row[13]', '$row[14]', '$row[15]' )";

That should do the trick?

#6

Yes, though it’s a rather long-winded way to do it. Personally I would use a loop to build the string, or prepared queries if available.

#7

I’ve been told I can tend toward the verbose on occasion :slight_smile:

I thought I was doing a prepare / execute in the send_query($sql, $db) function above?

#8

It’s the placeholder feature of prepared queries that you want. Something like what ADODB and other interface libraries provide. E.g.

$result = $db->query(“INSERT INTO widgets VALUES(?,?,?)”, array(“splunger”, “4’ diameter”, “$100.00”));