$ host -t cname qadbrw01
qadbrw01.cluster is an alias for va-qa-dbrw101.cluster.
Monday, April 22, 2013
Thursday, May 17, 2012
MySQL function GROUP_CONCAT and CAST
I have a table of emails that may have multiple entries for a contact and I wanted to join all the emails together before joining the emails table to the contacts in my select. My query looked something like this:
SELECT mc.contactid, ce.emailAddress as emailAddress
FROM merchant_contact mc
JOIN (
SELECT contactid, GROUP_CONCAT(DISTINCT emailaddress SEPARATOR ',') as emailAddress
FROM contact_email GROUP BY contactid
) ce ON ce.contactid = mc.contactid
It ran fine in dbVisualizer, but then when it ran as part of my Java app it returned values like
[B@2d7f2fae
[B@79135fd7
[B@66f95a5a
These looked something like pointer addresses, not the email values I was expecting.
After trying a few things that did not work, including adding group_concat as an sql function to my configuration (recommended here) I tried changing this:
query.addScalar("emailAddress", Hibernate.STRING);
to
query.addScalar("emailAddress");
to let Hibernate try to determine the type itself. Although this didn't fix it, I did get more information to work with, because it complained "No Dialect mapping for JDBC type: -4". Searching for this got me to this post on CodeRanch (which I find helpful from time to time) where the guy fixed his problem by CASTing it from an NVARCHAR to a VARCHAR. I tried a variation on this and it fixed my issue, so here is what I ended up doing:
SELECT mc.contactid, ce.emailAddress as emailAddress
FROM merchant_contact mc
JOIN (
SELECT contactid, GROUP_CONCAT(DISTINCT emailaddress SEPARATOR ',') as emailAddress
FROM contact_email GROUP BY contactid
) ce ON ce.contactid = mc.contactid
It ran fine in dbVisualizer, but then when it ran as part of my Java app it returned values like
[B@2d7f2fae
[B@79135fd7
[B@66f95a5a
These looked something like pointer addresses, not the email values I was expecting.
After trying a few things that did not work, including adding group_concat as an sql function to my configuration (recommended here) I tried changing this:
query.addScalar("emailAddress", Hibernate.STRING);
to
query.addScalar("emailAddress");
to let Hibernate try to determine the type itself. Although this didn't fix it, I did get more information to work with, because it complained "No Dialect mapping for JDBC type: -4". Searching for this got me to this post on CodeRanch (which I find helpful from time to time) where the guy fixed his problem by CASTing it from an NVARCHAR to a VARCHAR. I tried a variation on this and it fixed my issue, so here is what I ended up doing:
SELECT mc.contactid, ce.emailAddress as emailAddress
FROM merchant_contact mc
JOIN (
SELECT contactid, CAST(GROUP_CONCAT(DISTINCT emailaddress SEPARATOR ',') AS char) as emailAddress
FROM contact_email GROUP BY contactid
) ce ON ce.contactid = mc.contactid
FROM merchant_contact mc
JOIN (
SELECT contactid, CAST(GROUP_CONCAT(DISTINCT emailaddress SEPARATOR ',') AS char) as emailAddress
FROM contact_email GROUP BY contactid
) ce ON ce.contactid = mc.contactid
Posted here in case this helps someone going forward.
Monday, November 14, 2011
How to add up all numbers, one per line in a file
cat /tmp/foo | awk '{sum+=$1}END{print sum}'
(From Mike Masters, of course)
(From Mike Masters, of course)
Friday, November 11, 2011
How to output the first line of each file in a directory
$ head -n 1 *
Sample output:
$ head -n 1 * ==> Desktop <== ==> Development <== ==> Documents <== ==> Downloads <== ==> Dropbox <== ==> Environment <== ==> Library <== ==> Movies <== ==> Music <== ==> Pictures <== ==> Public <== ==> Sites <== ==> bin <== ==> current.html <==Current IP Check Current IP Address: 63.119.11.19 ==> databases <== ==> my.cnf <== [client]
(That's the directory structure of my home dir on my work machine.)
Wednesday, November 2, 2011
Notes on GROUP BY in MySQL
Here is a query I wanted to run, but I was concerned that the value of fat.rowsprocessed would not come from the same fat row as min(fat.processeddate).
My buddy Spencer pointed out that in standard SQL, if you use an aggregate function, then you have to include all the other fields you are selecting in the group by. It turns out that there is an extension to GROUP BY, and to HAVING, in MySQL that enables you to use them on a single field:
I was afraid that the db would pick any value of rowsprocessed, that it would not come from the same row that the min(processeddate) is from.
HAVING is what I wanted to use. It's also not standard SQL legal but the same MySQL extension enables this.
I ran both, exported the csv's, and diff'd them, and they gave identical results. But I suspect that was luck in this case, and that the first query would not be dependably unarbitrary. I am more comfortable with the second query, using HAVING.
select m.domain, ma.merchantacctid, ma.createddate,
fat.rowsprocessed, min(fat.processeddate)
from merchant_account ma
join merchant m on m.merchantacctid = ma.merchantacctid
join ftp_audit_trail fat on fat.merchantacctid =
ma.merchantacctid
where fat.processeddate > ma.createddate
and fat.rowsprocessed > 0
and ma.createddate > '2009-12-31'
group by fat.merchantacctid
order by domain;
My buddy Spencer pointed out that in standard SQL, if you use an aggregate function, then you have to include all the other fields you are selecting in the group by. It turns out that there is an extension to GROUP BY, and to HAVING, in MySQL that enables you to use them on a single field:
MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.
11.15.3. GROUP BY and HAVING with Hidden Columns
I was afraid that the db would pick any value of rowsprocessed, that it would not come from the same row that the min(processeddate) is from.
select m.domain, ma.merchantacctid, ma.createddate,
fat.rowsprocessed, fat.processeddate
from merchant_account ma
join merchant m on m.merchantacctid = ma.merchantacctid
join ftp_audit_trail fat on fat.merchantacctid =
ma.merchantacctid
where fat.processeddate > ma.createddate
and fat.rowsprocessed > 0
and ma.createddate > '2009-12-31'
group by fat.merchantacctid
having min(fat.processeddate)
order by domain;
HAVING is what I wanted to use. It's also not standard SQL legal but the same MySQL extension enables this.
I ran both, exported the csv's, and diff'd them, and they gave identical results. But I suspect that was luck in this case, and that the first query would not be dependably unarbitrary. I am more comfortable with the second query, using HAVING.
Monday, August 8, 2011
Set vim status line to show file name, format, column#, line#
:set statusline=%t\ %y\ format:\ %{&ff};\ [%c,%l]Sample output: .vimrc [vim] format: unix [2,3].Wednesday, July 13, 2011
How to restore Java 1.5 on Snow Leopard
Apple is so annoying. This morning I upgraded my Mac OS (a non-restart-required, supposedly low impact upgrade) and then discovered that my codebase, which requires Java 1.5, would no longer compile in IntelliJ. The upgrade had removed my install of 1.5 and replaced it with a symlink to 1.6. Why does Apple so badly want to force users into Java 1.6? It's extremely irritating to have to stop everything and remind myself how to do this all over again. I looked in here to see if I could find my notes and I could not.
On the command line:
Navigate inside the Pacifist display to /System/Library/Frameworks/JavaVM.framework/Versions.
Select 1.5.0 and Install to Default Location.
On the command line:
UTA: I found my notes. In case these provide any additional context.
Restoring Java 1.5.22 to the machine
For some reason Apple saw fit to remove all versions of Java other than 1.6 in Snow Leopard. In the dir /System/Library/Frameworks/JavaVM.framework/Versions there are entries for "1.5" and "1.5.0" but they are symlinks to "CurrentJDK", which itself is a symlink to "1.6". In order to restore Java 1.5.22 I followed the suggestions of this blog page:
http://codethought.com/blog/?p=233
In a nutshell (in case the page goes away) I used Pacifist (http://www.charlessoft.com/) to open the Java for Mac OS X 10.5 Update 6 (http://support.apple.com/downloads/Java_for_Mac_OS_X_10_5_Update_6) package, and selected only the 1.5 and 1.5.0 elements for install, rather than running the whole update. Before doing this I had to delete the empty symlinks "1.5" and "1.5.0".
It's possible - the blog author notes that this happened to him - when installing the latest Java update for OS X 10.{?} it will change the frameworks dir and rename the "1.5.0" folder to "1.5.0 1", installing the symlink to "CurrentJDK" in "1.5.0"'s place. If this happens, just jettison the new "1.5.0" and rename "1.5.0 1" back to "1.5.0".
On the command line:
cd /System/Library/Frameworks/JavaVM.framework/Versionssudo rm 1.5sudo rm 1.5.0Open the file JavaForMacOSX10.5Update6.dmg with Pacifist.
Navigate inside the Pacifist display to /System/Library/Frameworks/JavaVM.framework/Versions.
Select 1.5.0 and Install to Default Location.
On the command line:
sudo ln -s 1.5.0 1.5
Some links:
UTA: I found my notes. In case these provide any additional context.
Restoring Java 1.5.22 to the machine
For some reason Apple saw fit to remove all versions of Java other than 1.6 in Snow Leopard. In the dir /System/Library/Frameworks/JavaVM.framework/Versions there are entries for "1.5" and "1.5.0" but they are symlinks to "CurrentJDK", which itself is a symlink to "1.6". In order to restore Java 1.5.22 I followed the suggestions of this blog page:
http://codethought.com/blog/?p=233
In a nutshell (in case the page goes away) I used Pacifist (http://www.charlessoft.com/) to open the Java for Mac OS X 10.5 Update 6 (http://support.apple.com/downloads/Java_for_Mac_OS_X_10_5_Update_6) package, and selected only the 1.5 and 1.5.0 elements for install, rather than running the whole update. Before doing this I had to delete the empty symlinks "1.5" and "1.5.0".
It's possible - the blog author notes that this happened to him - when installing the latest Java update for OS X 10.{?} it will change the frameworks dir and rename the "1.5.0" folder to "1.5.0 1", installing the symlink to "CurrentJDK" in "1.5.0"'s place. If this happens, just jettison the new "1.5.0" and rename "1.5.0 1" back to "1.5.0".
Subscribe to:
Posts (Atom)