Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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:

<< DatabaseLink`DatabaseExamples`;
DatabaseExamplesBuild[ ]
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.