Posts Tagged solution

Solutions for using PHP with MySQL stored programs

Issue 1

In the PHP application, one MySQL server connection is established and then call two MySQL stored procedure in consecutive mysqli->prepare statements. The following error occurs when the second call happens.


Error code 2014
Commands out of sync; you can't run this command now

What does that mean? I do not know. I googled the internet and found not solution for this. I two consecutive calls for MySQL stored procedure in MySQL Workbench. There is no error. That tells me that the issue is related to PHP implementation, a bug should be fixed in the current PHP module. I suppose the stmt->close() should do the trick to clean the buffer and complete the DB cursor but apparently not.

Solution for Issue 1

For every call of stored procedure, establish a new DB connection. After the completion of the stored procedure call, close the established DB connection appropriately. Here is sudo code:


$db = openBD();
$stmt = $db->stmt->init();
$stmt->prepare($sql);
$stmt->bind_param("ss",$var1,$var2);
$stmt->execute();
$stmt->bind_results($res1, $res2, ...);
while ($stmt->fectch())
{ ... }
$stmt->close();
closeDB($db);

The above code has some redundancy. A very simple solution is to issue the following statement between two consecutive queries.


$db->next_result();

Issue 2

MySQL utf-8 encoding issue. The situation is that the MySQL database use utf-8 encoding. Using SQL query or calling stored procedure in MySQL Workbench can return correctly answer. When the same stored procedure call invoked by PHP, strange characters are returned. However, call SQL statement directly in PHP does return correct results. This seems a bug specifically associates MySQL stored procedure call in PHP module. In the other word, it is a PHP bug instead of MySQL.

Solution for Issue 2

So far I have not find a solution for this. I like the idea to use MySQL stored procedure in PHP application. In this way, the data logic is separated from the presentation rendering logic. Make PHP code more clean and concise and easy to maintain. Finally, I got a function that makes the stored procedure works with PHP with no problem. Here is the magic function for creating a DB connection.


function openDB()
{
$this->_mysqli = @new mysqli($this->_config['DB_HOST'],
$this->_config['DB_USER'],
$this->_config['DB_PASSWORD'],
$this->_config['DB_NAME']);

$this->_mysqli->query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");
$this->_mysqli->query("SET character_set_client=utf8");
$this->_mysqli->query("SET character_set_connection=utf8");
$this->_mysqli->query("SET character_set_results=utf8");
}

After this is done, you have to add the following statements to the top of your PHP application, too.


mb_language('uni');
mb_internal_encoding('UTF-8');

Share

Tags: , , , , , , , , , ,

Use libcurl.net at C# project – a complete solution

Introduction

When I am working on TwitterGIFT project, I need find how to use Twitter API in my C# project. I know CURL is really good at Linux environment. My PERL script use it to access Twitter API functions. It is powerful and convenient and easy to use. How can I use it in my C# project? Through system calling. That will be pretty bad. I explore the curl’s official site and find out they provide library to different development environments. One of them is for .NET, which is what I am looking for. I download it and try hard to find how to use it. At the same time, I searched the internet to find some useful example. Yes, I found some thing and use them as my start point. Then I developed a complete solution for my C# project. I publish it here to share with developers worldwide.

Source code

/*
 * Author: Zhanshan Dong
 * Website: http://www.sunfinedata.com/
 * Date: 4/3/2009
 * Modified: 9/18/2009
 */

using System;
using SeasideResearch.LibCurlNet;
using System.IO;  

namespace TwitterGIFT
{
	/// 
	/// This is a complete C# class to utilize LibCurlNet
	/// You can use it in your project freely as long as 
	///    1) keep the identity of the class
	///    2) keep the authorization information
	///    3) use it in open source
	///    4) do not use it in commercial environment
	/// 
	public class GiftCURL
	{
		private StreamWriter giftWriter;
		private string outputFilename;
		private string username;
		private string password;

		public GiftCURL( string userName, string passWord)
		{
			username = userName;
			password = passWord;
		}
		
		private void giftWriterOpen() 
		{
			giftWriter = new StreamWriter(outputFilename);
		}
		
		private void giftWriterClose() 
		{
			giftWriter.Close();
		}

		private Int32 OnWriteData(Byte[] buf, Int32 size, Int32 nmemb, Object extraData)
		{
	        	giftWriter.Write(System.Text.Encoding.UTF8.GetString(buf));
	        	return size * nmemb;
		}

		public void DownloadURL(string url, string urlPostFields, string OutputFilename, bool isPost)
		{
			outputFilename = OutputFilename;
			if ( outputFilename != "") { giftWriterOpen(); }
			try 
			{
            	Curl.GlobalInit((int)CURLinitFlag.CURL_GLOBAL_ALL);
	            Easy easy = new Easy();
	            Easy.WriteFunction wf = new Easy.WriteFunction(OnWriteData);
	            easy.SetOpt(CURLoption.CURLOPT_WRITEFUNCTION, wf);
	            easy.SetOpt(CURLoption.CURLOPT_POST, isPost);
	            if (urlPostFields != "") 
	            {
	            	easy.SetOpt(CURLoption.CURLOPT_POST, true);
	            	easy.SetOpt(CURLoption.CURLOPT_POSTFIELDS,urlPostFields);
	            }
	            easy.SetOpt(CURLoption.CURLOPT_USERAGENT,
	                "Mozilla 4.0 (compatible; MSIE 6.0; Win32");
	            easy.SetOpt(CURLoption.CURLOPT_FOLLOWLOCATION, true);
	            if (url != "") { easy.SetOpt(CURLoption.CURLOPT_URL,url); }
	            if ((username !="") && (password != "")) 
	            {
	            	easy.SetOpt(CURLoption.CURLOPT_USERPWD,username + ":" +password);
	            }
	            easy.Perform();
	            easy.Cleanup();
	            Curl.GlobalCleanup();
	        }
	        catch(Exception ex) { }
			if ( outputFilename != "") { giftWriterClose(); }
		}
	}
}

Download

you can download the source code in file – GiftCURL.cs

Reference

Share

Tags: , , ,