| Author |
Message
|
| Dave Ziegler |
 Posted: Thu Oct 30, 2014 12:02 pm Post subject: Pass TIME parameter to SQL Server 2008 |
 |
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
What is the proper way to pass a TIME parameter to SQL Server 2008 from ESQL? I've tried the following:
| Code: |
ALTER PROCEDURE [dbo].[usp_MyProc]
(
@windowStart TIME = '9:00:00 AM', -- 8AM CT
@windowEnd TIME = '6:00:00 PM' -- 5PM CT
)
AS...
CREATE PROCEDURE Fetch(IN windowStart TIME, IN windowEnd TIME)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.MyProc";
...
DECLARE windowStart TIME CAST('9:00 AM' AS TIME FORMAT 'h:mm a');
DECLARE windowEnd TIME CAST('6:00 PM' AS TIME FORMAT 'h:mm a');
CALL Fetch(results.Rows[], windowStart, windowEnd);
|
And:
| Code: |
ALTER PROCEDURE [dbo].[usp_MyProc]
(
@windowStart VARCHAR(11) = '9:00:00 AM', -- 8AM CT
@windowEnd VARCHAR(11) = '6:00:00 PM' -- 5PM CT
)
AS...
CREATE PROCEDURE Fetch(IN windowStart CHARACTER, IN windowEnd CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.MyProc";
...
DECLARE windowStart CHARACTER '09:00:00 AM';
DECLARE windowEnd CHARACTER '18:00:00 PM';
CALL Fetch(results.Rows[], windowStart, windowEnd);
|
Without the two time parameters, this executes just fine using defaults in the sproc.
(The error messages in the debug window are not very helpful IMO.) |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Thu Oct 30, 2014 12:48 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Thu Oct 30, 2014 12:52 pm Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| I see VARCHAR in that table. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Oct 30, 2014 12:56 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Yes, VARCHAR is there and it is mapped from CHARACTER.
So the second form you mention should work.
You say the error in the debug view doesn't make much sense.
Which is fine, because it's really the errors thrown by the runtime that you need to look at. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Thu Oct 30, 2014 12:57 pm Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
So to my question... is there a preferred way of doing this that I am not aware of? I'd rather not pass TIME values as strings, but if I must, I will.
I disagree that showing errors that don't make much sense is "fine", however. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Thu Oct 30, 2014 1:00 pm Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| Maybe I can get away with TIMESTAMP... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Oct 30, 2014 1:01 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
What I really meant was that the errors in the debug window aren't the ones that matter. The ones that matter are the ones thrown by the ODBC componentry under the flow, which are reported through the syslog.
But even in the debug view, if you have a break point at somewhere that will show the exception list, then you should still see most of the information you'd need.
According to that table, you can send in an ESQL TIMESTAMP which will become an SQLServer DATETIME. That timestamp can only contain an hour/minute value.. or you can extract only the hour/minute inside the procedure.
But changing to that won't help if the issues is something with the ODBC config.
Again, either look at the syslog (Event Viewer if you're running on Windows) or the ExceptionList at the Catch node of the flow. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Thu Oct 30, 2014 2:07 pm Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
D'oh!
| Code: |
CALL Fetch(results.Rows[], windowStart, windowEnd);
|
Should be:
| Code: |
CALL Fetch(windowStart, windowEnd, results.Rows[]);
|
My tiny brain chose to ignore this the first 200 times I read it:
| Code: |
Insert
Type:INTEGER:5
Text:CHARACTER:RESULT_SET_PARAM_0
|
PARAM ZERO YOU BLOCKHEAD. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Oct 31, 2014 4:51 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Dave Ziegler wrote: |
D'oh!
| Code: |
CALL Fetch(results.Rows[], windowStart, windowEnd);
|
Should be:
| Code: |
CALL Fetch(windowStart, windowEnd, results.Rows[]);
|
|
I saw that, but I've not directly worked with dynamic result sets to be sure it wasn't the correct way to do things... But it did look suspicious. I should maybe have said something. |
|
| Back to top |
|
|
|
|
