Search This Blog

Wednesday, November 9, 2011

MYSQL - IF NOT EXISTS ... INSERT INTO

Database MYSQL - tried IF NOT EXISTS ... INSERT INTO but not working.

IF NOT EXISTS (SELECT * FROM tblabc WHERE id = 10547) THEN
INSERT INTO tblabc
(a, b, c)
SELECT a , b, c
FROM tblabc WHERE id = 10547;

END IF;


Finally, this work

INSERT INTO tblabc
(a, b, c)
SELECT a , b, c
FROM tblabc WHERE id = 10547
AND NOT EXISTS (SELECT * 
FROM tblabc WHERE id = 10547);

Wednesday, November 2, 2011

Warning: Please select a file! in Opencart

I'm using Opencart version 1.9.4.3,  windows server 2003, IIS 6 and PHP Version 5.3.8. Try to upload image in opencart and error message "Warning: Please select a file!" prompted.



After check on the php eror log file, The error log show "Warning: File upload error - unable to create a temporary file in Unknown on line 0"

Solution for this issue is edit upload_tmp_dir in php.ini.

For example, change to
upload_tmp_dir = "C:\PHP\temp"

after the finish edit, restart the web server.

Done. Problem solved.

Monday, August 22, 2011

ASP.NET - new session id when refresh the page

Using Visual Studio 2005, dotnet framework 2.0.

Try to get a session id in default.aspx


  public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            txtSessionID.Text = this.Session.SessionID;
        }
    }

when i refresh the page, new session id show. This is weird because it won't happen in Asp.net for dotnet framework 1.1.

To solve the problem, simply add below code in global.asax to register an event handler for the session start event


void Session_Start(Object sender, EventArgs e)
        {

        }


Tuesday, July 19, 2011

MySQL scheduler job / event

In order to have MySQL event run, we need to make sure GLOBAL event_scheduler is set to on or 1.

SET GLOBAL event_scheduler = ON;

Or we can set in my.ini (in windows) or my.cnf (in linux) file with value

event_scheduler=on

Then create a scheduler job in MySQL. Example, i wish to call a stored procedure psp_DailyJob() every day start from 12:25:00.

CREATE EVENT CallDailyJob
ON SCHEDULE EVERY 1 DAY STARTS '2011-07-18 12:45:00'
DO
      CALL psp_DailyJob()

Monday, July 18, 2011

Search text in MySQL stored procedure

Search keyword or text in MySQL stored procedure

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%IsConsignment%'
Order by Routine_Name;

Saturday, July 2, 2011

You can't specify target table 'xx' for update in FROM clause

Try to update a column in MySQL which uses SUM and join and hit error "You can't specify target table 'xx' for update in FROM clause "


UPDATE tblstocktake ap SET TotalQuantity =
(
  SELECT SUM(t.Unitinstock)
  FROM tblstocktake t
  JOIN tblstocktake a
  ON a.productid = t.productid
  WHERE a.productid = ap.productid
  GROUP BY a.productid
);

Which is not working.

Finally, found a solution which is working


UPDATE tblstocktake LEFT JOIN
(SELECT productid,
SUM(unitinstock) AS baseVal
FROM tblstocktake
GROUP BY productid)
AS totalSum USING (productid)
SET totalquantity=baseVal;

Tuesday, April 12, 2011

Data too long for column 'xxxxx' at row 1

Try to update a column showContent which is boolean datatype in tbl_page table in mysql

UPDATE tbl_page SET showContent='1' WHERE pk=13

and hit error message "Data too long for column 'showContent' at row 1"

After try around and figure out below solve my issue

UPDATE tbl_page SET showContent=1 WHERE pk=13

Tuesday, March 29, 2011

"Could not load file or assembly 'CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304' or one of its dependencies. The system cannot find the file specified."

Error message show when try to deploy web application to 64 bit Windows server 2008 R2 server. 
"Could not load file or assembly 'CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304' or one of its dependencies. The system cannot find the file specified."


This is not easy for us to debug because we already install crystal report runtime in the server. But wait ... what is the problem then? version? yes, the version for the runtime installed is 10.5.3700.0 which i get from my machine:
C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\CrystalReports10_5. 

So, how to get crystal report runtime 10.2.3600.0 version? We can download from http://resources.businessobjects.com/support/additional_downloads/runtime.asp#07


So, beware of the different version of crystal report runtime. Hope this help.

Monday, March 21, 2011

Unable to retrieve stored procedure metadata. Either grant SELECT privilege to mysql.proc for this user or use "use procedure bodies=false" with your connection string.

Error receive when trying access to mysql database using windows application.
"Unable to retrieve stored procedure metadata. Either grant SELECTprivilege to mysql.proc for this user or use "use procedure bodies=false" with your connection string."










I'm using user "mrptest" connect to database and user "mrptest" has been granted to all privilage. The stored procedure created using 'root'@'localhost'.

To overcome this problem, i need to to drop all the created database and change the statement from CREATE DEFINER=`root`@`localhost` PROCEDURE to CREATE DEFINER=`mrptest`@`%` PROCEDURE

DELIMITER $$

DROP PROCEDURE IF EXISTS `psp_AAA` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `psp_AAA`()
BEGIN

END $$

DELIMITER ;

Now i change to

DELIMITER $$

DROP PROCEDURE IF EXISTS `psp_AAA` $$
CREATE DEFINER=`mrptest`@`%` PROCEDURE `psp_AAA`()
BEGIN

END $$

DELIMITER ;

Problem solved.

Friday, March 4, 2011

rename htaccess.txt to .htaccess in windows

We will hit error when try to rename htaccess.txt to .htaccess in windows. Error message "You must type a file name."



.htaccess is usually used in php application like joomla and some of the shopping card. The purpose is to provide more user friendly url for SEO purposes or reading purposes by using Apache mod_rewrite.

To overcome this problem, we need to use windows command promp to rename the file. Below is the step by step how we can solve the problem.



  1. Go to windows start and click on Run.


  2. Type cmd in the text box and hit enter


  3. A command prompt will popup, change to the folder location your file htaccess.txt located. In my sample, my file located in C:\temp_folder. So i type

    cd C:\temp_folder

    and hit enter. My current directory will be in "C:\temp_folder"


  4. Use ren command to rename the file.

    type ren .htaccess.txt .htacess

    and hit enter.


    Done.


Hope this help.

Friday, February 18, 2011

file:///C:/Program%20Files/Mozilla%20Thunderbird/modules/iteratorUtils.jsm:113

Hit error

"A script on this page may be busy, or it may have stopped responding. You can stop the script now, or you can continue to see if the script will complete.
file:///C:/Program%20Files/Mozilla%20Thunderbird/modules/iteratorUtils.jsm:113 
"
I can click on "Stop script" or "Continue" button. I click on continue and it's working fine. This issue most probably caused by the work load for the script like loading folder and etc. It's fine if it's not consistently happen.

To know more what happen, please read on http://kb.mozillazine.org/Script_busy_or_stopped_responding