Using SetDelayed wih SQLExecute

Question

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!

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? — Sinistar, Apr 23 '14 at 23:29
@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. — Sinistar, Apr 23 '14 at 23:54

kglr · Accepted Answer · 2014-04-24T00:12:52.673

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 *)

Using SetDelayed wih SQLExecute

1 Answers1