"OnGroupChange" event?

I find that I regularly need to evaluate all the rows in "data groups" within the pipeline and execute script based transform logic for each grouping (aggregate transform does not provide script access to the data).

I do this in script by sorting the pipelined data on some key columns to define the groupings, caching the key values as the rows pass through the ProcessInputRow procedure, and running logic that compares the current row's key values as each row passes through the transform with the previous row's key values. When they differ I know I have a new group (special casing for the very first row in the pipleine), so I perform my transforms with cached data from the previous group .

This works but I am thinking this must be a common "pattern". Also, I have experience with a competing ETL tool which has "built in" support for script processing of user defined data groupings. That product provides an "OnChange" event, where the user defines the key in the GUI (can be composite), and the event provides a container to hold script (with access to the row data values) to run when the event "fires"(any values change in the key vs. the previous). Of course the data stream again has to be sorted for this to work.

Anyone else regularly have a need to do this type of processing? If so perhaps it is a good candidate for a custom component? Or a suggestion to Microsoft to enhance the aggregate component for the next SSIS version to provide a script "hook" in the aggregate transform? Anyone else regularly have this type of processing requirement?

Ken

Yeah, this would be a useful feature, but it looks like someone will have to write a custom component!

"Incorrect syntax" exception when prefacing SP names with "dbo." and named p

We're currently trying to evaluate SQLJDBC 2005 1.1 June CTP's support for database mirroring automatic failover. Unfortunately we're getting unexpected exceptions for calls that work fine w/ jtds that our blocking our ability to perform these evaluations without us making substantial changes to our codebase.

The first issue is with the name used when calling a stored procedure -- SP names that start with "dbo." give us the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'. src:{call dbo.xyz(?,?,?,?,?,?,?,?)}

The call will work if we change the SQL statement to {call xyz(...)}. I don't understand why we would need to do this, especially given that the documentation for the driver shows call statements with the "dbo." prefix.

We're also having problems using named parameters with stored procedures (for both in and out parametes). Our code has parameter names of the form "@.param" as is standard with TSQL (and is required when using jtds). However, this won't work with SQLJDBC -- it only seems to accept parameter names w/o the leading "@.". Why is this so?

Finally, we were able to cause a NullPointerException within the driver due to an incorrectly built Properties object that contained an Integer for loginTimeout instead of a String:

java.lang.NullPointerException

at java.util.Hashtable.put(Hashtable.java:396)

