Friday, July 10, 2009

Quick SQL --> Excel export w/ column names

I know there's lot of ways to do this, including an SSIS/DTS package, but here's another quick way I pieced together for ad-hoc exports with that I haven't seen outlined before:

1. Run the query in SQL Server Management Studio (w/ "Results to Grid")
2. Copy all data from the grid (right-click on the unlabled box at the top left of the grid, choose "copy"), and paste into second row in Excel.
3. Back in SQL, run this query:

SELECT sc.name
FROM syscolumns sc
INNER JOIN sysobjects so
ON so.id = sc.id
WHERE so.name = '[table name]'
ORDER BY sc.colid

4. Paste the results into Excel in a new sheet.
5. Copy the row, then "paste special" --> "transpose" into the first row in the original worksheet, so that they paste as columns.

This will probably break down for very large tables, but I just did this with 96 columns, and 10,000+ rows without issues.

Some more ways to accomplish this:
http://www.mssqltips.com/tip.asp?tip=1202

BTW, not sure why there's not a "Results to .csv format" in SQL Server Management Studio. "Results to Text" seems rather useless...

Friday, April 17, 2009

Forcing label values to be displayed to the right of a bar (ASP.NET 3.5 chart control)

As I've previously written, the new ASP.NET 3.5 chart control is great. However, a minor thing that doesn't work well is displaying value labels for bar charts. It displays the value to the right of the bar except for the longest bar when it determines that it wouldn't fit within the scale. So, if you have a bar with a value of 98, and you have your axis interval set to 25, it will display "98" in the bar. This doesn't necessarily look good, depending on what color the bar is, and it is also inconsistent with the other bars.

Here's my solution (C#):

Chart1.DataBound += delegate(System.Object o, System.EventArgs ea) {
double longestBarLength = Chart1.Series[0].Points.FindMaxByValue().YValues[0],
padding = 10d,
roundedUpBarLengh = Math.Ceiling((longestBarLength + padding) / 25) * 25;

Chart1.ChartAreas[0].AxisY.Maximum = roundedUpBarLengh;
};


