Wednesday, November 22, 2006

Getting Java JVM and midp SSH to run on a Motorola Q

I bought a Motorola Q a few days ago with the full intention of using it for emergency SSH access when I'm not in front of a computer. I was a little disappointed to find that getting a terminal app with SSH wasn't as easy as I had expected, but I got it working nonetheless.

If you want something quick for SSH or Telnet, try zaTelnet at the link below. It installed fine on my Q (make sure you download the version for SmartPhone running Windows Mobile 5), though it does seem a bit slow while working. It's freeware, but it's not open source, which makes me a bit wary. However, it does seem to work fairly well:

http://www.codebrowser.org/

If you're looking to get a Java VM installed so you can run MidpSSH, it's more involved, but not too bad, and then you have a Java VM for other Midlets.

First, you need the JVM for the SmartPhone, which is still in the "evaluation" stage from IBM Websphere, but it seems pretty stable. You have to sign up for an IBM account (free, just annoying) to get to the download, but this is the link:

http://www14.software.ibm.com/webapp/download/preconfig.jsp?id=2006-04-06+13%3A40%3A41.975747R&S_TACT=104CBW71&S_CMP=

after you signup (and wait a few minutes for your account to become active, apparently), follow the steps below:

1. download the executable file listed under "CLDC 1.1/MIDP 2.0 for Windows Mobile 5.0 Smartphone Edition/ARM". The filename will begin with ibm-weme-wm50-sp-arm-midp20. Also download install.pdf under the same heading.

2. run the file and complete the install wizard.

3. Once installed, browse to C:\Program Files\IBM\WEME\runtimes\61\wm50-arm-sp-midp20

4. in there you will find a zip file called weme-wm50-sp-arm-midp20_6.1.0.20060727-102926.zip
(Note: the filename might have a slightly different version number after midp20_, but it's the only zip file in the directory)

5. Extract the zip, then follow the instructions in the install.pdf file and you should be good to go.
(Note: The bin/, lib/, and examples/ directories referred to in the install file are the ones from the extracted .zip file, *not* from the _jvm directory.)

The install.pdf file details how to install their example app, but you can follow the same steps to install midPSSH.

Thursday, November 09, 2006

"mySQL server has gone away" in PHP when using mySQL stored procedures

There seems to be a lot of conflicting information floating around about stored procedures in PHP, and I spent a bit of time myself grappling with a "mysql server has gone away" error when using the CALL statement to run a stored procedure. I think I've managed to get things squared away, both in the code and in my head, as to the caveats of mySQL 5 stored procedures in PHP. Here's the lowdown:

Per mySQL's own documentation for the C API of mysql_next_result:
"each CALL returns a result to indicate the call status, in addition to any results sets that might be returned by statements executed within the procedure."

This means that you should use mysqli_multi_query() when calling your stored procedure.

Additionally, this means that you cannot simply call mysqli_store_result() to store your buffered result in a variable then move on to your next query, which is perfectly reasonable for normal queries. Even for procedures that return a single resultset, you need to make sure you iterate through the "call status" result even though you won't be using it for anything. In fact, this status result seems to be kind of nebulous in that you can't use_result() or store_result() on it (as far as I can tell), but you need to iterate through it (via next_result()) before executing another query.

My problem with the way this all works is that since all of my procedures only returned a single result set, I wanted to be able to access them exactly as I would any other query (including storing the result so I could use it later), without having to manually iterate through this extra status result each time I called a procedure. Below is what I came up with.

Note: I'm using the procedural functions for the mysqli interface because people who are just getting acquainted with mysqli will recognize the syntax more easily, but you should definitely look into the object oriented approach for using mysqli functions.


function &procedure_get_result( $procedure_call ) {

//
//note: error handling has been removed from this function
//for the sake of the example, but you'll want to check the
//return values of functions like mysqli_connect(), etc...
//

$result_count = 0;
$result_loop_silence = 0;
$false_ret = false;

$dbh = mysqli_connect("localhost", "my_user", "my_password", "my_dbname");

//
// if the word CALL was included in the parameter, just strip it out.
// We'll add it later.
//
if ( substr(strtoupper($procedure_call), 0, 4) == 'CALL' ) {
$procedure_call = substr( $procedure_call, 4 );
}

$procedure_call = trim($procedure_call);

$query = "CALL {$procedure_call}";

if ( mysqli_multi_query($dbh, $query) ) {

$result = mysqli_store_result($dbh); //this is the result we want!

while ( mysqli_more_results($dbh) ) {

//
// iterate through the call status result set
//
if ( $result_count > 0 ) {
//
// this function only supports a 0th and a 1st result set
// so show a warning if we have more
//
if ( !$result_loop_silence ) {
trigger_error( 'Too many results returned for ' . __METHOD__ . '. Use multi query.', E_USER_WARNING );
$result_loop_silence = 1;
}
}

mysqli_next_result($dbh);

if ( $status_result = mysqli_use_result($dbh) ) {

//
// this never seems to get executed with
// CALL that only return one result set plus the
// status resultset, but we'll leave it in
//

$status_result->close();
mysqli_free_result($status_result);

}

$result_count++;
}

return $result;

}

return null;
}