next »

[perkiset]

Hey all -

I have updated my dbConnection class to use mysqli_ functions rather than the old mysql_ funcs. It's a straight retrofit - all functions behave exactly the same as the original class. But more importantly (and the reason for this upgrade) is the addition of a new method, multiQuery. Works like this: pass a series of SQL queries separated by semicolons to multiQuery (just like you'd pass a single statement to query) and it executes all of them via mysqli_multi_query. It's way fast and clean. Results come back in a 2 dimensional array: the first index is based on the order of statements in the multi query, the second dimension is the results of that query.

So, if you did this:

Code:

$sql = <<<SQL
select * from contacts limit 3;
select * from transactions limit 4;
select * from memos limit 2;
SQL;

$answerArray = $db->multiQuery($theAboveSQL);

your $answerArray would look something like this:

• $answerArray[0][0] // contact data record #1
• $answerArray[0][1] // contact data record #2
• $answerArray[0][2] // contact data record #3
• $answerArray[1][0] // transaction data record #1
• $answerArray[1][2] // transaction data record #2
• $answerArray[1][3] // transaction data record #3
• $answerArray[1][4] // transaction data record #4
• $answerArray[2][0] // memo record #1
• $answerArray[2][1] // memo record #2

multiQuery can be called with an optional second parameter - the array you'd like to have filled with the answers. So you could do any of the following:

• $newArray = $db->multiQuery($sql);
• $myArray = new Array();
• $myArray = $db->multiQuery($sql, $myArray);
• $db->multiQuery($sql, $myArray);

It's handy and fast. How fast you ask? Well, I'm writing a test case for my own purposes ATM and will post my results.

Enjoy!

/perk

Code:

<?php

class dbConnection
{
var $__connected;
var $__host;
var $__user;
var $__password;
var $__database;
var $__myConnection;
var $__lastQuery;
var $dataSet;
var $row;
var $silent;

function dbConnection($host, $user, $password, $database, $doConnect=false)
{
$this->__host = $host;
$this->__user = $user;
$this->__password = $password;
$this->__database = $database;
$this->__connected = false;
$this->silent = false;
if ($doConnect) { $this->connect(); }
}

function &cloneConnection()
{
if (!$this->__connected) { $this->connect(); }
return new dbConnection($this->__host, $this->__user, $this->__password, $this->__database);
}
function close()
{
if($this->__connected) { mysqli_close($this->__myConnection); }
}
function connect()
{
$this->__myConnection = mysqli_connect($this->__host, $this->__user, $this->__password, $this->__database);
if (!$this->__myConnection) { die('class DBConnection2 cannot connect'); }
$this->__connected = true;
}
function connected() { return $this->__connected; }
function error()
{
if (!$this->__connected) { return ''; }
return mysqli_error($this->__myConnection);
}
function fetchArray()
{
$this->row = mysqli_fetch_array($this->dataSet);
if ($this->row) { return true; } else { return false; }
}
function fetchAssoc()
{
$this->row = mysqli_fetch_assoc($this->dataSet);
if ($this->row) { return true; } else { return false; }
}
function fetchRow()
{
$this->row = @mysqli_fetch_row($this->dataSet);
if ($this->row) { return true; } else { return false; }
}

function fieldCount() { return mysqli_num_fields($this->dataSet); }
function fieldName($idx) { throw new Exception('fieldName function has not been upgraded'); } // return mysql_field_name($this->dataSet, $idx);
function lastInsertID() { return $this->singleAnswer('select LAST_INSERT_ID(' . $this__myConnection . ')'); }
function lastQuery() { return $this->__lastQuery; }

function &multiQuery($queryStr, &$outArr=false)
{
if (!$outArr) $outArr = array();
if (!$this->__connected) { $this->connect(); }
$this->__lastQuery = $queryStr;
$this->__lastError = '';
$ptr = 0;
if(mysqli_multi_query($this->__myConnection, $queryStr))
{
do 
{
if ($res = mysqli_store_result($this->__myConnection))
{
while($row = mysqli_fetch_assoc($res)) $outArr[$ptr][] = $row;
$res->close();
} else $outArr[] = "Store/Fetch problem: {$this->__myConnection->error}";
$ptr++;
} while(mysqli_next_result($this->__myConnection));
} else $outArr[] = "Query problem: {$this->__myConnection->error}";

return $outArr;
}

function query($queryStr, $ignore=false)
{
if (!$this->__connected) { $this->connect(); }
$this->__lastQuery = $queryStr;
$this->__lastError = '';
$this->dataSet = mysqli_query($this->__myConnection, $queryStr) or ($this->__lastError = mysql_error($this->__myConnection));
if (($this->__lastError > ' ') && (!$ignore))
{
if (!$this->silent)
print "MySQL Error on query('{$this->__lastQuery}') - {$this->__lastError}";
}
}

function rowCount() { return mysqli_num_rows($this->dataSet); }
function rowToXML(&$xml)
{
$max = mysql_num_fields($this->dataSet);
for ($i=0; $i<$max; $i++)
{
$xml->addChild(mysql_field_name($this->dataSet, $i), $this->row[$i], false);
}
}
function seek($rowNum) { return mysqli_data_seek($this->dataSet, $rowNum); }
function selectDB($dbName)
{
if (!$this->__connected) { $this->connect(); }
mysqli_select_db($dbName, $this->__myConnection);
}
function singleAnswer($queryStr)
{
if (!$this->__connected) { $this->connect(); }
$this->query($queryStr);
$this->fetchRow();
return $this->row[0];
}
function singleArray($queryStr)
{
if (!$this->__connected) { $this->connect(); }
$this->query($queryStr);
$this->fetchRow($this->__myConnection);
return $this->row;
}
function singleAssoc($queryStr)
{
if (!$this->__connected) { $this->connect(); }
$this->query($queryStr);
$this->row = mysqli_fetch_assoc($this->dataSet);
return $this->row;
}
}