I'm determining the longest bar length w/out looping thanks to a nice built-in function "FindMaxByValue()" which gets me access to the DataPoint object with the largest value, and I'm just grabbing the Y Value from it (in my case, there's only one Y value).

Then I specify a set padding to make room for the value label to be displayed on the right side of the bar (10 seems to work well for numbers in the 100's at least). Lastly I'm pushing the value whatever the longest bar length is to the nearest 25 and setting the axis max value to that number.

This all needs to be done after the data is already there, obviously, so this is all wrapped in the databound event handler.

I'll add pics later.

Thursday, March 26, 2009

Great .NET 3.5 Charting Control from Microsoft

I don't know how I missed this when it was released a few months ago, but this control is awesome - it generates very professional-looking charts that you used to have to pay hundreds of dollars for, and it looks just as good as the best charting tools I've seen.

The organization scheme seems a little convoluted to me, but it probably just seems that way because I was just trying to do a simple 3D bar chart, and the scheme is set up to allow a very high level of customization. I also ran into a few web.config-related snags while getting my code up and running, but nothing that I couldn't fix within a few minutes of searching the web.

Example screenshots and everything you need to get started are here.

Wednesday, February 18, 2009

Interesting SAP JCo error

Exception Details: com.sap.mw.jco.JCO$Exception
Type conflict when calling a function module (field length).

Possible Source of Error:
Class Name: com.sap.mw.jco.rfc.MiddlewareRFC$Client
File Name: MiddlewareRFC.java
Method Name: nativeExecute
Line Number: -2


Line Number -2? Those SAP guys sure are tricky with their coding before the file even starts.

BTW, SAP JCo is a major nightmare to get working. If somebody brings you a JCo project and says "this should be easy", my advice is to run away as fast as you can.

UPDATE: I solved this problem, no thanks to the error message. I was trying to pass in a field as an Export instead of an Import. Which makes perfect sense, expect the original VBScript/COM code that I am re-writing this from uses the call "[function].Exports()" for an import (and the code works!).

Thursday, December 11, 2008

Camera Focal Range Data application

I'm currently using a point & shoot camera (Canon S2 IS), but have a DSLR on the way (Canon Rebel XS). The DSLR comes with a kit lens that ranges from 18-55mm (28.8mm - 88mm in 35mm equivalents), which is quite a bit shorter than my p&s (up to 432mm equiv!). So I got worried that I wouldn't be too happy with just the kit lens. At the same time, I wanted to try out LINQ in .NET, so a quick project was born...

I wrote a bare-bones console application that reads in EXIF data from images, extracts the focal length of each, and uses some simple LINQ queries that lets me see exactly how many photos were taken in wide, normal, tele, and super tele modes. It recurses through directories from a starting directory you specify, and tries to get info on all JPEG images.



Right now, there's no nice GUI, it's not multi-threaded, you can only search one folder at a time, there is no progress bar, and you can't actually see what images fall into which category, but if there's any interest in this app at all, I'll add all that sometime soon.

Also, you have to enter a 35mm focal length equivalent for the categories to be accurate. This is annoying, because for point and shoots, that info is sometimes hard to find. I had to actually take a picture all the way zoomed in, and compare the specs on dpreview.com with the EXIF data (72mm max zoom in the EXIF = 432mm max zoom in the specs, so it has a multiplier of "6"). I may end up looking up the multipliers on a bunch of popular cameras and just ask the user to select his/her camera, or optionally manually enter the focal length multiplier if the user's camera is not on the list.

Anyway, the application is fully working in it's current state - all you need is the .NET 3.5 framework. If you want to use the app and can't figure out the proper multiplier to use, just post a comment here, and I or another reader can certainly help you out.

No crazy code in this app, but here's the LINQ part:

//generic list
private List<double> zoomData;
...
zoomData = new List<double>();
...
//code in the directory/file loop
zoomData.Add(focalLength * focalLengthX);
...
int shortTele = (from t in zoomData where t > 100 && t <= 150 select t).Count();

In my case, I was happy to see that I had a significant amount more wide and normal shots than telephoto shots, so maybe I won't need an extra lens after all.

You can download the app here.

UPDATE: Picasa somehow figures out the 35mm equiv. from the EXIF data. I'm going to figure out how they're doing it and update my application soon.

Wednesday, December 3, 2008

System.Net.Mail throws "no such user" exceptions w/ Lotus Domino!

Code that I had running for years without issues suddenly broke with the error "Mailbox unavailable. The server response was: user@domain.com... No such user". Very useful, as with this information, I can now automate (or at least partially automate) internal mailing list management.

For now, I've just added code to catch this specific exception (in addition to the catch-all SmtpException) to have it generate a work order ticket if there's a problem:

try {
smtp.Send(newsletter);
} catch(System.Net.Mail.SmtpFailedRecipientException ex) {
//generate work order...
}

There's also "SmtpFailedRecipientsException" which triggers only if it can't send an email to all the recipients, though I don't see why you would use this over SmtpFailedRecipientException, as the latter informs you in the exception message if all recipients haven't received the email in addition to handling the case where one or some recipients failed.

However, I've noticed that if more than one recipient email fails, it only displays the first email address that failed in the error message. Makes sense, since it probably is processing one email completely at a time, and if it fails on one, it doesn't go any further. The solution to this, I guess, would be to have each email sent out within a loop of separate SMTP calls instead of using the handy "mail.To.Add" method. There would most likely be a performance hit doing it this way, though.

I'm going to keep looking into it, but does anyone know of a better way?

Also, I assume this works with MS Exchange as well, but don't know anyone using email with Exchange to test with. I have verified it doesn't work with Gmail and Hotmail, so this is best used only with an environment where you know that everyone is on the same mail server.

Friday, September 19, 2008

Java SwingX ActiveDirectory/LDAP LoginService

Sorry for the long break from posting, but I haven't worked on anything interesting enough to post for a while. I've just completed some basic ActiveDirectory auth code with a Java Swing application. I had a heck of a time connecting to AD at all with Java - all the examples I found online seemed like they should have worked, but my test code just hung with no errors, and with no useful debugging info. I'm still not sure what was going on exactly, but the following helper class worked for me (the most notable exception between this code and my previous attempts is that it's now specifying the domain controller):

LDAPUtils.java:
package app;

import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.NamingEnumeration;
import javax.naming.NamingException;
import javax.naming.directory.Attributes;
import javax.naming.directory.SearchControls;
import javax.naming.directory.SearchResult;
import javax.naming.ldap.InitialLdapContext;
import javax.naming.ldap.LdapContext;

/*
@author Randy Coates
Modified slightly by Dave Gruska
*/
public class LDAPUtils {
static String ATTRIBUTE_FOR_USER = "sAMAccountName";
public Attributes authenticateUser(String username, String password, String _domain, String host, String dn) {
String returnedAtts[] ={ "sAMAccountName", "memberOf" };
String searchFilter = "(&(objectClass=user)(" + ATTRIBUTE_FOR_USER + "=" + username + "))";

//Create the search controls
SearchControls searchCtls = new SearchControls();
searchCtls.setReturningAttributes(returnedAtts);

//Specify the search scope
searchCtls.setSearchScope(SearchControls.SUBTREE_SCOPE);
Hashtable environment = new Hashtable();
environment.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");

//Using starndard Port, check your instalation
environment.put(Context.PROVIDER_URL, "ldap://" + host + ":389");
environment.put(Context.SECURITY_AUTHENTICATION, "simple");

environment.put(Context.SECURITY_PRINCIPAL, username + "@" + _domain);
environment.put(Context.SECURITY_CREDENTIALS, password);
LdapContext ctxGC = null;

try {
ctxGC = new InitialLdapContext(environment, null);

//search for objects in the GC using the filter
String searchBase = dn;
NamingEnumeration answer = ctxGC.search(searchBase, searchFilter, searchCtls);
while (answer.hasMoreElements()) {
SearchResult sr = (SearchResult)answer.next();
Attributes attrs = sr.getAttributes();
if (attrs != null)
return attrs;
}
}
catch (NamingException e) {
e.printStackTrace();
}
return null;
}
}
I'm then extending SwingX's LoginService to work with this class and return some extra details, like the user's group (this can be easily expanded to capture an Active Directory attribute):

LDAPLoginService:
package app;

import javax.naming.directory.Attributes;
import org.jdesktop.swingx.auth.LoginService;

/*
@author Dave Gruska
*/
public class LDAPLoginService extends LoginService {
private String domain;
private String host;
private String dn;
private String userName;
private String groupName;

public String getDomain() {
return domain;
}

public void setDomain(String domain) {
this.domain = domain;
}

public String getHost() {
return host;
}

public void setHost(String host) {
this.host = host;
}

public String getDn() {
return dn;
}

public void setDn(String dn) {
this.dn = dn;
}

public String getUserName() {
return userName;
}

public String getGroupName() {
return groupName;
}

public LDAPLoginService(String domain, String host, String dn) {
this.domain = domain;
this.host = host;
this.dn = dn;
}

@Override
public boolean authenticate(String name, char[] password, String server) throws Exception {
LDAPUtils LDAPlogin = new LDAPUtils();

//TODO: investigate if there's a more efficient way to convert this
StringBuilder passwd = new StringBuilder();
for(char c : password) {
passwd.append(c);
}

Attributes attrs = LDAPlogin.authenticateUser(name, passwd.toString(), this.domain, this.host, this.dn);

if(attrs == null) {
//login failed
return false;
} else {
//login successful
String[] splitUserName = attrs.get("sAMAccountName").toString().split(":");
userName = splitUserName[1].trim();
groupName = attrs.get("memberOf").contains("CN=AppAdmins,CN=Users,DC=domain,DC=com") ? "admin" : "user";

return true;
}
}
}
and finally, the presentation layer code displays a login pane (JXLoginPane dialog) that gets called in the constructor right after the components are initialized:
loginService = new LDAPLoginService("domain.com", "[domain controller IP]", "cn=Users,dc=domain,dc=com");
JXLoginPane.Status status = JXLoginPane.showLoginDialog(null, loginService);

if(!status.equals(status.SUCCEEDED)) {
System.exit(0);
}

loggedInAsLabel.setText(String.format("%S (%s)", loginService.getUserName(), loginService.getGroupName()));
BTW, the System.exit code only gets called when the user gives up on trying to log in.