Blog

Category Filtering: 'MySQL'

Remove Filter


Creating a comma delimited list of column values

Posted by Luis Majano
02 Apr 2010 12:00 AM
I had a cool little SQL problem yesterday where I had a table with 2 columns: cn, email and for some reason there could be many entries for the same email address:

cn | email

1 | [email protected]

23 | [email protected]

So I needed to get a nice query where I could join all the values of the cn column into a single column for the same email. This was tricky but custom SQL functions to the rescue. This is for MSSQL but you can tweak it for MySQL very easily. So the first thing is to create a function:

CREATE FUNCTION [dbo].[getValueList]

(@email varchar(500))

RETURNS varchar(500)

AS

BEGIN

DECLARE @listStr VARCHAR(500)

SELECT @listStr = COALESCE(@listStr+',' , '') + cast(cn as varchar)

FROM tempTokenMailingList

WHERE emailAddress = @email

RETURN @listStr

END

Once I did this, it will return to me a list of column values based on an incoming email on my target table. You can make this more dynamic but it basically solves my use case. Then on my selection I do this:

select distinct emailAddress, name, token,

( dbo.getValueList(emailAddress) ) CN

from tempTokenMailingList

order by emailAddress

Run that and VOILA! You get a cool way to get your cn column concatenated as a list of values.

Paging in MSSQL 2005-Greater? Help

Posted by Luis Majano
19 Feb 2009 12:00 AM
I wanted to shoot out on this as I do not use MSSQL 2005 and greater too much and need help on what would be the best and great way to do record paging. I am so used to this in MySQL and you can see a sample of MySQL below:

SELECT SQL_CALC_FOUND_ROWS

Users.user_id, Users.user_fname, Users.user_lname, Users.user_email, Users.user_isActive

FROM wiki_users as Users

WHERE Users.user_isActive =

LIMIT ,

SELECT found_rows() AS foundRows

First of all, do you see any problems with this approach?

Second, how would I reproduce this in MSSQL?

A good MySQL GUI For Mac? Any ideas?

Posted by Luis Majano
10 Jan 2009 12:00 AM
I am sick and tired of MySQL Query crashing on me every 10 minutes. What other Mac software would you recommend for dealing with MySQL. I really like MySQL Query's simplicity, but I just hate its instability. What do you recommend?

Getting MySQL to work with Leopard, MySQL on Leopard

Posted by Luis Majano
30 Oct 2007 12:00 AM
My friend Russ Johnson has posted a great tutorial to get MySQL working on Leopard. So you can visit his site or follow the instructions below (so I don't forget)

It seems that the preference pane will not work to start or stop MySQL yet so you will have to start it manually from the command line and create a link to the socket file.

First, start MySQL in a terminal window with the following command:

sudo /usr/local/mysql/bin/safe_mysqld

Then either close the terminal and open a new one, or just hit Command-N to open a new terminal window. Then type the following:

sudo mkdir /var/mysql/

And lastly, create a symbolic link to point the default socket file:

sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

I will update this post with info on how to get it going with Lingon

MySQL default value Gotcha!! No functions allowed!!

Posted by Luis Majano
20 Mar 2007 12:00 AM
I just came upon this issue today and it was bothering the creepers out of me. I could not put a function as a default value on a column on a MySQL table, a function call like : NOW() or UUID()

Noooo!!! MySQL Do not like it one bit. So I had to revert to the docs and there it was:

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

Link: http://dev.mysql.com/doc/refman/5.0/en/create-table.html

So please take note on this issue if you just spend 20 minutes like me, trying to figure it out.

Hope this helps.

How to install the new MySQL JDBC driver on Coldfusion MX7

Posted by Luis Majano
29 Nov 2005 12:00 AM
I needed to upgrade my coldfusion JDBC drivers for MySQL since I upgraded my release to 5.0 and here is the little how to.

1) Donwload the JDBC drivers from here:

http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-3.1.11.zip/from/pick

2) Extract the archive and copy the following file:

mysql-connector-java-3.1.11-bin.jar

to the following destination:

J2EE

{j2ee path}/servers/lib

If you want it available to all servers or just for the Coldfusion app then:

{cfmx instance path}/cfusion-ear/cfusion-war/WEB-INF/lib

Standalone

{cfmx path}/wwwroot/WEB-INF/lib

3) Restart your CFMX instance or server.

4) Go to your Datasources in your coldfusion administrator to register a new datasource:

Name: {Your datasource name}

JDBC URL: jdbc:mysql://{your host}:3306/{your database}

Driver Class: com.mysql.jdbc.Driver

Driver Name: MySQL 5.0

User Name: {Username}

Password: {Your Password}

And that is it.

Upgrade MySQL 5.0.13 to 5.0.15 Guide

Posted by Luis Majano
07 Nov 2005 12:00 AM
I just finished upgrading my MySQL Release Candidate installation to the Final MySQL 5.0 Release and found and interesting tip. My OS is Ubuntu 5.10 and I had MySql 5.0.13 RC installed. The following is my setup:

MySQL Install Dir:

/opt/mysql-standard-5.0.13

MySQL Link

/usr/local/mysql ==> /opt/mysql-standard-5.0.13

To upgrade to the new release, I downloaded the Linux (x86, glibc-2.3, dynamic, gcc) Standard Binary

I then extracted the archive to the following location:

/opt/mysql-standard-5.0.15

I continued to copy all of my data folder from my previous install to the new install. Now I fired up a terminal to fix the permission, groups and ownerships following the readme. Below is what you need to do:

shell> cd to /usr/local/mysql

shell> chown -R root .

shell> chown -R mysql data

shell> chgrp -R mysql .

The first command changes the owner attribute of the files to the `root' user. The second changes the owner attribute of the data directory to the `mysql' user. The third changes the group attribute to the `mysql' group.

I then proceed to startup my database. I use webmin, if not just type in the following command:

shell> bin/mysqld_safe --user=mysql &

Everything should be working great now. However, my InnoDB structure databases are somehow "FLOPPED." For some reason, which I still have not been able to figure out, the tables are not working. So a simple workaround, is just to do a simple backup of your InnoDB databases using the MySQL Administrator and then importing them back to the new install. This actually is the approach that the MySQL manual advices.

So there it is, simple, yea right!! MySQL Upgrade.

Upgrade MySQL 5.0 Beta to 5.0 Release Candidate

Posted by Luis Majano
28 Sep 2005 12:00 AM
Today was the much expected release of MySQL 5.0 Release candidate. I had previously installed the beta on my Ubuntu "hoary" linux distro and it has been running incredibly smooth. However, I had never done a MySQL upgrade and turned to Google for some help.

I could not find a single article on it, actually I looked for 5 minutes and grew desperate. So I decided to tinker around. My beta install is on /opt/mysql_5.0_beta with a symbolic link on /usr/local/mysql. I had installed the database according to the installation directions.

I unpacked the release candidate to /opt/mysql-standard-5.0.13 and copied over all my data folder from my beta install. I then had to do a:

chown -R mysql:mysql /opt/mysql-standard-5.0.13/data

Site Updates

Entries Search