It is currently Wed Feb 20, 2019 8:00 am




Post new topic Reply to topic  [ 4 posts ] 
MySQL - Data provider or service returned an E_FAIL Status 
Author Message

Joined: Mon May 26, 2008 8:02 am
Posts: 65
Post MySQL - Data provider or service returned an E_FAIL Status
Some of you may have found an error in the dbQwikSite designer saying 'MySQL - Data provider or other service returned an E_FAIL Status', trying to design a site using a MySQL database.

The dbQwikSite Designer is connecting to any database using universal methods, either straight ADO, or ODBC via ADO. It does not use proprietary connectors like MySQL connectors. So even if you are using dbQwikSite to generate a PHP/MYSQL web site, while the site itself WILL use native MySQL connections (and not ODBC), the designer itself needs to use MyODBC.

MyODBC has a problem dealing with tables that have TimeStamps/DateTime columns for which records exist but have 'zero-values' in it. This is actually different than 'NULL' values. It is a value, but all are set to zero like '00/00/0000 00:00:00' for a timestamp. MyODBC raises an E_FAIL status when it tries to retrieve records from such table.

Here are some recommendations you can apply to get over this issue:

First, make sure you have up-to-date MySQL Connector/ODBC drivers. You can use version 3.51.17.0 or higher. We provide MyODBC version 3.51.17 drivers installation package in your dbQwikSite installation folder, e.g. “C:\Program Files\dbQwikSite 5\Installation Packages\MyODBC 3.51”. Run the installer there if you want 3.51.17.

If you want the latest version of the driver, the latest version is available here:
http://dev.mysql.com/downloads/connector/odbc/3.51.html

However, even with the latest drivers, it will not be enough to resolve the E_FAIL Status error. Two solutions are available:

1. Change your TimeStamp columns to be 'DateTime'. DateTime does not cause as many problems as timestamps.

2. Assign a default value to your TimeStamp columns so no Null values are in the database.

For option 2, you need to make sure your database does not contain any ’0000-00-00 00:00:00’ date values in TimeStamp columns. This E_FAIL problem does not happen with DateTime columns.

To do so, alter your table and default the timestamp field to a non-zero date. Pick a date that for you means a ‘low-value’ (or 'high-value) date, for example January 1st, 1900. You can choose any date other date of course.

If you sort your records by date, this date will be lower than actual dates. Pick an older date if required for your business.

How to change the default date in MySQL?

You need to ‘alter’ your table and change the column(s) of Date/Time type to include a Default clause. Follow the example below to alter your table:

Code:
ALTER TABLE `mytable` CHANGE `myTimeStampField` `myTimeStampField` TIMESTAMP NOT NULL DEFAULT '1900-01-01 00:00:01';


If your database already contains rows with zero-dates, then you need to mass update your table

Code:
UPDATE mytable SET myTimeStampField = '1900-01-01 00:00:01' WHERE myTimeStampField = '0000-00-00 00:00:00'


Make sure you repeat these two SQL commands for each TimeStamp fields in your table.

If this is not clear or still does not resolve the issue, you can get more information via Google:

http://www.google.com/search?source=ig&hl=en&rlz=&q=returned+E_FAIL+Status


Mon Jun 16, 2008 2:59 pm
Profile E-mail

Joined: Fri May 09, 2008 5:38 am
Posts: 271
Location: Texas USA
Post Re: MySQL - Data provider or service returned an E_FAIL Status
There is also an issue with simple date fields -- 0001-01-01 is accepted as a valid date (called low-date) in almost every database I know of -- it is universally used to signify that a date has not happened yet. Your product will not accept this and we are forced to put high date (9999-12-31) which usually means that a date is at some indetermined point in the future or possible never expires. Now er have to have code to deternine which is which because we are foreced to use the same value for both. Enabling Low-date would be a nice plus.


Thu Jun 19, 2008 1:39 am
Profile E-mail YIM WWW

Joined: Mon May 26, 2008 8:02 am
Posts: 65
Post Re: MySQL - Data provider or service returned an E_FAIL Status
The Zero date is definitely something that causes problem in the designer (not at runtime), but the 'Low Date' should be ok as long as the database supports it.

If you have a specific case where it does not work, please post more details so we can nail it down and fix it.

I think in several circumstances, it wrongly appears that dbQwikSite 'does not support it'.

To start on the positive, in the case of MySQL, the low dates are supported. I have a Products table with both DATE and DATETIME fields. I can freely enter a low date on my update page:

Attachment:
LowDateMySqlUpdate.GIF


Which is reflected on the Data page:

Attachment:
LowDateMySqlData.GIF



In the case of MSACCESS, it is a different story. Any date you enter as far back as year 100 is ok.

The statement:

Code:
UPDATE employee SET HireDate=#1/1/100# WHERE (EmpNo=2);

gives you the right results.

But the following statement

Code:
UPDATE employee SET HireDate=#1/1/0098# WHERE (EmpNo=2);

sets the date to 01/01/2098 automatically. So if you try to use low date, or just less than year 100, you get the wrong results. Because this is the default behavior of MSAccess, dbQwikSite is subject to the same rules. And it 'appears' that dbQwikSite does not handle these dates correctly with MSAccess. The same statement entered directly within MSAccess Query Editor gives the wrong results by default.

So in terms of Low-Date support, I would say it depends on multiple factors. Factors that are specific to dbQwikSite should be enhanced. As you say it would be nice to support low-dates when the database supports it.

Users finding these low date issues can post more details here for their specific cases or create a service request in the support system at http://www.dbqwiksite.com/KnowledgeBase/LogIssue.asp?Application_abbr=QSITE .


You do not have the required permissions to view the files attached to this post.


Thu Jun 19, 2008 9:39 am
Profile E-mail

Joined: Wed Oct 08, 2008 11:27 pm
Posts: 141
Location: UK
Post Re: MySQL - Data provider or service returned an E_FAIL Status
We have experienced this and failing to resolve the date issue we have removed the date fields from the database, and all mention of them in the pages and items.

Now we have no connection at all ! We have been developing this with no problems, but come the day that we are supposed to present, the whole thing has e-fail on the whole system, cannot see anything cannot retrieve data it is just stuck on the first page and doesn't work. So we have now lost a client. We do not have time to do three weeks work in 3 hours and the problem will probably reappear. Why can't this system be updated to be in line with the lastest databases ? The demo version that we have deployed to the server, we have been able to change the date fields to what we require, and have experienced no problems. It is when it comes back to the desktop that it fails. It is not a mysql problem it is a QuikSite problem.

Have sent emails and tried to skype but no luck, I am fed up with the whole thing. !!!!!

_________________
Thanks
Simon
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
dbQwikSite Developer Version: 5.4.0.4
cPanel Version: 11.24.4-S33268
WHM 11.24.2 - X 3.9
Apache version: 2.0.63
PHP version: 5.2.5
MySQL version: 5.0.51a-community
Operating system: Linux


Mon Nov 03, 2008 9:16 pm
Profile WWW
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Copyright © 2004-2008 TheDevShop Ltd. All Rights Reserved