?>

[jammaster82]

Awesome, I can think of a few places this would be handy.

Code:

So, if you did this:

$sql = <<<SQL
select * from contacts limit 3;
select * from transactions limit 4;
select * from memos limit 2;
SQL;

$answerArray = $db->multiQuery($theAboveSQL);
your $answerArray would look something like this:

    * $answerArray[0][0] // contact data record #1
    * $answerArray[0][1] // contact data record #2
    * $answerArray[0][2] // contact data record #3
    * $answerArray[1][0] // transaction data record #1
    * $answerArray[1][2] // transaction data record #2
    * $answerArray[1][3] // transaction data record #3
    * $answerArray[1][4] // transaction data record #4
    * $answerArray[2][0] // memo record #1
    * $answerArray[2][1] // memo record #2

• I would know to look for three records on elements

Code:

$answerArray[0][0]
$answerArray[0][1]
$answerArray[0][2]

and four on the second query, etc .... what if the program didnt
know how many records were coming back how would you address
each query and the individual results and how would you count the amount
of records that came back from each query?

• also are

Code:

$answerArray[0][0]
$answerArray[0][1]
$answerArray[0][2]

each entire records that might contain multiple fields?
how would you count those fields and iterate through them
without knowledge of how many fields there were or how many
records came back?

[perkiset]

Consider this code. It will bake your noodle a bit, but there are some interesting mechanics presented here:

Code:

<?php

$sql = <<<SQL
select * from contacts;
select * from addresses;
select * from memos;
SQL;

$result = $db->multiQuery($sql);

foreach($result as $idx=>$resultSet)
{
echo "$idx) {$sql[$idx]}\n";
foreach($resultSet as $rows)
{
print_r($rows);
}
}

?>

[isthisthingon]

Nice work Perk!  This would be great to call from a worker thread.  In Apex it would look something like this:

Code:

class PerksMultiFun
{
   public List<List<String>> multiReturnArray {get; set;}

   public PerksMultiFun()
   {
      // Initialize array
      multiReturnArray = new List<List<String>>();
   }

   @future // Run in separate thread and return immediately
   public static void multiFun(String theMultiQuery)
   {
      // Assuming multiQuery was native Apex
      multiReturnArray = multiQuery(theMultiQuery);

      // If you exposed this as a RESTful or web service,
      // you could call it directly from Apex.  Wanna try this
      // someday??  I'd love to package up your framework and
      // list it on the AppExchange as a composite app.  Even
      // a subset would be wicked cool.
   }
}

Then perhaps the Apex code that calls the class:

Code:

private List<List<String>> doMultiQuery(String qry)
{
   PerksMultiFun pm = new PerksMultiFun();
   return pm.multiFun(qry);
   // Or return pm.multiReturnArray after the multiFun call,
   // either way.
}

public PageReference doIt()
{
   // bodyStr is defined in the class and grabs the query(s) from
   // VF page.  User enters this and launches query with button
   asyncResults = doMultiQuery(bodyStr);
}

And the Visualforce page with the do-it button...

Code:

<apex:page controller="PerksMultiFun">
<apex:sectionHeader title="Perk's MultiQuery Spans the Clouds!" subtitle="bitches"/>
   <apex:form >
      <apex:pageBlock id="theBlock">
         <apex:pageBlockButtons location="bottom">
            <apex:commandButton action="{!doIt}" value="Do it!" rerender="results"/>
         </apex:pageBlockButtons>
         <apex:outputPanel id="input">
            <apex:pageBlockSection columns="1">
               <apex:inputTextarea id="querystring" value="{!bodyStr}" rows="5" style="width: 100%"/>
            </apex:pageBlockSection>
         </apex:outputPanel>
         <apex:outputPanel id="results">
            <apex:pageBlockSection columns="1">
               <apex:inputTextarea id="queryresults" value="{!asyncResults}" rows="10" style="width: 100%"/>
            </apex:pageBlockSection>
         </apex:outputPanel>
      </apex:pageBlock>
   </apex:form>
</apex:page>

 

Google Apps? CacheApps!

