Business Objects DeskI: DA0005 “No column or data to fetch”
This is just a quick tip to help people fix an odd error I encountered whilst runing a Freehand SQL query against a MySQL 5 database, I’d used prompted queries against MySQL previously so that wasn’t an issue – I’m speculating a little but in my case the data provider used subqueries and both the inner and outer query were prompted which is a little unusual.
The error I received was DA0005 “No column or data to fetch”…
The solution came from the brilliant BOB Forums (original post here), the solution was to edit the odbc.sbo file which on my default installation found in:
C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc
You’ll need to add the following in the relevant section, in my case I added it under Generic, Generic ODBC and MySQL 5.
<Parameter Name="ForceSQLExecute">Always</Parameter>
After closing and restarting DeskI everything was fine, please bear in mind that if you’re running a client/server installation and your Inforview users need to run the report you’ll need to change the settings on the server too.
Categories: Business Objects Tags: business objects, Business Objects, DA0005, MySQL, PROMPT
Creating Databases and Users in MySQL
Historically I’ve been a Microsoft SQL Server guy but I’ve been doing quite a bit of query & analysis work on MySQL lately, though I’ve never performed any serious MySQL DBA. Well, the other day I was asked by a friend how to create a user and allow read/write access to a newly created database and this had to be done in SQL (i.e. no GUI tools or PHPMyAdmin).
Since I was starting from scratch I thought I’d put together a little script to create the database, add some data, create a user, prove that the user had write access and then tidy up after myself (always good to do!).
CREATE DATABASE ash_db;
USE ash_db;
CREATE TABLE ash_tbl (id INT NOT NULL,name CHAR(50) NOT NULL);
INSERT INTO ash_tbl (id,name) VALUES(1,'Ash');
SELECT * FROM ash_tbl;
CREATE USER 'ash_user' IDENTIFIED BY 'ash_pass';
GRANT ALL PRIVILEGES ON ash_db.* TO ash_user;
-- LOGIN AS ash_user THEN EXECUTE THIS...
INSERT INTO ash_tbl (id,name) VALUES(2,'Burton');
-- THEN LOG OUT AND COME BACK HERE
SELECT * FROM ash_tbl;
DROP TABLE ash_tbl;
DROP USER ash_user;
USE information_schema;
DROP DATABASE ash_db;
CREATE USER 'ash_user' IDENTIFIED BY 'ash_pass';
GRANT ALL PRIVILEGES ON ash_db.* TO ash_user;
-- LOGIN AS ash_user THEN EXECUTE THIS...
INSERT INTO ash_tbl (id,name) VALUES(2,'Burton');
-- THEN LOG OUT AND COME BACK HERE
SELECT * FROM ash_tbl;
DROP TABLE ash_tbl;
DROP USER ash_user;
USE information_schema;
DROP DATABASE ash_db;
Business Objects Fails to Retrieve Rows from MySQL Table
I just encountered a very odd situation in Business Objects where a Web Intelligence (WebI) report running against a MySQL v5 database suddenly stopped returning any rows, even though the data had not been deleted. It turned out that the underlying application had been upgraded and the database schema had undergone a few minor changes but essentially the table structure appeared not to have changed at all.
After investigating for a few hours I managed to rule out the change in storage engine (from MyISAM to InnoDB) and had managed to narrow the issue down to a single varchar column. I didn’t have access to the pre-upgrade table scripts so I couldn’t see what had changed but I did notice that in the new table definition the character set of the column was being specified as ASCII like so…
`column` VARCHAR(10) CHARACTER SET ASCII DEFAULT NULL
To fix this I went back to the Universe level and amended my object definition to CAST the column values…
CAST(`column` AS CHAR CHARACTER SET UTF8)
That solved the problem, if you have the same issue – I hope this helps you on your way.
Categories: Business Objects, MySQL Tags: business objects, CAST, Character Set, fail, MySQL
