# Using SetDelayed wih SQLExecute

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

The following works as expected:

Needs["DatabaseLink"]
conn = OpenSQLConnection["publisher"]
(* SQLConnection["publisher", 10, "Open",


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