I'm trying to use SQLUnit with MS SQL Server 2000. My problem is converting from an acceptable java.sql.Type to DATETIME.
When supplying a value as a parameter to a stored procedure I have tried all types of formats (e.g., 7/1/2001, #7/1/2001#, Jan-07-01, etc.) and various types (e.g., DATE, VARCHAR, TIMESTAMP) and can't seem to elicit the proper combination.
VARCHAR appears to come closest, but I get the error message "Error converting data type nvarchar to datetime." What am I missing?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The GUI and TUI tools both work off the same backend code, so it is unlikely that one would support TIMESTAMP and the other would not, but I will look and confirm.
The closest to a DATETIME would probably be TIMESTAMP. SQLUnit stores it internally as yyyy-MM-dd HH:mm:ss.SSS (century to millisecond) and uses SimpleDateFormat to do the conversion. With the new type system that Ralph and I are working on, you should be able to change the format to what you want, simply by overriding the type to something that you can provide.
For example, you could override the Timestamp type to your MyTimestampType which would just override the pattern to MMM-dd-yy.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thank you, this helps, the test case you specified is fairly easy to reproduce, and I can try it against the new code in CVS and see if it still fails. I will let you know what I find.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I tested this against the new GUI tool code and got back the same result for a postgresql stored procedure. It was a bug introduced by the new types system. Since its now properties file based, it had to be loaded, which was not being done. I will update CVS with the changes shortly (perhaps in the next 10-15 mins), so it should work.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm trying to use SQLUnit with MS SQL Server 2000. My problem is converting from an acceptable java.sql.Type to DATETIME.
When supplying a value as a parameter to a stored procedure I have tried all types of formats (e.g., 7/1/2001, #7/1/2001#, Jan-07-01, etc.) and various types (e.g., DATE, VARCHAR, TIMESTAMP) and can't seem to elicit the proper combination.
VARCHAR appears to come closest, but I get the error message "Error converting data type nvarchar to datetime." What am I missing?
It appears that I was on the right track. The problem was the GUI tool, not me.
I switched to use the TUI tool, and that appears to accept both VARCHAR and TIMESTAMP.
Please let me know if this information is in error.
Hi James,
The GUI and TUI tools both work off the same backend code, so it is unlikely that one would support TIMESTAMP and the other would not, but I will look and confirm.
The closest to a DATETIME would probably be TIMESTAMP. SQLUnit stores it internally as yyyy-MM-dd HH:mm:ss.SSS (century to millisecond) and uses SimpleDateFormat to do the conversion. With the new type system that Ralph and I are working on, you should be able to change the format to what you want, simply by overriding the type to something that you can provide.
For example, you could override the Timestamp type to your MyTimestampType which would just override the pattern to MMM-dd-yy.
-sujit
Hi Sujit-
I performed additional investigation into the error, since you said that both tools use that same backend code.
My stored proc is merely echos back the DATETIME it recived as a parameter and also returns an integer value.
I invoke it within SQLunit as {? = call testproc(?)}.
I have included stack traces for using VARCHAR and TIMESTAMP as input parameter types.
Here is a stack trace that I get when using the gui tool attempting to use a VARCHAR type for the input parameter:
Buildfile: build.xml
init:
compile:
gui:
[java] SQLUnit GUI Tool
[java] Copyright(c) 2003 The SQLUnit Team
[java] DEBUG [AWT-EventQueue-0] (ConnectionRegistry.java:93) - >> setConnectionById(null)
[java] DEBUG [AWT-EventQueue-0] (ConnectionFactory.java:56) - >> getInstance(props)
[java] DEBUG [AWT-EventQueue-0] (ConnectionRegistry.java:54) - >> getConnectionById(null)
[java] DEBUG [AWT-EventQueue-0] (HandlerFactory.java:49) - >> getInstance(call)
[java] DEBUG [AWT-EventQueue-0] (CallHandler.java:60) - >> process()
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(null)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(null)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue({?=call testproc(?)})
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName({?=call testproc(?)})
[java] DEBUG [AWT-EventQueue-0] (HandlerFactory.java:49) - >> getInstance(param)
[java] DEBUG [AWT-EventQueue-0] (ParamHandler.java:51) - >> process(elParam)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(1)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(1)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(integer)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(integer)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(false)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(false)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(${retval})
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(${retval})
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:60) - >> getObject(${retval})
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(${retval})
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(out)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(out)
[java] DEBUG [AWT-EventQueue-0] (HandlerFactory.java:49) - >> getInstance(param)
[java] DEBUG [AWT-EventQueue-0] (ParamHandler.java:51) - >> process(elParam)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(2)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(2)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(varchar)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(varchar)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(false)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(false)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(7/1/2004)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(7/1/2004)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(in)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(in)
[java] DEBUG [AWT-EventQueue-0] (ConnectionRegistry.java:54) - >> getConnectionById(null)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(null)
[java] DEBUG [AWT-EventQueue-0] (SqlTypeUtils.java:110) - >> getSqlTypeFromXmlType(integer)
[java] DEBUG [AWT-EventQueue-0] (SqlTypeUtils.java:404) - >> createFieldMap()
[java] DEBUG [AWT-EventQueue-0] (ConnectionRegistry.java:108) - >> invalidate(null)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:74) - >> setObject(${__FailureMessage__},net.sourceforge.sqlunit.SQLUnitException
[java] Exception caught in generate: null
[java] java.lang.NullPointerException
[java] at net.sourceforge.guisqlunittool.SQLUnitGUITool.btnGenerateActionPerformed(SQLUnitGUITool.java:461)
[java] at net.sourceforge.guisqlunittool.SQLUnitGUITool.access$300(SQLUnitGUITool.java:66)
[java] at net.sourceforge.guisqlunittool.SQLUnitGUITool$5.actionPerformed(SQLUnitGUITool.java:342)
[java] at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786)
[java] at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839)
[java] at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
[java] at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
[java] at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245)
[java] at java.awt.Component.processMouseEvent(Component.java:5100)
[java] at java.awt.Component.processEvent(Component.java:4897)
[java] at java.awt.Container.processEvent(Container.java:1569)
[java] at java.awt.Component.dispatchEventImpl(Component.java:3615)
[java] at java.awt.Container.dispatchEventImpl(Container.java:1627)
[java] at java.awt.Component.dispatchEvent(Component.java:3477)
[java] at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:3483)
[java] at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198)
[java] at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128)
[java] at java.awt.Container.dispatchEventImpl(Container.java:1613)
[java] at java.awt.Window.dispatchEventImpl(Window.java:1606)
[java] at java.awt.Component.dispatchEvent(Component.java:3477)
[java] at java.awt.EventQueue.dispatchEvent(EventQueue.java:456)
[java] at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
[java] at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
[java] at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145)
[java] at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
[java] at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
BUILD SUCCESSFUL
Total time: 1 minute 56 seconds
Here is a stack trace that I get when using the gui tool attempting to use a TIMESTAMP type for the input parameter:
Buildfile: build.xml
init:
compile:
gui:
[java] SQLUnit GUI Tool
[java] Copyright(c) 2003 The SQLUnit Team
[java] DEBUG [AWT-EventQueue-0] (ConnectionRegistry.java:93) - >> setConnectionById(null)
[java] DEBUG [AWT-EventQueue-0] (ConnectionFactory.java:56) - >> getInstance(props)
[java] DEBUG [AWT-EventQueue-0] (ConnectionRegistry.java:54) - >> getConnectionById(null)
[java] DEBUG [AWT-EventQueue-0] (HandlerFactory.java:49) - >> getInstance(call)
[java] DEBUG [AWT-EventQueue-0] (CallHandler.java:60) - >> process()
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(null)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(null)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue({? = call testproc(?)})
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName({? = call testproc(?)})
[java] DEBUG [AWT-EventQueue-0] (HandlerFactory.java:49) - >> getInstance(param)
[java] DEBUG [AWT-EventQueue-0] (ParamHandler.java:51) - >> process(elParam)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(1)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(1)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(integer)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(integer)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(false)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(false)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(${retval})
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(${retval})
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:60) - >> getObject(${retval})
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(${retval})
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(out)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(out)
[java] DEBUG [AWT-EventQueue-0] (HandlerFactory.java:49) - >> getInstance(param)
[java] DEBUG [AWT-EventQueue-0] (ParamHandler.java:51) - >> process(elParam)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(2)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(2)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(timestamp)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(timestamp)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(false)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(false)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(2004-07-01 12:34:56.789)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(2004-07-01 12:34:56.789)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:86) - >> getValue(in)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(in)
[java] DEBUG [AWT-EventQueue-0] (ConnectionRegistry.java:54) - >> getConnectionById(null)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:197) - >> isVariableName(null)
[java] DEBUG [AWT-EventQueue-0] (SqlTypeUtils.java:110) - >> getSqlTypeFromXmlType(integer)
[java] DEBUG [AWT-EventQueue-0] (SqlTypeUtils.java:404) - >> createFieldMap()
[java] DEBUG [AWT-EventQueue-0] (ConnectionRegistry.java:108) - >> invalidate(null)
[java] DEBUG [AWT-EventQueue-0] (SymbolTable.java:74) - >> setObject(${__FailureMessage__},net.sourceforge.sqlunit.SQLUnitException
[java] Exception caught in generate: null
[java] java.lang.NullPointerException
[java] at net.sourceforge.guisqlunittool.SQLUnitGUITool.btnGenerateActionPerformed(SQLUnitGUITool.java:461)
[java] at net.sourceforge.guisqlunittool.SQLUnitGUITool.access$300(SQLUnitGUITool.java:66)
[java] at net.sourceforge.guisqlunittool.SQLUnitGUITool$5.actionPerformed(SQLUnitGUITool.java:342)
[java] at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786)
[java] at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839)
[java] at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
[java] at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
[java] at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245)
[java] at java.awt.Component.processMouseEvent(Component.java:5100)
[java] at java.awt.Component.processEvent(Component.java:4897)
[java] at java.awt.Container.processEvent(Container.java:1569)
[java] at java.awt.Component.dispatchEventImpl(Component.java:3615)
[java] at java.awt.Container.dispatchEventImpl(Container.java:1627)
[java] at java.awt.Component.dispatchEvent(Component.java:3477)
[java] at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:3483)
[java] at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198)
[java] at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128)
[java] at java.awt.Container.dispatchEventImpl(Container.java:1613)
[java] at java.awt.Window.dispatchEventImpl(Window.java:1606)
[java] at java.awt.Component.dispatchEvent(Component.java:3477)
[java] at java.awt.EventQueue.dispatchEvent(EventQueue.java:456)
[java] at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
[java] at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
[java] at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145)
[java] at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
[java] at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
BUILD SUCCESSFUL
Total time: 1 minute 4 seconds
Hope this helps.
Thank you, this helps, the test case you specified is fairly easy to reproduce, and I can try it against the new code in CVS and see if it still fails. I will let you know what I find.
-sujit
I tested this against the new GUI tool code and got back the same result for a postgresql stored procedure. It was a bug introduced by the new types system. Since its now properties file based, it had to be loaded, which was not being done. I will update CVS with the changes shortly (perhaps in the next 10-15 mins), so it should work.
-sujit