Application Development

MYSQL - IF NOT EXISTS ... INSERT INTO

2011-11-09T05:07:00.000-08:00

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);

Warning: Please select a file! in Opencart

2011-11-02T10:27:00.000-07:00

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.

ASP.NET - new session id when refresh the page

2011-08-22T03:22:00.000-07:00

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)
{

}

MySQL scheduler job / event

2011-07-19T02:33:00.000-07:00

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()

Search text in MySQL stored procedure

2011-07-18T23:25:00.000-07:00

Search keyword or text in MySQL stored procedure

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

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

2011-07-02T05:55:00.000-07:00

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;

Data too long for column 'xxxxx' at row 1

2011-04-12T03:07:00.000-07:00

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

"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."

2011-03-29T07:12:00.000-07:00

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.

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.

2011-03-21T00:53:00.000-07:00

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.

rename htaccess.txt to .htaccess in windows

2011-03-04T21:51:00.000-08:00

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.

Go to windows start and click on Run.
Type cmd in the text box and hit enter
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"
Use ren command to rename the file.

type .htaccess.txt .htacess

and hit enter.

Done.

Hope this help.

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

2011-02-18T05:58:00.000-08:00

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

Expected identifier, string or number

2011-01-13T19:52:00.000-08:00

Expected identifier, string or number

xin invoice software website

2010-12-25T22:35:00.000-08:00

xin invoice software website nominated as cool site in http://www.coolsitecollection.com/sites/view/8c007b3f2e1bbfb94890045f0b37dc0e and most inspired site in http://www.mostinspired.com/sites/view/4194e25452fb2ef5bb6a390df84a162a

xin invoice software http://www.xininvoice.com in www.coolsitecollection.com

xin invoice software http://www.xininvoice.com in www.mostinspired.com

Xin Invoice software 2.0 holiday season and year end promotion

2010-12-19T02:34:00.000-08:00

Xin Cube is running promotion for Xin Invoice product for 16th December 2010 to 31st December 2010. The discount up to 37% for Xin Invoice software version 2.0. for more information, visit: http://www.xininvoice.com . Press release can be found in http://www.free-press-release.com/news-xin-invoice-software-2-0-holiday-season-and-year-end-promotion-1292512710.html

invoice software | invoicing software | billing software

2010-12-16T07:55:00.000-08:00

invoice software | invoicing software | billing software

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

2010-12-07T09:38:00.000-08:00

Facing issue when try to connect to MSSQL 2005 Express from remote server

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

To solve this issue

1. Enable TCP/IP

Go to Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager

Make sure TCP/IP is Enabled

2. Make sure MS SQL allow remote connection with TCP/IP protocol.

Go to Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration

Click on Surface Area Configuration for Services and Connections

Make sure Local and remote connections and using TCP/IP only is selected.

3. Open port for Port: 1433, sqlbrowser.exe and sqlservr.exe in windows firewall.

Launch the Windows Firewall configuration tool from the control panel.

Click the Exceptions Tab

Click on Add Port, key in information as below

Repeat above but this time click the "Add Programs." button and select "sqlservr.exe" from the

location where you install SQL Server Express (normally in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn)

Report the same steps but select "sqlbrowser.exe" (normally in: C:\Program Files\Microsoft SQL Server\90\Shared)

Try again and the problem solved. Hope this help.

invoice software - Xin Invoice 3 release

2010-11-22T05:26:00.000-08:00

Xin Cube announce that new version of invoice software - xin invoice 3.0 has released. The invoice software come with new features and new invoice template.

For more information, refer to the press release http://www.prlog.org/11089459-invoice-software-xin-invoice-new-version-30-released-by-xin-cube.html or http://www.free-press-release.com/news-invoice-software-xin-invoice-new-version-3-0-released-by-xin-cube-1290268955.html . For video demo, please refer to http://www.youtube.com/user/xincube

MySQL Server 5.1 data folder

2010-11-22T04:29:00.000-08:00

My development machine have MySQL server 5.0 installed and i upgrade to version 5.1.

After i've installed MySQL server 5.1, i can't found the data folder which normally in "C:\program files\MySQL\MySQL Server 5.0\data" for version 5.0.

Finally able to get the location in in "C:\Users\All Users\MySQL\MySQL Server 5.1\data" for Windows Vista

If you are using windows XP, it's in "C:\Documents and Settings\All Users\MySQL\MySQL Server 5.1\data"

Hope this help

MS SQL profiler like for mysql

2010-10-17T04:39:00.000-07:00

I was looking around a software for MySQL that can work like MSSQL profiler which can monitor the SQL query executed. Finally i found one called Log Monitor which work well for me.

How it's work? Download Log Monitor. Open LogMonitor.jar file.

Connect to MySQL server, open a SQL Query window and run this script

SET GLOBAL general_log = 'ON';

Check on log file location in my.ini file (for windows user) or my.cnf (for linux user).

For windows user,

#Path to the database root

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"

Open Log Monitor and set "log file path" with the file name.

