Crystal XI and MySQL 5.0

[SBpsc]

Hi:

We are currently using Crystal Reports XI and MySQL 4.1.5. All of our reports are query based. In other words, instead of using the tables in the database directly, we paste the the query into Crystal Reports using the Add Command.

We are thinking of moving to MySQL 5.0.x so that we can make use of the stored procedures.

Can anyone tell me if this would be a simple upgrade to the MySQL? Do we need Crystal Enterprise for stored procedures? Can we still use the Add Comand to write our queries or do we need to write them in Visual Studio.NET, etc? I haven't found much information on the web so any information that anyone can provide me of what we need to get MySQL 5.0x and Crystal Reports XI to work is greatly appreciated.

Thanks,
SB

 

[synapsevampire]

Crystal Reports and Crystal Enterprise support Stored Procedures as the big kid databases have always used them.

Upgrading to a different version of MySQL shouldn't prevent you from accessing the database via command objects or SP's providing they have functional connectivity (ODBC I'd guess), and there isn't anything wrong with their driver.

My feeling on MySQL is that it costs MORE in the long run because of limited functionality and lack of real support available.

You might also consider that Oracle now gives away a 4GB version (10 XE) of their database that many are touting as the MySQL killer. Like any good heroin dealer, once you hit that 4gb limit though, they know that they have you hooked and you're gonna bleed for Larry

-k

 

[SBpsc]

Thanks for the information. That really helps a lot.

I do have another question. Perhaps a stupid one so bear with me...

We are creating the Crystal Reports and providing them to our customers through our custom software. With the current version, the customer installs MySQL 4.1.5 and Crystal XI (on their server). They click a link on our software (from their desktop), magic happens, and walla - they see pretty reports with data.

So, how would that work with MySQL 5.0? How does the customers (database) get access to the stored procedures? Is there some sort of migration procedure to copy over the functions and procedures to their side? CR XI and MySQL are linked using the MySQL ODBC driver but we still have to push the procedures/functions/etc over to the customer side somehow, right?

Thanks,
SB

 

[synapsevampire]

The stored proceeures are IN the database.

Sounds like you're new to databases, or at least those with stored procedures.

Basically a stored procedure is a more complex query stored within the database that runs faster because it has a precompiled execution plan.

The stored procedures aren't stored in Crystal, and your clients will all have to upgrade their servers to MySQL 5, and you'll have to give them the new database with the SPs.

-k

 

[SBpsc]

Yes, I am very new to stored procedures and still a novice to databases too. Got thrown into the pit and I am trying to get myself out of it...

Thanks for the information though. They really helped.
- SB

 

[synapsevampire]

You'll be fine, it's not very complicated.

SPs are akin to Views or tables, they are all database objects.

So if you add one locally, you'll need to add it to any other databases that are intending to use that SP as well.

The syntax for creating them is fairly straightforward, so if you have changes later, you basically execute a query such as:

drop procedure MyProc
go
create MyProc as
select * from table

This would drop the old one and create a new one.

This isn't MySQL specific syntax, but it gives you an idea as they all work similarly.

-k