TDBC

(Submitted on behalf of a colleague - also mailed to the 'tcl-tdbc' list but that mail doesn't seem to have come through?)

I am using tdbc's odbc driver to work with an MS SQL Server (2008) database.  I have run into a problem when writing code to create a stored procedure, specifically w.r.t. the syntax for a procedure's parameters.  SQL Server's "CREATE PROCEDURE" syntax (http://msdn.microsoft.com/en-us/library/ms187926%28v=sql.100%29.aspx) requires that a procedure's parameters be declared using the @ symbol preceding the name, e.g. @myvar
Just doing this results in the connection's prepare method returning a statement whose params method returns a dict that has all of the stored procedure's parameters as bound variables.  If I precede the parameter specifications with an underscore then the params dict does not contain the parameters, they are not seen as bound variables (b.t.w. this is undocumented) but when executing the statement fails due to the sql command still having the underscore.  See examples below.

Thanks
Douglas

<verbatim>
(tools) 5 %   set con_str {Driver=SQL Server Native Client 10.0;Server=127.0.0.1\SQLEXPRESS;DATABASE=TDBC;UID=builder;PWD=bob;}
Driver=SQL Server Native Client 10.0;Server=127.0.0.1\SQLEXPRESS;DATABASE=TDBC;UID=builder;PWD=bob;
(tools) 6 % tdbc::odbc::connection create my_mssql_con $con_str
::my_mssql_con
(tools) 7 % set sql_stmt {
>   CREATE PROCEDURE [ESS].[sp_commitsinsert]
>     @commitRef nchar(64),
>     @streamId int,
>     @lastCommitId int,
>     @events nchar(64),
>     @metadata nchar(64) = NULL,
>     @snapshot nchar(64) = NULL
>   AS
>     IF EXISTS (SELECT 1 FROM [ESS].[t_streams_myStore] s WHERE s.streamId = @streamId AND s.lastCommitId != @lastCommitId)
>       RAISERROR('Concurrency exception', 0, 0)
>     ELSE
>       INSERT INTO [ESS].[t_commits_myStore] (commitRef, streamId, lastCommitId, events, metadata, snapshot)
>       VALUES (@commitRef, @streamId, @lastCommitId, @events, @metadata, @snapshot)
> }

  CREATE PROCEDURE [ESS].[sp_commitsinsert]
    @commitRef nchar(64),
    @streamId int,
    @lastCommitId int,
    @events nchar(64),
    @metadata nchar(64) = NULL,
    @snapshot nchar(64) = NULL
  AS
    IF EXISTS (SELECT 1 FROM [ESS].[t_streams_myStore] s WHERE s.streamId = @streamId AND s.lastCommitId != @lastCommitId)
      RAISERROR('Concurrency exception', 0, 0)
    ELSE
      INSERT INTO [ESS].[t_commits_myStore] (commitRef, streamId, lastCommitId, events, metadata, snapshot)
      VALUES (@commitRef, @streamId, @lastCommitId, @events, @metadata, @snapshot)

(tools) 8 % set stmt [my_mssql_con prepare $sql_stmt]
::oo::Obj22::Stmt::1
(tools) 9 % set param_dict {}
(tools) 10 % set param_dict [$stmt params]
commitRef {name commitRef direction in type wvarchar precision 255 scale 0}
  streamId {name streamId direction in type wvarchar precision 255 scale 0} 
  lastCommitId {name lastCommitId direction in type wvarchar precision 255 scale 0} 
  events {name events direction in type wvarchar precision 255 scale 0} 
  metadata {name metadata direction in type wvarchar precision 255 scale 0} 
  snapshot {name snapshot direction in type wvarchar precision 255 scale 0}
(tools) 11 % set args {}
(tools) 12 % foreach v_name [dict keys $param_dict] arg $args {
>   if { ($v_name ne {}) && ([dict get $param_dict $v_name direction] ne "out") } {
>     dict set param_dict $v_name $arg
>   }
> }
(tools) 13 % puts $param_dict
commitRef {} streamId {} lastCommitId {} events {} metadata {} snapshot {}
(tools) 14 % set resultset [$stmt execute $param_dict]
[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'PROCEDURE'.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
(executing the statement)
(tools) 15 %
(tools) 15 %
(tools) 15 % set sql_stmt {
>   CREATE PROCEDURE [ESS].[sp_commitsinsert]
>     _@commitRef nchar(64),
>     _@streamId int,
>     _@lastCommitId int,
>     _@events nchar(64),
>     _@metadata nchar(64) = NULL,
>     _@snapshot nchar(64) = NULL
>   AS
>     IF EXISTS (SELECT 1 FROM [ESS].[t_streams_myStore] s WHERE s.streamId = _@streamId AND s.lastCommitId != _@lastCommitId)
>       RAISERROR('Concurrency exception', 0, 0)
>     ELSE
>       INSERT INTO [ESS].[t_commits_myStore] (commitRef, streamId, lastCommitId, events, metadata, snapshot)
>       VALUES (_@commitRef, _@streamId, _@lastCommitId, _@events, _@metadata, _@snapshot)
> }

  CREATE PROCEDURE [ESS].[sp_commitsinsert]
    _@commitRef nchar(64),
    _@streamId int,
    _@lastCommitId int,
    _@events nchar(64),
    _@metadata nchar(64) = NULL,
    _@snapshot nchar(64) = NULL
  AS
    IF EXISTS (SELECT 1 FROM [ESS].[t_streams_myStore] s WHERE s.streamId = _@streamId AND s.lastCommitId != _@lastCommitId)
      RAISERROR('Concurrency exception', 0, 0)
    ELSE
      INSERT INTO [ESS].[t_commits_myStore] (commitRef, streamId, lastCommitId, events, metadata, snapshot)
      VALUES (_@commitRef, _@streamId, _@lastCommitId, _@events, _@metadata, _@snapshot)

(tools) 16 % set stmt [my_mssql_con prepare $sql_stmt]
::oo::Obj22::Stmt::2
(tools) 17 % set param_dict {}
(tools) 18 % set param_dict [$stmt params]
(tools) 19 % set args {}
(tools) 20 % foreach v_name [dict keys $param_dict] arg $args {
>   if { ($v_name ne {}) && ([dict get $param_dict $v_name direction] ne "out") } {
>     dict set param_dict $v_name $arg
>   }
> }
(tools) 21 % puts $param_dict

(tools) 22 % set resultset [$stmt execute $param_dict]
[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '_@commitRef'.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
(executing the statement)
(tools) 23 %
</verbatim>

<hr /><i>anonymous claiming to be twylite added on 2012-11-16 11:41:01 UTC:</i><br />
While pushing TEA build scripts for tdbcodbc I seem to have accidentally pushed the branch 'td-prism-tokenise-mod' as well, which contains our fix for this problem (which is to remove the special behaviour of '@' in tdbcTokenize.c).

<hr /><i>kbk added on 2012-11-16 20:51:22 UTC:</i><br />
This problem is reported against ODBC specifically and is being closed out from the main repository in the repository split.