DB_Table Problem

We did a major update to one of our financial sites on Aug 30th 2005, we converted all forms to use DB_Table using HTML_QUICKform . During implementation we faced few problems, let me put it over here.

• Problem with AUTO_INCREMENT

DB_Table (in fact PEAR DB class es) doesn’t support AUTO_INCREMENT as it is not supported across DB architectures. In other words it is non-standard. So we had to avoid nextID() and use the max . I had to make it support AUTO_INCREMENT as we were using three different open source apps on one DB other two running on AUTO_INCREMENT. Make sure when you use DB_Table classes a table with _seq suffix (default, this can be edited as well) is created, so while doing the system conversion to DB_Table make sure you update your _seq tables with the AUTO_INCREMENT count manually else it will give duplicate entry error as it starts from 1. Get more help at http://wiki.ciaweb.net/yawiki/index.php?area=DB_Table&page=GettingStartedManip

• Problem with Data types

Next problem was with date and text data types, we were using timestamp and text as the data type, while debugging with nusphere we found the warnings, it still works though. Then I did a googling and found that we can use varchar or integers for mysql datatype timestamp and clob for text. This solved the problem. read more at http://wiki.ciaweb.net/yawiki/index.php?area=DB_Table&page=DataTypes

We thought we have solved almost all problems but then one table was not getting populated and we tested it many times. In all our tests it was working fine but system was failing randomly making the programmers life more difficult. We guessed, checked and corrected many times but without a complete cure. Then we decided to log the errors to a log file. We sat and thought it will be a long process to put check at each step of mysql connection and operation but thanks to a great PEAR architecture, we implemented a good and efficient error logging system in an hour. We used the Log Package to log the errors. It was quite a simple program.

Now I understand how imp is to have a well defined, stable, community supported and flexible architecture for faster and efficient programming. We are commited towards a simple and scalable architecture based on open and free systems.

Today morning we were able to track and fix the error.

2 Comments »

---

1. reg: Problem with AUTO_INCREMENT

   faced the same problem myself. i found a neat solution…atleast works fine with me…
   made a class called DB_Table_Mod
   and rewritten the nextID() function to return the next autoincrement value. the function simply returns the max value + 1
   of course my other classes extends or needs an object of DB_Table_Mod

   
   
   maxi_million on February 17, 2006 - 11:45 am @ 11:45 am

2. in fact theres another problem with another class that comes with DB_Table package called DB_Table_Manager

   what interests me here is this cool method: create()

   all very neat and convenient…just pass the params and it tries to create the table for you also…

   TILL you come to around line 204…its horror!
   $newIdxName = $table . ‘_’ . $idxname . ‘_idx’;

   and then..line 208…
   if (strlen($newIdxName) > 30) {
   return DB_Table::throwError(
   DB_TABLE_ERR_IDX_STRLEN,
   “‘$idxname’ (’$newIdxName’)”
   );
   }

   its all very good it tries to take care of possible errors in oracle and pgsql…
   but what about us poor souls with mysql…

   i did the despicable thing…simply commented out that portion…but any better alternatives…

   i mean you may sometimes get a littele prosaic and give a lonfg name to your table…but hardly expect to be rewarded with a PEAR error

   
   
   maxi_million on February 17, 2006 - 12:03 pm @ 12:03 pm

RSS feed for comments on this post · TrackBack URI

Share your thoughts