Putting Two Columns into one In MYSQL

No Biggie but in a single select clause you can use the concat to combine fields like so

Select concat(vw_curr_new_over4_names_fxd.NAREA,’ ‘,vw_curr_new_over4_names_fxd.Area_name)AS AREA

Note the use of commas and the single ‘ mark to provide a space the result is this new Field

110 Fultondale/Gardendale/MtOlive

From these two old fields

From NAREA=110

and Area_name=Fultondale/Gardendale/MtOlive

Advertisements

Installing SQL Explorer Eclipse plug-in In Ganymede

Installing SQL Explorer Eclipse plug-in Using auto Update: Hat tip to 5thcross.wordpress.com.  I noticed this set of instructions had not been updated for a while but they worked like a charm. The tool seems to have picked up some support recently and may be a good addition to your toolbox. Paticularly since the Data Tools Platform Eclipse component still seems to have a way to go. I’ll be trying to use this along side Birt for report wrting to edit the underlying queries… we’ll see. These instructions are lifted verbatum, they work. With Eclipse 3.41 and SQL Explorer version 3.5.0.RC6

  • From the Eclipse menu,
    select Help->Software Update->Find and Install. You will be
    displayed with a Install/Update dialog box.
  • Select Search for new
    features to install radio button & click next.
  • Click on the New Remote
    Site on the right hand side of the Install dialog box
  • Enter the name as “SQL
    Explorer” & “http://eclipsesql.sourceforge.net/” (without quotes) for
    the url in the New Update Site dialog box and click ok.
  • Select the SQL Explorer
    site in the Install dialog box and click finish.
  • Once the search is
    finished, it will present with the features to install.
  • Select the features you
    want to install. In this case select SQL Explorer branch & click next.
  • Accept the license
    agreement & click next.
  • Select the install location
    where you want to install this plug-in. By default it will be installed
    into the eclipse plug-in directory. If you want to add another location
    click on the change location button on the lower right hand corner and
    specify the directory where you want to install. Once you are finished
    with the selections click on finish button.

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 http://hartsock.blogspot.com

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’);

Got Ganymede RC4 with Birt Running on Win xp sp3

As I thought might be the case my problems seem to have been due to massive user error. The key is that one must start a project before trying to set anything else up, and.. The BIRT package comes with its own version of data explorer that is not the same as the main Eclipse DTP. I sill have not gotten the data tools platform (DTP) working with My Mysql database. I did get a sample report going with MY mysql database. so that is a start. For help on this if you are as confused as I was, and to some extent still am see the screen shots from me and the from the great help from an Actuate master (BIRT developer). see the bug 236647 (in Bugzilla) for some useful info for the total newbie!

So a key that I don’t think is highlighted in Eclipse Docs is that you must have STARTED A PROJECT, before anything else works properly, makes sense since it uses the project workspace to store all environment info…

If any one has an idea how or where one should put the MYSQL driver into the Eclipse environment so that it will work with the DTP I’d appreciate it! Currently I just get the “can’t find it” message even though it is displayed in the Jar file tab…(and I did set up another project to test it)

I’m still drinking through a fire hose trying to learn Eclipse, MYSQL, and JAVA all at once,(along with BIRT and probably a few other items)

Installing Eclipse Ganymede on Win XP sp3

Eclipse looks to be a very powerful and well supported software development environment, with huge capability, if I can get it running!

Well…Since for the time being I bombed out on my Linux installation I thought I’d try the win Xp install. I chose the “Ganymede RC3” Report writer package. Every thing seemed to go very smoothly. Then I tried to get my My SQL database hooked up using the JDBC driver… At first I though I had it, the server pinged and indicated that it was connected.. But then I went to look for “data” the server it turns out should have shown up on the navigator with the various databases, so a “data source could be defined,,, These things never happened,. Left some messages on the Newsgroup. Got asked to file a bug report, I did, It’s Bug 236647 In Bugzilla. The first response has been that they tried it and everything works great… So again I’m at a dead end with even being able to get a fairly good starting point with Eclipse. What I don’t get is why it has such an extensive error log when I do a clean install.. It looks like ti is trying to install a bunch of other items not in the installed package,,, It makes getting useful error messages hard to distinguish from the noise.

It should not be this difficult to get a tool installed and working… I’ll even grant that it is probably “user error” but Still!!! Give me something to go on!!!

It’s pretty clear that I have to get this working but I’m not sure what’s next. I’ll have to give them a few days to see if Eclipse people can help me out. I have to get some real work done anyway!

When I get a chance I’ll update getting MYSQL going. I found a neat tool HeidiSQL looks to be much more complete and free than the mysql workbench.

Freeware, Free hosting and Web 2.0+

Updated 5/29/2008 to include links to services and my sample pages. I intend to keep updating the sample pages, so check in or subscribe!

Well I think I’m going to start to document my journey to the brave new software world. Wait! you say, its not new, and well you may be right! But it is new to me and from poking around in the process of learning it it seems that it is new to a lot of other people also.

I began by setting up a web site on Microsoft Live. Located at Http://TomBrander.com it hosts information about my Real Estate statistical consulting services. While Microsoft is not normally associated with great web stuff, this product is a wonderful exception. I got it when they were still handing out free domain registration, which sadly they have discontinued, now its something like $14.95 per year. The hosting however remains free. You can get a free subdomain (like http://myname.live.com) and still get completely operational for free. Since, as some have pointed out, search is the most common way to find information now on the web I don’t think the lack of a full domain registration is the handicap it once was.

I also incorporated a Blog hosted here at wordpress, http://tbrander.wordpress.com and incorporated into the main site via Iframes.

I’m now exploring Linux (Ubuntu), MySql, Eclipse, Open Office. I’ve got them loaded but I have a way to go to get fully operational. I’m Using a dual boot Dell Win XP pro Machine with the newest Ubuntu Hardy. I’ll pick up some of that next time. I’m striving to create a solid free software development replacement to my entire Windows experience. I need a replacement for MS Access and eventually Photoshop CS3, although If I could get that in Wine I’d probably be ok with it.

Other neat tools/ services I’ve picked up are: Many Items in Google such as my new favorite: Google “reader” the Shared items page, Base, Mail, Docs, adsense, to do lists, portfolio, finance and a bunch of others, something new almost every day.

Jott: great for keeping notes to myself when away from the desk. It is a voice recognition system which I set on my cellphone speed-dial and use to send e-mail reminders as well as to do items and calendar items.

Metadot: A free hosted Wiki that I just started to work on yesterday. My experimental site is here.

Del.icio.us; A great way to keep bookmarks organized so I can get to them via any computer and not use my browser which keeps them stuck on one machine.

In fact one of my main goals is to move as much as I can into the “cloud” so that depending on a particular machine and location is no longer necessary. So this means giving up on Microsoft Outlook. So far no problem.

One last item for today is getting Firefox instead of Explorer, It has been a great improvement. I happen to be in Explorer at the moment, for no particular reason but I find myself most of the time in Firefox, which seems noticeably faster and much less crash prone, when running exactly the same stuff! Update, Installed the rc1 of Mozilla 3.0 on Windows works great  and the pre release 3.xx version on Linux.