Now, open another SQL Query window and run any script. You can see the query you run displayed in the Log Monitor.

Hope this help.

Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

2010-10-12T08:25:00.000-07:00

Face strange error when try to print report using crystal report.

Server Error in 'xxx' Application.

Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Source Error:

  An unhandled exception was generated during the execution of the current  web request. Information regarding the origin and location of the  exception can be identified using the exception stack trace below.

Stack Trace:

[AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.]
CrystalDecisions.ReportAppServer.ClientDoc.ReportClientDocumentClass.Open(Object& DocumentPath, Int32 Options) +0
CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.Open(Object& DocumentPath, Int32 Options) +95
CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.EnsureDocumentIsOpened() +271

[Exception: Load report failed.]
CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.EnsureDocumentIsOpened() +333
CrystalDecisions.CrystalReports.Engine.ReportDocument.Load(String filename, OpenReportMethod openMethod, Int16 parentJob) +894
CrystalDecisions.CrystalReports.Engine.ReportDocument.Load(String filename) +84
Transaction_InvoicePrint.Print() +149
Transaction_InvoicePrint.Page_Load(Object sender, EventArgs e) +5
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

I'm not sure what could cause this issue but after i reset iis, everything work fine.

mysql 5.1 data folder / directory / location

2010-10-01T02:40:00.000-07:00

I've installed new MySQL 5.1 to replace my MySQL 5.0. I cannot find my data folder.
Finally, check my.ini file in MySQL 5.1 folder and found line below

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"

All data folder is in "C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"

ASP.NET Security Vulnerability: Take Action Immediately

2010-09-21T20:05:00.000-07:00

This is very important for those who have asp.net application running out there. Attacker can get data encrypted data in view state or web.config data if you ignore this.

Detail informations and workaround stated in Scottgu's Blog

http://weblogs.asp.net/scottgu/archive/2010/09/18/important-asp-net-security-vulnerability.aspx

For more others information, please refer to

http://www.microsoft.com/technet/security/advisory/2416728.mspx

http://info.dotnetnuke.com/rs/dotnetnuke/images/DotNetNuke_Security_Update_Regarding_ASPNET_Vulnerability_091810.pdf

Keep track download in ASP.NET

2010-09-20T01:22:00.000-07:00

Situation:

I have a software in my web server and let visitor download for free, i wish to keep track of total download and visitor location.

Feasibility:

For the location keep tracking, i use GeoIP (GeoLiteCity.dat) http://www.maxmind.com/app/ip-location

Solution:

Database:

I have create a table called downloadlog with field below. I plan to have different type of product in future, so i add ProductFamilyID field. If you don't have that, you can ignore it.

Now, i need a stored procedure called psp_DownloadLog_Add that insert data into table above. The code should be as simple as possible

CREATE PROCEDURE [dbo].[psp_DownloadLog_Add]

(

@p_IP NVARCHAR(50),

@p_Country NVARCHAR(255),

@p_CountryCode VARCHAR(50),

@p_City NVARCHAR(255),

@p_ReferrerURL NVARCHAR(512),

@p_ProductFamilyID INT

)

BEGIN

SET NOCOUNT ON;

INSERT INTO tblDownloadLog

(

IP,

Country,

CountryCode,

City,

ProductFamilyID,

ReferrerURL,

DownloadDate

)

VALUES

(

@p_IP,

@p_Country,

@p_CountryCode,

@p_City,

@p_ProductFamilyID,

@p_ReferrerURL,

GETDATE()

)

SET NOCOUNT OFF;

END

ASP.NET

To make the system more structural and manageable, I have create 2 classes to handle the download log. DownloadLogInfo class mainly is use the deal with the information that need to log into database. DownloadLog class is mainly the class who do the communication with the database. Please check the code below.

DownloadLogInfo class

public sealed class DownloadLogInfo

