BusObj + MySQL – SQLBindParameter State 07001
I recently encountered a strange problem in Business Objects whilst trying to create a derived table against a MySQL database and whilst the query was a complex one it executed without fault in the MySQL Query Browser so I knew that wasn’t the probem. The error message I encountered was “SQLBindParameter not used for all parameters”…
I tried the same query as a Freehand SQL query in DeskI and received the same error, after banging my head against the problem for about 15 minutes I decided to take my favourite solution – I went for lunch! On return the solution seemed blindingly obvious, at the very top of my query I’d included a comment but in the comment I’d included a question mark – as soon as I removed the “?” the query ran without a hitch. I’m not quite sure of the reason behind this but I suspect that one of the Business Objects, ODBC or MySQL layers treats the “?” as a reserved character to indicate a parameterised query – if anyone has the answer i’d be interested to know.
Categories: Business Objects, MySQL Tags: business objects, DeskI, sql, SQLBindParameter, Universe
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;
How to show the version number in MS SQL Server, Sybase and MySQL
Just a quick post – I often need to find out what version of a database platform I am running SQL against (presuming this is not my own server!), this may come up whenever you are contemplating upgrades, diagnosing faults or looking for features in documentation. In Microsoft SQL Server, Sybase and MySQL (and perhaps other platforms) the command is…
SELECT @@VERSION
Categories: Microsoft SQL Server, MySQL, Sybase Tags:
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
Learn Microsoft SQL Server for Free
I had a conversation with a colleague recently about how to start teaching yourself SQL Server skills outside of the workplace, he’d read an article on SQL Server Central that recommended buying the Developer Edition of Microsoft SQL Server which I think is a great idea but if you’re really just starting out with SQL Server why not do it for free?
Luckily, there are a number of ways that Microsoft helps us to do this…
Microsoft SQL Server Express 2008 is a cut-down (but not time-limited) version of the full SQL Server 2008 product restricted to 1 CPU, 1GB RAM and one single 4GB database. The express edition contains the core database engine as well as the excellent Management Studio (IDE) and if you download the “Runtime with Advanced Services” version you also get Reporting Services, enabling you to build, deploy and run a small-scale BI solution. Please note that the express edition does not come with SQL Server Agent (scheduling) or Integration Services (ETL) but you do get the Import/Export wizard to help move data around – check out the full comparison for more detail.
Alternatively, if you want to run with the big dogs you could try SQL Server 2008 Evaluation Edition which contains all the features of the Enterprise Edition but has a time limit of 180 days.
If you’re just starting out and want to get the hang of basic data manipulation, writing queries, creating tables, etc. then you’re probably better off with the Express version. It’s smaller and won’t use up as much space/RAM on your PC and you won’t have to worry about the time running out – there’s also a chance that you’d be overwhelmed by all of the other features and products that are bundled with the Evaluation version and in the beginning it’s always better to keep things simple. If you’re already an intermediate-advanced user of SQL Server or another database platform, or you already know that you’re interested in SSIS or Analysis Services then you might as well get hold of the evaluation version but don’t forget that it’s time limited!
Categories: Microsoft SQL Server, MySQL, Open Source, Oracle Tags: Evaluation, Free, Microsoft SQL Server, RDBMS, sql, SQL Server, Trial
