# Want to use column names as indices, but without quotes

The following works:

devFactors =SQLExecute[SicsDBconnection, "SELECT * FROM " <>  devFactorTable];
devFactors[[1]]

colNames = {ALOB, Country, Months, LossFactor, EarnedFactor};
asRules = Rule @@@ Transpose[{colNames, Range[Length@colNames]}];
newList[col_] := devFactors[[All, col /. asRules]];
newList[LossFactor][[1]]


and produces:

{"Property Treaty PR", "Morristown", 12, 3.5736, 3.09256}
3.5736


I would like to pull those column names from SQL as the following:

colNames = SQLColumnNames[SicsDBconnection, SQLTable[devFactorTable]][[All, 2]];


But, when I do this, colNames now is a list of strings instead of a list of symbols and I have to reference my devFactors list like:

newList["LossFactor"][[1]]


I would like to be able to reference it using the symbol LossFactor instead of the string. I see that I can't replace the head of an atom, so I think that means that I can't turn those strings from SQL into symbols.

Obviously this isn't a critical problem, but does anyone know how I can accomplish this, or if it's possible?

-

Just add ToExpression[]:

Needs["DatabaseLink"]
conn = OpenSQLConnection["demo"];
devFactorTable = "SAMPLETABLE1"
devFactors = SQLExecute[conn, "SELECT * FROM " <> devFactorTable]
colNames = ToExpression /@ SQLColumnNames[conn, SQLTable[devFactorTable]][[All, 2]];
asRules = Rule @@@ Transpose[{colNames, Range[Length@colNames]}];
newList[col_] := devFactors[[All, col /. asRules]];
newList[NAME][[1]]
CloseSQLConnection[conn]
(*
"Day1"
*)


Edit

Prior to running this code you've to set up the demo environment for DB connectivity. If you haven't done it yet, just run:

<< DatabaseLinkDatabaseExamples;
DatabaseExamplesBuild[ ]

-
Your solution works, but I did something that I don't understand. I put all of the code (from Needs["DatabaseLink"] to CloseSQLConnection) in a module and made all of the symbols local, except for newList. So newList references a local list, devFactors. But by using ":=" in the definition of newList, doesn't it evaluate when I use it later on in the code? If so, how does it use the local symbols when I reference it outside of the module? That is, isn't devFactors gone after I leave the module? –  Mitchell Kaplan Nov 27 '13 at 14:20
@MitchellKaplan The garbage collector takes note that the local symbol is referenced from outside and it doesn't swipe it. It's a good question for the site on its own. –  belisarius Nov 27 '13 at 20:47