[perkiset]

Ah... at first I was like, WTF is going on ... then I remembered that Apex is actually "behind the wall" of SalesForce, so it doesn't need a database connection per se - it's already connected to the "database" that is 'force.

@ the Visual Force stuff - is that a specialized markup to exist *within* an HTML page, or is it stand-alone? (I assume if it's to be used within, you'd need some special type of processing to instantiate/execute it all...)

[isthisthingon]

Correct, Apex is in the Force.com cloud along with the Visualforce markup.  They released Force.com Sites which allows full access to their platform and doesn't require the world to login to Salesforce (ie - no license fees for anyone you choose to authenticate - or not - to visit your pages).  Sites, like the rest of their cloud, is completely hosted on their servers.  The Force.com platform is 100% pure cloud computing.  There's a tremendous amount of support, tools, documentation, etc., to provide complete integration with the rest of the cloud world (such as..... Perk's cloud??)  The Force.com API exposes the whole banana through web services and their Metadata API describes it.  You can roll your own web services to expose custom apps to other clouds as well.  Apex provides a WSDL generator for consumption and supports RESTful (HTTP) callouts as well.

This is how Eclipse blends so nicely with the Force.  Using metadata web services under the hood, Eclipse allows you to create as many "orgs" (virtual organizations) as you want and keep them organized in your library.  Code is also stored locally and verified in their cloud upon saving to SF.  Syntactical errors are caught when saving and Test classes are executed in the cloud as well (through Eclipse).

I've totally transitioned to test-driven development.  It's truly the way to go IMO.  The larger the code base, the more of a life saver this approach is.  Test-driven development is strictly enforced if you want to get published on the AppExchange.  Speaking of, here's a link to that app I coded: http://sites.force.com/appexchange/listingDetail?listingId=a0N300000016gkMEAQ.  It's called SaaS TEa (Time and Expense application).  It's totally native force but I'm in the middle of a composite app (mentioned it this weekend) that's baking my possibilities noodle   

  Also, may I take you up on your offer for a Force.com discussion group if you think it would be of interest?  One thing that occurred to me is that the principles of test-driven development apply to all languages (excluding markup).  I think this would be a valuable subsection and I'd be happy to write what I've learned on the subject.  Let me know and I'll make it so 

[perkiset]

Alrighty, we'll give it a go.

Here's the board: http://www.perkiset.org/forum/salesforce_apex-b81.0/

...nestled in the Level 2 Cache area (because it's a specialty item).

[kurdt]

I added this function to help me to insert stuff little bit easier:

Code:

function insert($table,$data_array) {
$tmp_col = array();
$tmp_dat = array();

# Create column and data values for SQL command
foreach ($data_array as $key => $value)
{
$tmp_col[] = $key;
$value = strip_tags($value);
$tmp_dat[] = $value;
}
$columns = join(",", $tmp_col);
$data = join(",", $tmp_dat);

# Create and execute SQL command
$sql = "INSERT INTO ".$table."(".$columns.") VALUES (". $data.")";
$runsql = $db->multiQuery($sql);
return $runsql;
}

You can make it more efficient if you want, it's not my code originally but I've been using it for many years. Points to those who know where this code is from originally

*edit* If you put it into live site to save user's input, you might want to sanitize inputs little bit better.

*edit 2* I just noticed that it of course doesn't work when you put it like that in Perk's class so you have to modify it a little bit to make it work. When I have the time, I'll finish this and post it here.

[jammaster82]

Okay my noodle is definitely baked.  Now i want to just go learn cloud computing..lol

This would be awesome for just inserting a mysql dump file,
split it on the semicolons and fire it away...

Consider this code. It will bake your noodle a bit, but there are some interesting mechanics presented here:

Code:

<?php

$sql = <<<SQL
select * from contacts;
select * from addresses;
select * from memos;
SQL;

$result = $db->multiQuery($sql);

foreach($result as $idx=>$resultSet)
{
echo "$idx) {$sql[$idx]}\n";
foreach($resultSet as $rows)
{
print_r($rows);
}
}

?>

[nutballs]

ya except my Dump file...

splitting would require the whole thing loaded in memory.

my dump is 40gigs currently, LOL

[jammaster82]

so how to you reinsert your dumps then?

[nutballs]

command line only it would seem.

I also use mysqlhotcopy, but find that it brings my server to a grinding halt, so only use it on light load DBs.

I am considering the live cluster, master->slave option instead. Master dies, change some connection strings and bam, its all good.
Also I could read from the slave, only doing inserts and updates and deletes on master. That would split off 1 motherfuckerofagiantquery that is causing me problems and bring the master to a halt.

[jammaster82]

so from the command line how are you sure every line was inserted?

are you just using the source command?

[nutballs]

im not positive, but considering it either finishes without error, or bombs out, its should be inserting every line.

all i do is:   mysql -u USER -p DBNAME < dump.sql

[jammaster82]

so when it bombs out you go through every line and find out why?
error by error on a 40 gig file until it all flies?