mySQL 5.x database server

[cebutech]

---

anybody here using mySQL 5.x database server?
i'm interested with your opinion on its features like the stored procedure support feature...etc.

[BadDudes]

This is what ive been waiting since last year! We have develop a small system before that uses mysql .. kapoy kaayo ang coding sa front end due to lack of support for stored proc! Para sa ako stored proc will reduce your development time especially with mix dev language ang gamit kay ma reuse man gud ang code ...and i think supported na sad ang database triggers! got to check it out later

[gloryhunter]

For me it's the other way around.

Stored procedure is non standard and you will have problem when you switch database. It's good to use O-R mapper that generates standard SQls and you wont have compatibility problem whatever you database..

[BadDudes]

and what do you mean by non-standard ... stored procedure resides on the database!!!! so when will it become non standard?? Most of the matured sql based database uses stored procedures and even packages, I just dont get it ....

but at least you mentioned O-R mapper is this another tool give us info beh ... this might help us also

[M@rv|n]

hmmm i somehow agree with gloryhunter correct me if am wrong
each database has its own standards when it comes in creating a stored procedure, it wldnt be an easy road to track during the migration of stored procedures and triggers unless you have a powerful and very versatile tool that will help you in its stored proc & triggers migration.

i remember before when i was migrating a foxpro database to sybase, instead of migrating it turnsout that im only migrating the records not the triggers and stored procedures so im back to coding.

if i wer the project manager and i can see that in 1-2 years the the records grow big and needs a more powerful database but less than oracle hmmm shall we say sybase i would ask my programmers to just create a separate function for that ... although that will somehow kill the runtime

i expect some violent reaction on this ahehe
i cud be wrong

[BadDudes]

each database has its own standards when it comes in creating a stored procedure

but my question is when will it become non-standard in-fact all major db supports stored proc ... and you probably mean is how it is coded ... in which I agree .. each has its own way of coding. with regards to migration it is probably better to re-code the stored proc natively than being migrated ..unless you dont keep the source codes ... and thats another story

[gloryhunter]

and what do you mean by non-standard ... stored procedure resides on the database!!!! so when will it become non standard?? Most of the matured sql based database uses stored procedures and even packages, I just dont get it ....

but at least you mentioned O-R mapper is this another tool give us info beh ... this might help us also

It's non-standard because you have a different syntax in stored procedure for every db server. Microsoft Tsql, oracle pl/sql, etc etc. So you will have a compatibility problem in multiple database.

My point to consider: I can create a program in any sql server without rewriting SQL that conforms ANSI-SQL 9x but it's very difficult for me to deploy a program that uses SPs a lot to target multiple databases.

Unless you need performance desperately, SP is a no-no.

Re: OR Mapper

Basic Problem: Most db developer, sad to say, thinks in tables while a software developer should think in objects.

From that fact alone there's an impedance mismatch. The business object developers need not to know the detail of the database and he should think in object -- more dynamic as contrary to tables (static). So as a software developer, you only need to instantiate an object, set the property, and save the object or find the object and manipulates it.

Example:
Customer customer = new Customer();
customer.setName("Glory Huntah");
customer.setType(CORPORATE_CUSTOMER);
...

database.save(customer); // standard SQL command executed to targeted to any db server ... Oracle, MS SQL, Sybase, DB2, MySQL

Simple ra no?

So Ideally, a developer should threat database server as just simple object repository not the table/rows/cols.

So, here come's the OR mapping into play. It's a technique to map tables to classes, colums to attribute, and relationships to classes. You can use OR mapping tools to generate the mapping for you (or just a design pattern DAO (Data Access Object)). They are also robust and can handle transaction and caching.

Here are the most popular ORs are:

ADO of microsoft -- ring's a bell?
Toplink of Oracle -- originally from webgain
Datawindow of Sybase -- not sure if it's object, but i saw it as a layer before communicating to db. I can understand why powerbuilder programmers loves powerbuilder so much and hesitant to change to popular VB. They have the best db development tool in the world.
Hibernate -- my favorite java OR mapper
Castor JDO - the first OR mapper I used
JDOs (java database objects) e.g. Solarmetric Kodos
I had an OR mapper too - but naahh it sucks.

There are companies also created object databases called ODBMS. I think RDBMS companies are also going into that direction.

I hope naa'y makakutlo sa akong insight.

[idlewild]

i'm no expert in this but since you mentioned objects, it might also be worth to look into ODBMS (Object Database Management Systems), like Versant

[gloryhunter]

hi idlewild,

yeah i edited my post and mentioned it.

[M@rv|n]

yeap! agree with the ODBMS that would basically fill the gap between the db & software developer and take note its much more faster than RDBMS