Prev: Intersection of sublists on date and making a 2D list from a 3D one
Next: Usage Messages; % evaluates to Null. Why?
From: Jagra on 22 May 2010 00:42
A bit simplified but I hope this explains my problem...

This works:

SQLExecute[conn, "=E2=80=A8SELECT column1, =E2=80=A8FROM table1=E2=80=A8 WHERE dateColumn >=
'2007-02-15'"=E2=80=A8];

But I'd like to make the date ('2007-02-15') a parameter. I've tried
this:

SQLExecute[conn, "=E2=80=A8SELECT column1, FROM table1=E2=80=A8 WHERE dateColumn >=
'`1`'",
"2007-02-15" ];

and a whole variety of other combinations like:

SQLExecute[conn, "=E2=80=A8SELECT column1, FROM table1=E2=80=A8 WHERE dateColumn >=
'`1`'",
{"2007-02-15"} ];

and this:

SQLExecute[conn, "=E2=80=A8SELECT =E2=80=A8column1, FROM table1=E2=80=A8 WHERE dateColumn >=
'`1`'",
SQLArgument[{"2007-02-15"}] ];

and this:

SQLExecute[conn, "SELECT column1, FROM table1=E2=80=A8 WHERE dateColumn >=
'`1`'",
DateList["2007-02-15"] ];

BUT I can't seem to figure it out. Any suggestions or guidelines
beyond the documentation?
Perplexed but hopeful.
Thanks a lot.

From: Jagra on 23 May 2010 03:16
I don't know what happened in my earlier post for it to have picked up
a bunch of extraneous characters that confuse the issue. Unable to
edit an earlier post, I've reentered it below, hoping it will come
through without the extra characters....

A bit simplified but I hope this explains my problem...

This works:

SQLExecute[conn,"SELECT column1, FROM table1 WHERE dateColumn
>='2007-02-15'"];

But I'd like to make the date ('2007-02-15') a parameter. I've tried
this:

SQLExecute[conn,"SELECT column1, FROM table1 WHERE dateColumn
>='`1`'","2007-02-15" ];

and a whole variety of other combinations like:

SQLExecute[conn,"SELECT column1, FROM table1 WHERE dateColumn >='`1`'",
{"2007-02-15"} ];

and this:

SQLExecute[conn,"SELECT column1, FROM table1 WHERE dateColumn
>='`1`'", SQLArgument[{"2007-02-15"}] ];

and this:

SQLExecute[conn,"SELECT column1, FROM table1WHERE dateColumn >='`1`'",
DateList["2007-02-15"] ];

BUT I can't seem to figure it out. Any suggestions or guidelines
beyond the documentation?
Perplexed but hopeful.
Thanks a lot.

From: Hans Michel on 23 May 2010 03:17
DatabaseLink/ref/SQLDateTime

Use SQLDateTime to insert into a Date, DateTime, or TimeStamp field.

Hans

"Jagra" <jagra24891(a)mypacks.net> wrote in message
news:ht7nbr$pss$1(a)smc.vnet.net...
>A bit simplified but I hope this explains my problem...
>
> This works:
>
> SQLExecute[conn, "=E2=80=A8SELECT column1, =E2=80=A8FROM table1=E2=80=A8
> WHERE dateColumn >=
> '2007-02-15'"=E2=80=A8];
>
> But I'd like to make the date ('2007-02-15') a parameter. I've tried
> this:
>
> SQLExecute[conn, "=E2=80=A8SELECT column1, FROM table1=E2=80=A8 WHERE
> dateColumn >=
> '`1`'",
> "2007-02-15" ];
>
> and a whole variety of other combinations like:
>
> SQLExecute[conn, "=E2=80=A8SELECT column1, FROM table1=E2=80=A8 WHERE
> dateColumn >=
> '`1`'",
> {"2007-02-15"} ];
>
> and this:
>
> SQLExecute[conn, "=E2=80=A8SELECT =E2=80=A8column1, FROM table1=E2=80=A8
> WHERE dateColumn >=
> '`1`'",
> SQLArgument[{"2007-02-15"}] ];
>
> and this:
>
> SQLExecute[conn, "SELECT column1, FROM table1=E2=80=A8 WHERE dateColumn >=
> '`1`'",
> DateList["2007-02-15"] ];
>
> BUT I can't seem to figure it out. Any suggestions or guidelines
> beyond the documentation?
> Perplexed but hopeful.
> Thanks a lot.
>


From: dr DanW on 25 May 2010 06:32
Below is some code I use to retrieve data from our CRM system, stored
on a SQL database. It shows how to use StringForm[] type arguments
(like fprint() ) and format dates for SQL calls.

---- code ----
SQLEscapeSingleQuote[v_] := v;
SQLEscapeSingleQuote[v_String] := StringReplace[v, "'" -> "''"];

SQLForm[s_String, vars___] :=
ToString[StringForm[StringReplace[s, Whitespace -> " "],
Sequence @@ SQLEscapeSingleQuote /@ {vars}]]

SQLDateString[date_] := DateString[DateList[date],
{"Year", "-", "Month", "-", "Day"}]

CRMExecute[s_String, vars___] := SQLExecute[crm, SQLForm[s, vars],
"ShowColumnHeadings" -> True]

(* Example of a function call *)

CRMProjectTimeHistory[projectname_, start_, end_] :=
CRMExecute["
SELECT
FilteredKe_time.ke_timedate AS date,
FilteredKe_time.ke_hours AS hours
FROM
FilteredKe_time
WHERE
FilteredKe_time.ke_projectidname = '`1`'
AND
FilteredKe_time.ke_timedate BETWEEN '`2`' AND '`3`'
ORDER BY
FilteredKe_time.ke_timedate,
FilteredKe_time.ke_itemidname",
projectname,
SQLDateString[start],
SQLDateString[end]
]

--- end code ---

Enjoy.
Daniel

 | 
Pages: 1
Prev: Intersection of sublists on date and making a 2D list from a 3D one
Next: Usage Messages; % evaluates to Null. Why?