Tuesday, November 27, 2007

Don't Give Space When Calling MySQL Function

Dear people ... this might be a simple problem but you can take a whole day to trace where the problem exist if you didn't care about this. I face this problem and get confuse when working with MySQL.

I have the following code and execute well using phpMyAdmin but it's failed (no result set given) when i try to execute directly from my php script.

$strSql = "
SELECT user_password, user_name
FROM user_t
WHERE 1
AND user_id = '" . $_POST['txt_id'] . "'
AND user_password = SHA1 ('" . $_POST['txt_pwd'] . "')" ;

if ( $objRS = mysql_query ($strSql, $objConn) )
{
// Do something here ...
}

As you can see in the bold style of the code above we can see that there is a space between MySQL SHA1 function and the parameter for that function.

After i remove that space then the problem is solved. So, my code now looks like below :

$strSql = "
SELECT user_password, user_name
FROM user_t
WHERE 1
AND user_id = '" . $_POST['txt_id'] . "'
AND user_password = SHA1('" . $_POST['txt_pwd'] . "')" ;

if ( $objRS = mysql_query ($strSql, $objConn) )
{
// Do something here ...
}

So, keep your MySQL function closed with ( without space after function name. Hope this helps.

Wednesday, November 21, 2007

MySQL Stored Procedure : can't return a result set in the given context

This problem came when i'm deploying the program and databases to the production server while it's running smooth on my local computer. This is terrible and i have a suspicious if there's something wrong with the MyODBC Connector in the production server. I need to know what version of MyODBC Connector installed on my local computer and on my production server so i can compare it. But how do i know the version of this MyODBC Connector?



Below is my step to find out the version of MyODBC Connector :
  1. Go to Control Panel -> Administrative Tools and choose Data Sources (ODBC).
  1. Click the Add button and scroll to the end of the data source list and you will find out the version of your MyODBC Connector like shown in the image below.


The version of MyODBC Connector on my computer is 3.51.12.00 while the version on production server is 3.51.11.00. The MyODBC Connector version on production server come with the installation of Plesk 8.1.

This why i got the error PROCEDURE db_name.procedure_name can't return a result set in the given context when running MySQL stored procedures on the production server. And after i upgraded the MyODBC Connector to the latest version then the problem is solved.

In the general case, the server did not know if a procedure will return just one result set, or more. So, it has to assume the "worst" case, that several results sets might be returned.

Make sure we are using the latest ODBC version. And don't forget to restarting the server to takes an effect.

The support for the CLIENT_MULTI_RESULTS flag was added in 3.51.12.

How To Check If Processor Type Is 32-bit Or 64-bit

Sometime when we need to download or install a software, we need to know for what and which processor type that software. In some case we can use the same software for 32 and 64-bit version. But in most cases we need to install a different software for a specific processor type.

For a Windows user you can check the processor type using the following way :
  1. Right click My Computer and choose Properties.
  2. Click the Advanced tab.
  3. Click Environment Variables.
  4. In the System variables find a variable with the name PROCESSOR_ARCHITECTURE. If your PC has a 32-bit processor, this variable will have a value of x86. If it has a 64-bit processor this variable will have a value of x64.


This way is not a real world test for checking the processor type because
those variables can be changed by anyone but at least you can use this way.

To check the processor type for another machine or another operating system you can use this link http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=31892 as a reference.

MySQL Stored Procedure Is Gone !

Hello people ... i face this problem when working with MySQL Server 5.0.45

I've creating a new database on this MySQL Server and create many routines (stored procedure and function). And when i renaming the database name then all my routines related to the database i renamed is gone. All is gone.

I try to find out manually on the MySQL information schema because all the routines of all database is stored there but i find nothing except the other routines for other database. This is terrible and do not let you face the same problem.

So, be careful to backup your MySQL routines (stored procedure and function) before renaming the database name. Hope this helps !