Catch database errors

Issue #67 resolved
Luuk Jansen
created an issue

I think it might be good to catch database errors, so if something goes wrong it looks a bit more presentable. Also, do we have to warn/check on the use of characters that cause the SQL to fail (e.g. use of " and ')

Comments (5)

  1. Fred LaPlante repo owner

    Once again, Micah's help would be VERY useful here. Openbiblio has a very extensive error handling mechanism if you stay with the classes & models and away from direct use of the Query class. But if you can't use the models as-is, and can't see how to extend them, then you get the sort of db erors that the PHP builtin db handler displays. The best solution is to work with the models even if it seems awkward.

    my biblio_search does not, at this time, handle errors very well. Micah's search does it very well with errors available in PostVars[error] or some such. All through the various models you can see error messages being added to postVars[error]. When ever the php redraws a screen fields using the inputFields() function, errors are displayed along side the responsible field. We can do similarly in biblio_search by adding an error field alongside everything, and hiding it until needed. Another detail!

    As to quotes, we have to deal with them in the db models, somewhere. Again, Micah's code generally does that well. In my convert06t010.php I had to add some reg exp code to deal with it, but it is rudimentry at best.

  2. Micah Stetson

    Escaping is handled by Query->mkSQL(). It's sort of like printf() for database queries. Here's what some of the code in convert06To10.php might look like using mkSQL():

    		$bibSql .= $db->mkSQL("(%N, %Q, %Q, %N, %N, %N, %Q),",
    			$bib['bibid'], $bib['create_dt'], $bib['last_change_dt'],
    			$bib['last_change_userid'], $bib['material_cd'],
    			$bib['collection_cd'], $bib['opac_flg']);
    		$fldSql .= $db->mkSQL("(%N, %N, 0, '245', NULL, NULL, NULL, NULL),",
    			$bib['bibid'], $fldid);
    		$subSQL .= $db->mkSQL("(%N, %N, %N, 0, 'a', %Q),",
    			$bib['bibid'], $fldid, $subid, $bib['title']);
    		if ($bib['title_remainder']) {
    			$subSql .= $db->mkSQL("(%N, %N, %N, 0, 'b', %Q),",
    				$bib['bibid'], $fldid, $subid, $bib['title_remainder']);

    The general idea is that you use %N whenever there's a number and %Q whenever there's a quoted string. Query->mkSQL uses the arguments in order (just like printf) and escapes them appropriately.

    More concerning to me is that the structure set up by Query, DBTable, and the various model classes may be unclear and difficult or awkward to use. I'd like to discuss this more, so I'll start a thread on the development forum.

  3. Log in to comment