Brain Dump - Database Issues

posted at 08:41:00
By Flipping Heck!

Posted In
Technical

[Warning.... Entering a slightly geeky zone]

A ~~couple of~~ few things have cropped up in the past two days that have caused a fair bit of hair pulling and have such simple solutions it's not true.

I'm sure at some point I will need to remember this information so I hope you don't mind that I put it here for future reference even if it is a bit off topic for this blog.

If you're not of a technical nature then feel free to fall asleep now... ;)

Problem 1 - Combining multiple SQL queries with sub-select statements (MYSQL).

I have a users table that I wish to join to a table containing the baskets that they have created and the languages of each item the user has selected.

I could have done this using multiple select statements or some kind of array and loop but this proved to be hugely processor intensive - something that we couldn't have as it the code's due to run on a shared server.

So, a colleague and I sat down and came up with a rather splendid query, much better than tonnes of evil joins .

Part of the problem also lay in the fact that we wanted a nice tabular layout for the results which wouldn't work with all of the queries I tried. It was fine for a single language but a right royal mess for table layouts.

Table Layout
Users Table
--------------------------
|User ID   |  User Name  |
--------------------------
Basket Table
---------------------------------------------------------------
|User ID    | Basket ID |  Language ID  | Catalogue Processed |
---------------------------------------------------------------
SQL Statement
SELECT UserTable.UserID, UserName, LanguageID, CatalogueID, Basketid,
(SELECT Count(LanguageID) FROM (BasketTable) where BasketTable.UserID = t.UserID AND 
CatalogueProcessed=1 AND CatalogueID=1 and LanguageID = 1) AS English,
(SELECT Count(LanguageID) FROM (BasketTable) where BasketTable.UserID = t.UserID AND
CatalogueProcessed=1 AND CatalogueID=1 and LanguageID = 2) AS French,
(SELECT Count(LanguageID) FROM (BasketTable) where BasketTable.UserID = t.UserID AND
CatalogueProcessed=1 AND CatalogueID=1 and LanguageID = 3) AS German,
(SELECT Count(LanguageID) FROM (BasketTable) where BasketTable.UserID = t.UserID AND
CatalogueProcessed=1 AND CatalogueID=1 and LanguageID = 4) AS Italian
FROM (UserTable INNER JOIN BasketTable t on UserTable.CD_ID=t.UserID)WHERE 
CatalogueProcessed=1 AND catalogueID=1 GROUP By UserID
Output
 --------------------------------------------------------------------
| UserID | UserName | BasketID | English | French | German | Italian |
 --------------------------------------------------------------------
|   1    |   Katy   |   1234   |    4    |   0    |   2    |   0     |
 --------------------------------------------------------------------
|   2    |   Pierre |   1235   |    0    |   5    |   0    |   4     |
 --------------------------------------------------------------------
Hooray!

Problem 2 - Memo fields being truncated to 255 characters (MS Access).

This one had me stumped for ages. Originally the script did truncate the field in question but it still did so when I removed the truncation - weird! Anyway, after much hair pulling it turned out to be the "SELECT DISTINCT" clause in the SQL statement. I'm not sure if this is a bug in the code or in Access itself and the way it handles DISTINCT but removing it from the SQL Statement returned the full contents of the memo field.

Weird!

Problem 3 - Scheduled Database Backups failing (MYSQL).

This was another annoying one. All user permission were set correctly and the backup ran when manually activated but wouldn't run as a scheduled task.

It turns out that the run command generated by MYSQL contains an error. Here's the command auto-generated by MYSQL:
"C:Program FilesMySQLMySQL Administrator 1.1MySQLAdministrator.exe" 
"-UDC:Documents and SettingsKaty WhittonApplication DataMySQL" 
"-c" "-bpTest Backup" "-btC:" "-bxTest Backup"
You can see on the third line there's a command called "-c", changing this to "-C" or removing completely allows the backup to run as a scheduled task successfully.

Annoying!

[End slightly geeky zone]

Sorry for the off topic brain dump but if I didn't put it somewhere I'd forget what I'd done!