at java.util.Properties.setProperty(Properties.java:128)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.fixupProperties(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.mergeURLAndSuppliedProperties(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

While this was due to a bug in our code I would think that such common errors would be better handled.

Hi,

I am not able to reproduce the error that you are seeing with executing a stored procedure with the dbo prefix. What you have provided there looks like it should work fine. Could you provide a code sample that demonstrates the error?

Thank you,

--David Olix

JDBC Development

|||

We're using the Spring JDBC template helper classes in this instance :

JdbcTemplate t = getJdbcTemplate();

Object result = t.execute("{call dbo.SP(?, ?, ?, ?, ?, ?, ?)}",
new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.registerOutParameter( "@.pParam1", Types.INTEGER );
cs.registerOutParameter( "@.pParam2", Types.DATE );
cs.registerOutParameter( "@.pParam3", Types.DATE );
cs.registerOutParameter( "@.pParam4", Types.DATE );
cs.registerOutParameter( "@.pParam5", Types.INTEGER );
cs.setInt("@.pParam6", i)
cs.setString( "@.pParam7", s1);
cs.setString( "@.pParam8", s2);
cs.execute();

return result; // object created based on out params

}

The template is basically doing a "connection.prepareCall()" with the passed in SQL string, and then calling doInCallableStatement from within a try/catch block that translates

exceptions.

Were you able to reproduce the issue with "@." not being accepted for named params? That problem is effectively impossible for us to workaround, as we would need

to alter large sections of code when switching between jtds and sqljdbc.

|||

Ok, I was able to track down the cause of the Incorrect syntax exception by running a DB trace. The core problem appears to be the use of named parameters -- the call to registerOutParameter resulted in the following being executed on the server:

exec sp_sproc_columns dbo.SP, @.ODBCVer=3

This statement is incorrect -- it needs to be exec sp_sproc_columns [dbo.SP], @.ODBCVer=3

|||

Yes, that's it. If you'd like to submit a bug so that you can track progress on the fix, you may do so through the MSDN Product Feedback center at http://msdn.microsoft.com/feedback . You may have to jump through one small hoop (search for a resolution to your problem) before being taken to the "submit feedback" button that goes to the bug submission form.

I am still researching the "@." issue and will get back to you when I have an answer there.

Thank you,

--David Olix

JDBC Development

"Incorrect syntax" exception when prefacing SP names with "dbo." and nam

We're currently trying to evaluate SQLJDBC 2005 1.1 June CTP's support for database mirroring automatic failover. Unfortunately we're getting unexpected exceptions for calls that work fine w/ jtds that our blocking our ability to perform these evaluations without us making substantial changes to our codebase.

The first issue is with the name used when calling a stored procedure -- SP names that start with "dbo." give us the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'. src:{call dbo.xyz(?,?,?,?,?,?,?,?)}

The call will work if we change the SQL statement to {call xyz(...)}. I don't understand why we would need to do this, especially given that the documentation for the driver shows call statements with the "dbo." prefix.

We're also having problems using named parameters with stored procedures (for both in and out parametes). Our code has parameter names of the form "@.param" as is standard with TSQL (and is required when using jtds). However, this won't work with SQLJDBC -- it only seems to accept parameter names w/o the leading "@.". Why is this so?

Finally, we were able to cause a NullPointerException within the driver due to an incorrectly built Properties object that contained an Integer for loginTimeout instead of a String:

java.lang.NullPointerException

at java.util.Hashtable.put(Hashtable.java:396)

at java.util.Properties.setProperty(Properties.java:128)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.fixupProperties(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.mergeURLAndSuppliedProperties(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

While this was due to a bug in our code I would think that such common errors would be better handled.

Hi,

I am not able to reproduce the error that you are seeing with executing a stored procedure with the dbo prefix. What you have provided there looks like it should work fine. Could you provide a code sample that demonstrates the error?

Thank you,

--David Olix

JDBC Development

|||

We're using the Spring JDBC template helper classes in this instance :

JdbcTemplate t = getJdbcTemplate();

Object result = t.execute("{call dbo.SP(?, ?, ?, ?, ?, ?, ?)}",
new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.registerOutParameter( "@.pParam1", Types.INTEGER );
cs.registerOutParameter( "@.pParam2", Types.DATE );
cs.registerOutParameter( "@.pParam3", Types.DATE );
cs.registerOutParameter( "@.pParam4", Types.DATE );
cs.registerOutParameter( "@.pParam5", Types.INTEGER );
cs.setInt("@.pParam6", i)
cs.setString( "@.pParam7", s1);
cs.setString( "@.pParam8", s2);
cs.execute();

return result; // object created based on out params

}

The template is basically doing a "connection.prepareCall()" with the passed in SQL string, and then calling doInCallableStatement from within a try/catch block that translates

exceptions.

Were you able to reproduce the issue with "@." not being accepted for named params? That problem is effectively impossible for us to workaround, as we would need

to alter large sections of code when switching between jtds and sqljdbc.

|||

Ok, I was able to track down the cause of the Incorrect syntax exception by running a DB trace. The core problem appears to be the use of named parameters -- the call to registerOutParameter resulted in the following being executed on the server:

exec sp_sproc_columns dbo.SP, @.ODBCVer=3

This statement is incorrect -- it needs to be exec sp_sproc_columns [dbo.SP], @.ODBCVer=3

|||

Yes, that's it. If you'd like to submit a bug so that you can track progress on the fix, you may do so through the MSDN Product Feedback center at http://msdn.microsoft.com/feedback . You may have to jump through one small hoop (search for a resolution to your problem) before being taken to the "submit feedback" button that goes to the bug submission form.

I am still researching the "@." issue and will get back to you when I have an answer there.

Thank you,

--David Olix

JDBC Development