Zero dates or Null in MYSQL?

As a follow-up to my earlier post on date conversions, I noticed that the MYSQL database was getting loaded with a lot of zero dates where the original data had null dates, I wondered how to deal with this and if it would present issues… well here is a great tip: Also note that MYSQL more or less requires zero dates and does not like nulls for dates…

The following from

Magical MySQL URL parameters Save my Groovy Code

I’ve been working with legacy databases in MySQL and basically forbidden from using hibernate by a bug I had with all zero dates of the format ‘0000-00-00’ which is perfectly legal in MySQL but completely disallowed in JDBC.

Stack trace for this problem contains:

SQL Exception: Value ‘0000-00-00’ can not be represented as java.sql.Date

The answer? Apparently there is a hidden setting to use…

dataSource {

driverClassName = “com.mysql.jdbc.Driver”

dialect= org.hibernate.dialect.MySQLMyISAMDialect

url = “jdbc:mysql://localhost:3306/legacy?zeroDateTimeBehavior=convertToNull”

username = “legacyUser”

password = “legacyPassword”


That little flag on the URL is absolutely magical and now I can map my Grails objects onto a legacy database. Isn’t life grand?

Thoughts and Ideas: Magical MySQL URL parameters Save my Groovy Code.

Date Conversion to MySQL From Access/ SQL server

It took me a while to figure out how to convert dates from the format produced by Microsoft Access or Microsoft SQL server to the date format required by MYSQL. It is essential if you want to do any date manipulation.

My data comes as an ascii text file from a MSQL server which I import using Heidi SQL, a neat free package that can be found here. As far as I can tell it’s the only freeware supporting imports to MYSQL with some graphical help. It also has a number of other nice features and is under active development. In general it is better suited for me that the MYSQL workbench (which is pretty useless in the “free” edition), and for the time being I don’t feel like spending even the small $ to get the full edition. For the import, define the fields as terminated by \t and define the lines as terminated by \r\n .Ignore one line (to skip the headers, since you have to import into a defined table with field names. It took me a while to find those escape chars!!! not intuitive for someone accustomed to Microsoft!!

Anyhow once you get the dates into the database they are simply text and  may be in various forms, long or short. The fields Month Day Year are in different order.. so here is a small piece of code that changes the Microsoft format into a MYSQL one, you must have defined the destination field (DATE_MODIFIED in my example) in your database as a date field. The code uses two nested functions, The substring_index, and the STR_TO_TEXT to get the job done. So you can look up the references to see how it does its’ work. It handles nulls and blanks in the source field.

Old hands may think how simple, but I was rather pleased with my 1st, and so far only line of MYSQL code!

This takes the text contents of SDATE_MODIFIED (a string field) and puts a proper MYSQL date into DATE_MODIFIED (a date field).

Update ARESIMPORT set DATE_MODIFIED= STR_TO_DATE(substring_index(SDATE_MODIFIED,” “,1),’%c/%e/%Y’);

More Vista Weirdness: Printing PDF on Vista

As posted earlier, I could not seem to get Acrobat 8 (latest patches) to print correctly from Access Yes I could print, after having followed Adobe’s debug script to re-run install/repair (from control panel) but only in black and white and without being able to specify file location or file replace…(program just hung)

About to give up, I installed some freeware, “bullzip printer” after a reboot, not specified in the documentation, it works like a charm, well it keeps defaulting to black and white but will do color when asked nicely and repetativley!

Why can’t Adobe get this right, they charge a premium price but support committment and bloat are going to kill them.

Vista connecting to Win XP printer, Vista Weirdness

One would think, after all this time, that if you have a “shared” printer on Win XP that you can see with Vista (in my case Vista 64 home premium) (HP-970 cxi printer) that you should just click on it and connect and print….

oh contraire! one must define a new local port and then install the printer as follows….Create new port, “local port”, then Vista asks “port name”. In  “port name”  “\\my computer name(where my printer is installed)\printer name”.  Solution found here. (

So there is yet one more Vista wierdness…..

More Vista 64 Weirdness

Well I thought I had it all together. The Adobe printer with Acrobat using Access cannot prompt for a file location, when I set that option the process hangs… If I leave it going to the default .Documents folder all runs ok.

But within Access the printer option dialog started appearing then disappearing depending on mouse (touchpad) operation,,,particularly when trying to set Acrobat printer options/ doesn’t give me a lot of confidence…I really don’t have that much time to devote to ironing all the tweeks out. So far Neither Microsoft not Adobe are offering to pay!

I had heard that Vista 64 was running regular Win 32 bit apps fine, Your mileage may vary!!! I don’t consider either Acrobat  v8 nor Access v2003 to be terribly exotic!

T-Mobile At Home

Have not posted for a while. I have made progress on my development environment (Eclipse, BRIT, MYSQL, Heidi SQL) all now basically functioning together. I’ll try to cover that soon. In the meantime— another adventure into the world of new service offerings!

$10.00 a month to replace ATT/BellSouth with unlimited local and long distance! Vs. the current $53.00 a month for ATT/BellSouth and $5-10 for the cheap LD provider we used. A real deal!

Lets be kind and say that T-Mobile does not really have their act together in terms of service provisioning. Admittedly I signed up when I first saw the ad on the local media. To their credit everyone I dealt with was extremely pleasant, almost too much so. T-Mobile has got something going across all parts of their service sector that has excessively polite scripts being used by all of their employees and providers.

I took me about an two hours to get in and out of the local “company” store. They had me on the phone with a phone center and they tried to co-ordinate what was to be done. I had called ahead, to save the time and effort of getting the router sent by UPS. I strongly suspect it would have gone smoother by phone, but then I don’t know how they would have dealt with all the paperwork.

After getting the router and the sim card in the store I went back home to replace my current router. For what it’s worth my high speed internet is provided by Charter. I heard of some people trying to get the service when their service was provided by the telco. So when the service was cut the high speed internet went also, so no service!

I left the store intending to plug the router into my network exactly in the place of my then current D-link wireless router switch. The Lynksys provided by T-Mobile was functionally identical except that it had two phone jacks and two slots for Sim cards. The people at the store and that I spoke to on the phone there indicated that I should just plug the router in and the phone into the router and I should get dial tone and the number “port” of my existing number should follow in about a week… A few days later I received a large (router sized) box by UPS containing a SIM card…Now I had two, the one from the store and the one from the head office. I also got a message thanking me for signing up for a new line for a one time charge,of $35 I had missed that in the fine print, it would not have changed my mind but the disclosure seemed fairly below the radar.

I plugged everything up and could not get the router to pass info to my computer, after an hour or so,I got through to customer service only to have to wait on a call back from Lynksys. The instructions did not say that I had to “clone ” the mac address in the router set up, a fairly simple fix, once you know it. So my new friend from Lynksys in the Philippines told me.

So now I was back where I started, I had my regular router and computer configuration working with the new router in place, but no dial tone. Another few hours on the phone with T-Mobile. This time they informed me that I needed to have a temporary number assigned to get the dial tone… Avery helpful person got me set and determined that I needed the “boxed ” Sim card from head office. and she provided a temp phone number and shortly I was in business, and my number was to be ported in about a week.

A week goes by and I notice that the new phone no longer has dial tone. I call, slightly concerned since they should be porting my number any fay and I’d like it to work. It turns out their computer somehow had rejected my 911 address so they took me offline, and further my port request had been rjected by ATT since I had the “complete Choice” plan on the line which also provides for a second # (distinctive ring) which I had never used. So we got the temp # working again, I got ATT on the line and cancelled the features and roughly a week or so later I ot switched over!!

Only issues The caller ID is number only, no names, which we kind of miss. Call quality is fine in and out, if anything it seems a bit clearer to me. As of 10/21/2008 the caller Id is now coming through with names!

All in all I probably spent about 6 hours on the phone and in the store maybe more, hopefully the process wil get smoother for later users!

Vista 64 Weirdness

Just got a new Gateway Laptop with Microsoft Vista 64 pre-installed. As I expected I did run into some weirdness. The system comes with two versions of internet explorer the regular 32 bit version and the 64 bit version. Seemed weird to me until I discovered that I could not download a patch that required the “genuine advantage feature” with the 64 bit version, no error just did not work…went to the 32 bit version worked like a charm, way to go Microsoft! You would think if they want to promote Vista-64 they would get all of the job done??

Next up was trying to get Acrobat professional working…(version 8.0 intially ) I use this regularly to “print” my reports and then combine hem for electronic distribution. Short result is that the straight install did not work. I then found that an update was required.. I installed it, still no joy, I then followed a trouble shooter script from the Adobe web site, so you can be assured I’m not the first to have the problem. In short It appears the running “update and repair” from the control panel add remove programs menu got it fixed. Note that I had previously done an Update to the same. That 1st update did not get the job done but the second, and a reboot did.

There were a few other small things not really worth mentioning. I hope that the system does not have as yet undiscovered issues, but it seems to be running some of my heavy duty Access 2003 files just fine, and 4x faster than my 1 gig 1.25 ghz dell desktop!