2
$\begingroup$

I want to create a reusable line of code that I can send a column, table name and criteria to match using the WHERE clause.

colCount[x_, y_, z_] :=  Length[SQLExecute[conn, 
"SELECT " && x && " FROM " && y && " WHERE " && x && " = " && y &&
  ""]];

When I try to pass a column name to the SELECT statement using the variable x and pass the table name to y, using

colCount[col1,db1,40]

MM doesn't seem to understand. I tried the column and database name in quotes too.

Cheers!

$\endgroup$
  • $\begingroup$ AHA! Had taken my quotes of the strings I was passing previously. I put them back in and it works, I think. colCount["col1", "Table1", 40] At least I get a number returned that looks more expected. Could you explain why my code didn't work? $\endgroup$ – Sinistar Apr 23 '14 at 23:29
  • $\begingroup$ @kguler I corrected the SetDelayed part of the code, adding the string typing for x and y_ and it still didn't like it. Then I tried using the double ~ instead of & and it didn't like that either. I think it has something to do with how you concatenated it in the quotes part of SQLExecute is my guess. $\endgroup$ – Sinistar Apr 23 '14 at 23:54
2
$\begingroup$

The following works as expected:

Needs["DatabaseLink`"]
conn = OpenSQLConnection["publisher"]
(* SQLConnection["publisher", 10, "Open", 
     "TransactionIsolationLevel" -> "ReadCommitted"]   *)

First, an example from the docs:

SQLExecute[conn, "SELECT * FROM ROYSCHED WHERE ROYALTY >= .11 AND ROYALTY <= .12"]
(* {{"BS1011",5001,50000,0.12`},{"CP5018",2001,4000,0.12`},
      {"BS1001",1001,5000,0.12`},{"PY2002",1001,5000,0.12`},
      {"PY2003",2001,5000,0.12`},{"UK3004",1001,2000,0.12`},
      {"CK4005",2001,6000,0.12`},{"CP5010",5001,50000,0.12`},
      {"PY2012",5001,50000,0.12`},{"PY2013",5001,50000,0.12`},
      {"UK3006",1001,2000,0.12`},{"BS1014",4001,8000,0.12`},
      {"UK3015",2001,4000,0.12`},{"CK4016",5001,15000,0.12`},
      {"CK4017",2001,8000,0.12`},{"BS1007",5001,50000,0.12`}}  *)

Now, using the function colCount:

colCount[x_, y_, z_] := 
    Length@SQLExecute[conn, "SELECT `1` FROM `2` WHERE `1` = `3`",
          {SQLColumn[x], SQLTable[y],  z}];
colCount["ROYALTY", "ROYSCHED", .12]
(* 16 *)

Alternatively, you can use StringJoin (<>) to concatenate various pieces of the second argument of SQLExecute:

colCount2[x_, y_, z_] := 
   Length@SQLExecute[conn,  "SELECT " <> x <> " FROM " <> y <>
     " WHERE " <> x <> " = " <>    ToString@z];
colCount2["ROYALTY", "ROYSCHED", .12]
(* 16 *)

Finally, you can use StringExpression (~~) instead of <>:

colCount3[x_, y_, z_] := 
    Length@SQLExecute[conn, 
     "SELECT " ~~ x ~~ " FROM " ~~ y ~~ " WHERE " ~~ x ~~ " = " ~~ ToString@z];
colCount3["ROYALTY", "ROYSCHED", .12]
 (* 16 *)
|improve this answer|||||
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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