{

public DownloadLogInfo() { }

int _downloadID = 0;

string _ip = "";

string _country = "";

string _countryCode = "";

string _city = "";

int _productFamilyID = 0;

string _referrerURL = "";

DateTime _downloadDate = DateTime.Now;

public int DownloadID { get { return _downloadID; } set { _downloadID = value; } }

public string IP { get { return _ip; } set { _ip = value; } }

public string Country { get { return _country; } set { _country = value; } }

public string CountryCode { get { return _countryCode; } set { _countryCode = value; } }

public string City { get { return _city; } set { _city = value; } }

public int ProductFamilyID { get { return _productFamilyID; } set { _productFamilyID = value; } }

public string ReferrerURL { get { return _referrerURL; } set { _referrerURL = value; } }

public DateTime DownloadDate { get { return _downloadDate; } set { _downloadDate = value; } }

public DownloadLogInfo(int pDownloadID, string pIP, string pCountry, string pCountryCode, string pCity, int pProductFamilyID, string pReferrerURL, DateTime pDownloadDate)

{

_downloadID = pDownloadID;

_ip = pIP;

_country = pCountry;

_countryCode = pCountryCode;

_city = pCity;

_productFamilyID = pProductFamilyID;

_referrerURL = pReferrerURL;

_downloadDate = pDownloadDate;

}

DownloadLog Class

public sealed class DownloadLog

{

public void Add(DownloadLogInfo pInfo, string pConnectionString)

{

SpParamInfo[] myParamInfo = new SpParamInfo[6];

myParamInfo[0] = new SpParamInfo("@p_IP", SqlDbType.NVarChar, pInfo.IP);

myParamInfo[1] = new SpParamInfo("@p_Country", SqlDbType.NVarChar, pInfo.Country);

myParamInfo[2] = new SpParamInfo("@p_CountryCode", SqlDbType.NVarChar, pInfo.CountryCode);

myParamInfo[3] = new SpParamInfo("@p_City", SqlDbType.NVarChar, pInfo.City);

myParamInfo[4] = new SpParamInfo("@p_ProductFamilyID", SqlDbType.Int, pInfo.ProductFamilyID);

myParamInfo[5] = new SpParamInfo("@p_ReferrerURL", SqlDbType.NVarChar, pInfo.ReferrerURL);

DbAdapter myDbAdapter = new DbAdapter(pConnectionString);

myDbAdapter.ExecuteNonQuery("psp_DownloadLog_Add", myParamInfo);

myDbAdapter.Dispose();

}

Now, I have create a page, call download.aspx. This page will actually get information from the visitor and insert into database. After that it will force a file to be downloaded. Before that, i already GeoLiteCity.dat file and put into my App_Data folder.

Code behind for download.aspx

protected void Page_Load(object sender, EventArgs e)

{

string VisitorReferrer;

// Full path to GeoLiteCity.dat file

string FullDBPath = Server.MapPath("~/App_Data/GeoLiteCity.dat");

// Visitor's IP address

string VisitorIP;

VisitorIP = Request.ServerVariables["REMOTE_ADDR"];

if (Request.UrlReferrer != null)

{

VisitorReferrer = Request.UrlReferrer.ToString();

}

else

{

VisitorReferrer = "";

}

// Create objects needed for geo targeting

Geotargeting.LookupService ls = new Geotargeting.LookupService(FullDBPath, Geotargeting.LookupService.GEOIP_STANDARD);

Geotargeting.Location visitorLocation = ls.getLocation(VisitorIP);

DownloadLog myDownloadLog = new DownloadLog();

DownloadLogInfo myDownloadLogInfo = new DownloadLogInfo();

if (visitorLocation != null) // get the geoip information using visitor IP address

{

myDownloadLogInfo.Country = visitorLocation.countryName;

myDownloadLogInfo.CountryCode = visitorLocation.countryCode;

myDownloadLogInfo.City = "unknown";

if (visitorLocation.city != null)

{

myDownloadLogInfo.City = visitorLocation.city;

}

myDownloadLogInfo.IP = VisitorIP;

myDownloadLogInfo.ProductFamilyID = 1;

myDownloadLogInfo.ReferrerURL = VisitorReferrer;

}

else

{

string country = "unknown";

string countryCode = "unknown";

string city = "unknown";

myDownloadLogInfo.Country = country;

myDownloadLogInfo.CountryCode = countryCode;

myDownloadLogInfo.City = city;

myDownloadLogInfo.IP = VisitorIP;

myDownloadLogInfo.ProductFamilyID = 1;

myDownloadLogInfo.ReferrerURL = VisitorReferrer;

}

// WebConfig.Connection is the connection the database and insert data into database

myDownloadLog.Add(myDownloadLogInfo, WebConfig.Connection);

Response.ContentType = "application/zip";

Response.AddHeader("content-disposition", "attachment; filename=softwaresetup.zip");

FileStream sourceFile = new FileStream(@"C:\inetpub\dotnetfish\software\softwaresetup.zip", FileMode.Open);

long FileSize;

FileSize = sourceFile.Length;

byte[] getContent = new byte[(int)FileSize];

sourceFile.Read(getContent, 0, (int)sourceFile.Length);

sourceFile.Close();

Response.BinaryWrite(getContent);

}

Hope this help.

Cannot Delete xxx: Access is denied Make sure the disk is not full or write-protected and that the file is not currently in use

2010-09-14T22:38:00.000-07:00

Have you come across the annoying error when try to delete files from you computer or even external harddisk?

I've tried to change the permission setting in security tab but i cannot do anything because only system can change the setting.

Finally, i found a tool named unlocker http://download.cnet.com/Unlocker/3000-2248_4-10493998.html that help me to delete the files.

Hope this help.

Attempted to read or write protected memory. This is often an indication that other memory has been corrupted.

2010-09-14T06:38:00.000-07:00

Hit below error when deploy a new app to existing running application.

Attempted to read or write protected memory. This is often an indication that other memory has been corrupted.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory has been corrupted.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Try to set the folder permission but still hit the same error after restart iis.

Next, i try to run the permissions wizard in the IIS. After run it, restart iis and i'm lucky. It work like normal.

Hope this help.