First Taste of SubSonic

Having heard about SubSonic for almost a year, I finally decided to give it a try. I used SubSonic 2.1 Beta 2 and connected to Oracle9i database. Here were the two errors I encountered in the process, mostly related to stored procedures, and how I worked around the troubles. For the first error, I can use the precompiled SubSonic.dll directly. But to fix the second error, I had to modify the SubSonic source code.

The first error was related to the parameters I pass to a stored procedure. I got the following error after executing the stored procedure. Checking the source code, I found SubSonic add ':' as a prefix for each parameter name of stored procedures. Removing the colon before calling the Execute method solve the problem.Publish Post

System.Data.OracleClient.OracleException: ORA-06550: line 1, column 44:
PLS-00103: Encountered the symbol ":" when expecting one of the following:

( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol "( was inserted before ":" to continue.

The second error was related to the output parameter of a stored procedure. The output parameter I expected was a DateTime but I kept getting the following error.

System.Data.OracleClient.OracleException: ORA-06502: PL/SQL: numeric or value error

Checking the source code, I found the AddParams method in OracleDataProvider class didn't set the parameter direction. I checked MySqlDataProvider.cs and made a modification to OracleDataProvider.cs in the same manner. The error was gone. Also, I found the CheckoutOutputParams method in MySqlDataProvider.cs didn't exist in OracleDataProvider. So I also added the same logic to OracleDataProvider.

69 private static void AddParams(OracleCommand cmd, QueryCommand qry)

70 {

71 if(qry.Parameters != null)

72 {

73 foreach(QueryParameter param in qry.Parameters)

74 {

75 OracleParameter sqlParam = new OracleParameter();

76 sqlParam.DbType = param.DataType;

77 sqlParam.OracleType = GetOracleType(param.DataType);

78 sqlParam.ParameterName = param.ParameterName;

79 sqlParam.Value = param.ParameterValue;

80 if (qry.CommandType == CommandType.StoredProcedure)

81 {

82 switch (param.Mode)

83 {

84 case ParameterDirection.InputOutput:

85 sqlParam.Direction = ParameterDirection.InputOutput;

86 break;

87 case ParameterDirection.Output:

88 sqlParam.Direction = ParameterDirection.Output;

89 break;

90 case ParameterDirection.ReturnValue:

91 sqlParam.Direction = ParameterDirection.ReturnValue;

92 break;

93 case ParameterDirection.Input:

94 sqlParam.Direction = ParameterDirection.Input;

95 break;

96 }

97 }

98 cmd.Parameters.Add(sqlParam);

99 }

100 }

101 }