Hi,
I have troubles with a script: I need to call a stored procedure with a date parameter, but it doesn’t work. I always get the message “Error executing system.db.execSProcCall()” with “The conversion from UNKNOWN to DATE is unsupported”.
Note that I tried directly with the date format of ignition like system.date.now() and with a formated string value yyyY-MM-dd HH:mm:ss.SSS (the table has a datetime2 column).
When I run it in SQL Server Managment with the same values, it works fine.
From what I saw, I could run it with an EXEC in a runQuery, but it seems strange to me that registerInParam doesn’t work with a system.db.DATE.
Thanks.
Edit: didn’t manage to make it works with runQuery or runPrepQuery
Edit2: a coworker just find out it has to be system.db.VARCHAR with the formatted date to work.
Try with system.db.TIMESTAMP
instead of system.db.DATE
.
In MSSQL:
CREATE TABLE [dbo].[dates](
[d] [date] NULL,
[dt] [datetime] NULL,
[dt2] [datetime2](7) NULL
);
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[dates_insert]
@d DATE,
@dt DATETIME,
@dt2 DATETIME2
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO [dbo].[dates] ([d], [dt], [dt2]) VALUES (@d, @dt, @dt2);
END
GO
The code that fails:
from java.lang import Exception as JException
now = system.date.now()
try:
call = system.db.createSProcCall('dbo.dates_insert')
call.registerInParam('d', system.db.DATE, now)
call.registerInParam('dt', system.db.DATE, now)
call.registerInParam('dt2', system.db.DATE, now)
system.db.execSProcCall(call)
except JException, e:
print e.cause
# Output:
The conversion from UNKNOWN to DATE is unsupported.
The code that works:
from java.lang import Exception as JException
now = system.date.now()
try:
call = system.db.createSProcCall('dbo.dates_insert')
call.registerInParam('d', system.db.TIMESTAMP, now)
call.registerInParam('dt', system.db.TIMESTAMP, now)
call.registerInParam('dt2', system.db.TIMESTAMP, now)
system.db.execSProcCall(call)
except JException, e:
print e.cause
Result:
d dt dt2
---------- ----------------------- ---------------------------
2018-05-18 2018-05-18 18:09:50.293 2018-05-18 18:09:50.2933333
1 Like
That works as well thanks !
Is there a way to know all the other possibilities for system.db ? I don’t see them on the manual or with ctrl + space, and this may be usefull.
In a script console, try:
print dir(system.db)
The available datatype constants are listed on the createSProcCall manual page.