subsonic / subsonic-2.0 Goto Github PK

I'm using SubSonic 2.2 with Oracle 9.2 and .NET 2.0.

Calling SqlQuery.GetRecordCount() generates an Oracle error:

ORA-00923: FROM keyword not found where expected

The method call generates SQL which looks like the following:

SELECT count(*) as 'yadda' from SOMESCHEMA.SOMETABLE

The culprit appears to be Aggregate.ToString(). Specifically, the format string it uses contains single quotes which are not allowed (in column aliases, at any rate) in Oracle. A workaround that I've implemented was to override GetCountSelect in the OracleGenerator class which simply replaces all single quotes with double quotes:

    public override string GetCountSelect()
    {
        return base.GetCountSelect().Replace('\'', '"');
    }

When InlineQuery adds parameters to its command, it always sets their database type to DbType.AnsiString. This probably works in many cases, as these strings are implicitly converted to appropriate data type, but it's dependent on regional settings of Windows and database server (in my case MS SQL Server) for some data types like DateTime, Currency etc. I noticed problems on some developer machines; InlineQuery failed with error message from the server which was unable to convert AnsiString to e.g. DateTime. Perhaps database server was running on a machine with different regional settings than client machine with .NET 2.0 application using SubSonic.

I created a small fix for version 2.2.1.0 to resolve this issue, here is a patch file which can be applied from root: http://freeweb.t-2.net/gundakar/subsonic_dbtype_patch.patch

If SubSonic 2.x is still supported at all, maybe somebody with more knowledge of the library can take a look at my fix as a starting point and do a proper solution for this issue.

Code improvements are not being merged into the source from any of the branches that developers have created. This probably won't change but perhaps someone will notice this and look at some of the forked projects.

The scaffold control is missing some key functionality out of the box in subsonic 2.2. Or it appears to, to me.

It does not appear to support paging. There is also no bindable option to restrict editing and deleting of records. It would also be useful to have a property which can restrict what columns to display in grid mode.

If I'm wrong and this code is in a version can someone point me in the write direction.

Consider this example that references two tables, Employee and Employee_Role (linking table)

SqlQuery q2 = new Select()
.From(Employee.Schema)
.Where(Employee.IDColumn).In(
new Select(Employee_Role.EmployeeIDColumn)
.From(Employee_Role.Schema)
.Where(Employee_Role.RoleIDColumn).IsEqualTo(3))
.And(Employee.IDColumn).In(
new Select(Employee_Role.EmployeeIDColumn)
.From(Employee_Role.Schema)
.Where(Employee_Role.RoleIDColumn).IsEqualTo(1));

The resulting SQL looks like this:

{SELECT [dbo].[Employee].[ID], [dbo].[Employee].[LastName], [dbo].[Employee].[FirstName], [dbo].[Employee].[MidName], [dbo].[Employee].[CommonName], [dbo].[Employee].[DepartmentNumber], [dbo].[Employee].[EmployeeNumber], [dbo].[Employee].[ManagerEmployeeNumber], [dbo].[Employee].[NetworkName], [dbo].[Employee].[Email], [dbo].[Employee].[HireDate], [dbo].[Employee].[EmploymentStatusID]

FROM [dbo].[Employee]
WHERE [dbo].[Employee].[ID] IN (SELECT [dbo].[Employee_Role].[EmployeeID]
FROM [dbo].[Employee_Role]
WHERE [dbo].[Employee_Role].[RoleID] = @RoleID0
)
AND [dbo].[Employee].[ID] IN (SELECT [dbo].[Employee_Role].[EmployeeID]
FROM [dbo].[Employee_Role]
WHERE [dbo].[Employee_Role].[RoleID] = @RoleID0
)
}

Notice how both subselects get the same parameter reference, @RoleID0, instead of @RoleID0 and @RoleID1.

Problem:
duplicate clause is being generated by the following SqlQuery object when using AnsiSqlGenerator:

SubSonic.SqlQuery q = new Select()
  .From(Views.VwSearchIndexQuery2Mtx)
  .Paged(pageNumber, maximumRows)
  .Where(VwSearchIndexQuery2Mtx.Columns.SearchIndexQueryId)
    .In(
        new Select(SearchIndexQueryGroupMap.Columns.SearchIndexQueryId)
          .From(Tables.SearchIndexQueryGroupMap)
          .Where(SearchIndexQueryGroupMap.Columns.SearchIndexQueryGroupId)
          .IsEqualTo(searchIndexQueryGroupId));

This statement works using the Sql2005 & Sql2008 generators.

Reference:
http://stackoverflow.com/questions/1711798/subsonic-2-2-sqlquery-object-generates-very-different-sql-for-where-in-stateme

When using sql 2000 and paged query subsonic 2.2 returns 'ROW_NUMBER' is not a recognized function name.

The DatabaseVersion in provider from sqlquery is showing:
"Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) \n\tMay 3 2005 23:18:38 \n\tCopyright (c) 1988-2003 Microsoft Corporation\n\tStandard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)\n"

and the utility.IsSql2005 method is evaluating for "2005"
public static bool IsSql2005(DataProvider provider)
{
return provider.DatabaseVersion.IndexOf("2005") > -1;
}
//Code from DataService.cs
if(Utility.IsSql2005(provider))
generator = new Sql2005Generator(sqlQuery);
else if(Utility.IsSql2008(provider))
generator = new Sql2008Generator(sqlQuery);
else
generator = new Sql2000Generator(sqlQuery);
break;

And the Sql2005Generator is selected Not the Sql2000Generator.

Steps:
Create a SubSonic.Update query
Add a .SetExpression(SomeDecimalColumn).EqualTo("OtherDecimalColumnName")
Call Execute() on the Update query

Result:
Error occurs stating unable to convert String to decimal.

Reason:
Execute method in Update.cs does not check each Setting in SetStatements collection for IsExpression == true. Attempts to add Setting object as a parameter to the cmd object with type decimal.

Resolution:
Only add setting parameter if s.IsExpression == false as the expression is already embedde into the built SQL query.

Note:
I have this fixed in my fork, just as soon as I check it in.

I've been trying to add a function to compare my object TableColumnSettings. When I load 2 objects using the FetchByID they match, when I change one object by editing/saving a column always get the changed column and the primary key to fail the Equals() check. I noticed the datatype on my primary key changed from Int16 to Int32 after calling Save(). I see that the SqlDataProvider.cs, ExecuteQuery(QueryCommand qry) returns a int and ActiveRecord.cs Save() calls SetPrimaryKey(pkVal) that passes on the Int32 to the current object which is causing it to go from a Int16 to a Int32, and thus fail the Equals() check. Easy fix is to reload and compare, but I wanted to share what I found after pulling out a handful of hair.

I have a very large legacy database I am using and I wanted to use subsonic against the stored procedures.
Everytime I generated, my StoredProcedures.cs remained empty.
A colleague of mine has come across this problem before and found it is because the stored procedures are too big.

If you go to this page of the souce
http://github.com/subsonic/SubSonic-2.0/blob/master/SubSonic/DataProviders/SqlDataProvider.cs

and line 1745, if I run this query in my database I get the following error:

Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8098, which is greater than the allowable maximum of 8094.

If I remove the orderby from the statement it works.
Is it possible to remove the orderby or is this needed?
Or is there another way round it?

Bex

I have come across an issue where SQL generated by SubSonic was performing very slowly, which was unexpected. I would run the generated SQL directly in Oracle and the same SQL would run much, much quicker. What gives? Well, after much debugging it turns out SubSonic was specifying an incorrect data type for bind variables. More specifically, it was telling my DB that certain columns were DbType.String (Unicode) when in fact they were not - they should have been marked as DbType.AnsiString (the columns are defined as VARCHAR2). Though the difference is seemingly minor, it was enough to cause Oracle to ignore the indicies which existed on my tables.

Example:

Say I have the following table in my schema:

CREATE TABLE suppliers
(
    supplier_id     number(10)      not null,
    supplier_name   varchar2(50)    not null
);

And I create an index on SUPPLIER_NAME:

create index IDX_SUPPLIERS_SUPPLIER_NAME on SUPPLIERS (SUPPLIER_NAME);

SubCommander would generate the following code in the GetTableSchema() method of the Suppliers class that it generates:

TableSchema.TableColumn colvarSupplierId = new TableSchema.TableColumn(schema);
colvarSupplierId.ColumnName = "SUPPLIER_ID";
colvarSupplierId.DataType = DbType.Decimal;
colvarSupplierId.MaxLength = 0;
colvarSupplierId.AutoIncrement = false;
colvarSupplierId.IsNullable = false;
colvarSupplierId.IsPrimaryKey = false;
colvarSupplierId.IsForeignKey = false;
colvarSupplierId.IsReadOnly = false;
colvarSupplierId.DefaultSetting = @"";
colvarSupplierId.ForeignKeyTableName = "";
schema.Columns.Add(colvarSupplierId);

TableSchema.TableColumn colvarSupplierName = new TableSchema.TableColumn(schema);
colvarSupplierName.ColumnName = "SUPPLIER_NAME";
colvarSupplierName.DataType = DbType.String; <<< PROBLEM!!
colvarSupplierName.MaxLength = 50;
colvarSupplierName.AutoIncrement = false;
colvarSupplierName.IsNullable = false;
colvarSupplierName.IsPrimaryKey = false;
colvarSupplierName.IsForeignKey = false;
colvarSupplierName.IsReadOnly = false;
colvarSupplierName.DefaultSetting = @"";
colvarSupplierName.ForeignKeyTableName = "";
schema.Columns.Add(colvarSupplierName);

Because the datatype is incorrect for the colvarSupplierName TableColumn, Oracle will ignore and not use the previously created index. As a result: When you're dealing with large sets of data (~500k rows, in my case) performance will suffer significantly.

After tracing the code, the problem seems to lie in OracleDataProvider.GetDbTypeOracle:

    public static DbType GetDbTypeOracle(string sqlType, int dataScale, int dataPrecision)
    {
        switch(sqlType)
        {
            case "varchar2":
            case "varchar":
            case "char":
            case "nchar":
            case "nvarchar2":
            case "rowid":
                return DbType.String;
            case "nclob":
            case "clob":
                return DbType.AnsiString;
            case "number":
            .
            .
            .

SubCommander calls the above method to determine which DataType to insert into generated code. Fixing the above should fix the problem.

Oracles documentation is a bit confusing, but if I understand this documentation correctly (http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i3253): CHAR, VARCHAR and VARCHAR2 types would be mapped to the DbType.AnsiString since they are "variable-length character strings" as opposed to NCHAR and NVARCHAR2 which "are Unicode datatypes that store Unicode character data.". The confusion comes from the fact that you can define the size of CHAR, VARCHAR and VARCHAR2 in bytes OR characters and that "Character semantics are useful for defining varying-width multibyte strings;". So...VARCHAR2 can hold a unicode string? Like I said: confusing.

When i add a new item (IsNew == true) to the active list, and then delete the item from the list, this item gets added to _deletedItems.
If i then save the item on its own and the save the collection the item gets deleted.

It should not put new item to _deletedItems

In the Constraint.cs class, the method for the SQL IN statement using a nested Select statement seems to be enforcing a constraint incorrectly. The code is adding selectQuery.SelectColumnList.Length to selectQuery.Aggregates.Count and generating an error if the sum is not 1. Well, if you are using an aggregate function the aggregate count will be 1 and the column list length will be 1 giving you a sum of 2 so it gives you an error even though the SQL would be valid.

When I pull a record from the database via ExecuteSingle, it is incorrectly marked as IsNew = true. when I pull records from the database via ExecuteAsCollection, they are correctly marked is IsNew = false. Here's some sample code that reproduces the problem:

    Dim query As SubSonic.SqlQuery = _
        New SubSonic.Select().From(User.Schema)
    Dim userCol As UserCollection = query.ExecuteAsCollection(Of UserCollection)()
    Dim u1 As User = userCol(0)
    Dim u2 As User = query.ExecuteSingle(Of User)()
    Debug.Print("u1.isNew = " & u1.IsNew)
    Debug.Print("u2.isNew = " & u2.IsNew)

Here's the output from the Debug.Print:

u1.isNew = False
u2.isNew = True

I'm using VB.NET, VS2008, and SQL Server 2005.

Some others noticed something similar on Stack Overflow: http://stackoverflow.com/questions/985834/subsonic-vb-net-problem

Here's some forensics someone did on the issue:

I checked the source code of SubSonic.. and I found that the VB class generator doesn't implements the IActiveRecord. I think most likely is because VB.Net doesn't seem to support 're-implementation' of inheritance or whatever you call that...

So when I debug, I found that Utility.IsSubSonicType returns false (because the ActiveRecord class returns as IReadOnlyRecord, but IsSubSonicType checks for IActiveRecord and IRecordBase) and thus doesn't call the SetLoadState and MarkClean.

So I'm not sure if this is a bug or it is intentional. Any way to solve this?

SubSonic 2.2.1

When using the Update method on ODSController (ie using GridView / FormView in an ASP.NET application) and passing null values to nullable columns, the nullable column value remains unchanged.

This is due to the way that update method instantiate the ActiveRecord to update:

    public void Update(Guid MyKey, ...)
    {
        MyItem item =  new MyItem();
        item.MarkOld();
        item.IsLoaded = true;
        ...
        item.Save(UserName);
    }

By creating an empty MyItem instance (all fields are null) and setting a nullable field to null doesn' t allow the column to finish in the DirtyColumns collection (see ActiveHelper GetUpdateCommand).

I'm using SubSonic 2.2 with Oracle 9.2 and .NET 2.0.

I'm seeing an issue where SubSonic does not insert WHERE or ORDER BY clauses in the pagination SQL it generates for queries created with the SqlQuery object. I've looked at the source and the method responsible (OracleGenerator.BuildPagedSelectStatement) seems incomplete: The WHERE and ORDER BY clauses are generated, but are never merged into the final string that the method returns.

Examples can be found here: http://stackoverflow.com/questions/3824483/how-does-one-do-an-oracle-top-n-paged-query-in-subsonic-2-2/

Problem:
In Utitlity.cs, SubSonic.Utilities.IsSql2008() tests for provider.DatabaseVersion using the following logic at line 199:

return provider.DatabaseVersion.IndexOf("2008 - 10.") > -1 || 
    provider.DatabaseVersion.IndexOf("2008 (RTM) - 10.") > -1 ;

You will obviously fail this test if your provider.DatabaseVersion == "Microsoft SQL Server 2008 (SP1) - 10.0.2531.0". And then you'll end up using the AnsiSqlGenerator.

Resolution:
1)
return provider.DatabaseVersion.IndexOf("2008 ") > -1 ;
or
2)
return provider.DatabaseVersion.IndexOf("2008 - 10.") > -1 ||
provider.DatabaseVersion.IndexOf("2008 (RTM) - 10.") > -1 ||
provider.DatabaseVersion.IndexOf("2008 (SP1) - 10.") > -1 ;

Reference:
http://stackoverflow.com/questions/1711798/subsonic-2-2-sqlquery-object-generates-very-different-sql-for-where-in-stateme

Subsonic version: 2.2.0.0

In a SQL 2005 database server I've created a view:

CREATE VIEW [dbo].[V_RIT_SUMMERY]
AS
SELECT u.start_dtt, u.eind_dtt, DATEDIFF(MINUTE, u.start_dtt, u.eind_dtt) AS DIFF, r.rit_nmr
FROM tb_uitvoering u
LEFT JOIN tb_rit r ON u.guid_nmr = r.guid_nmr
WHERE u.eind_dtt IS NOT NULL
GROUP BY r.rit_nmr, u.start_dtt, u.eind_dtt
GO

I use the following code to create a groupby selection with an avg on diff:

SubSonic.SqlQuery qry = new SubSonic.Select(
Aggregate.GroupBy(VRitSummery.Columns.RitNmr),
Aggregate.Avg(VRitSummery.Columns.Diff)
).
From(VRitSummery.Schema).
OrderAsc(VRitSummery.Columns.RitNmr).
Where(VRitSummery.Columns.StartDtt).IsGreaterThanOrEqualTo(startdatum).
And(VRitSummery.Columns.EindDtt).IsLessThanOrEqualTo(einddatum).
Paged(page, pageSize);

Subsonic produces the following code:

SELECT *
FROM (
SELECT ROW_NUMBER() OVER ( ORDER BY rit_nmr ASC) AS Row,rit_nmr AS 'GroupByOfrit_nmr', AVG(DIFF) AS 'AvgOfDIFF'
FROM [dbo].[V_RIT_SUMMERY]
GROUP BY rit_nmr
WHERE [dbo].[V_RIT_SUMMERY].[start_dtt] >= @start_dtt0
AND [dbo].[V_RIT_SUMMERY].[eind_dtt] <= @eind_dtt1
) AS PagedResults
WHERE Row >= 1 AND Row <= 10

There is a GROUP BY cause before the WHERE cause, in SQL this is ofcourse not correct.

ParseExpression(string expression, WhereCondition condition) of SubSonic.Where.Query.cs
returns an incorrect paramValue when the right side of expression also contains columnName

i.e. apply this to "code=BARCODE" and you'll get "BAR" instead of "BARCODE"

There are a number of files in the repository that use .net 3.5 specific features like AutoProperties and Object Initializers. Looking at the checkins from the old Google SVN it looks like most (maybe all) were added by canofunk on 4/21/09 in r521:
*CHANGED: Global - AutoProperties now used where possible.
*CHANGED: Global - Object initializers now used where possible.

These changes make it so the projects can't be compiled in a VS2005 solution.

See the error explained here:

• http://forums.subsonicproject.com/t/3708.aspx

There was supposedly a workitem here at one time but the URL is dead:

• http://www.codeplex.com/subsonic/WorkItem/View.aspx?WorkItemId=18219

The fix is in Sql2005Generator.cs, starting at line 24:

change:

    private const string PAGING_SQL =
        @"
                SELECT {7}
                FROM     (SELECT ROW_NUMBER() OVER ({1}) AS Row, 
                {0} 
                {2}
                {3}
                {4}
                )
                                       AS PagedResults
                WHERE  Row >= {5} AND Row <= {6}";

to:

    private const string PAGING_SQL =
        @"
                SELECT {8}
                FROM     (SELECT ROW_NUMBER() OVER ({1}) AS Row, 
                {0} 
                {2}
                {3}
                {4}
                {5}
                )
                                       AS PagedResults
                WHERE  Row >= {6} AND Row <= {7}";

And add this variable declaration:

        string groupby = string.Empty;

Change:

        if (qry.Aggregates.Count > 0)
            joins = String.Concat(joins, GenerateGroupBy());

to:

        if (qry.Aggregates.Count > 0)
            groupby = String.Concat(joins, GenerateGroupBy());

And change:

        string sql = string.Format(PAGING_SQL, columnList, orderby, fromLine, joins, wheres, pageStart, pageEnd, top);

to:

        string sql = string.Format(PAGING_SQL, columnList, orderby, fromLine, joins, wheres, groupby, pageStart, pageEnd, top);

I switched from using the SubSonic.dll included with SubSonic2.2.zip found on http://www.subsonicproject.com to using my own copy generated from compiling the source from GitHub. Since I switched over, none of my queries calling the Distinct() method were returning the expected results. Example:

SqlQuery sql = new Select(Widgets.Columns.WidgetType)
                   .From(Widgets.Schema)
                   .OrderAsc(Widgets.Columns.WidgetType)
                   .Distinct();

I started debugging and I found the following

From ANSISqlGenerator.GenerateCommandLine():
public virtual string GenerateCommandLine()
{
StringBuilder sb = new StringBuilder();

        //start with the SqlCommand - SELECT, UPDATE, INSERT, DELETE
        sb.Append(query.SQLCommand);
        string columnList;
        if(query.Aggregates.Count > 0)
            columnList = BuildAggregateCommands();
        else
        {
            if (query.IsDistinct)
                sb.Append(query.DistinctSpec);
            //set "TOP"
            sb.Append(query.TopSpec);

            //decide the columns

Stepping through the code shows that query.DistinctSpec returns an empty string.

Now, keep in mind: This used to work. I was under the impression that the source found on here (GitHub) was from the 2.2 build of SubSonic (despite being labeled "2.0"). Is that not the case?

NOTE: Maybe I should make this another issue, but I'll mention it here: I noticed that the DLL generated from GitHub source is ~8k smaller than the SubSonic.dll bundled with SubSonic2.2.zip

Doing some more digging, I managed to find the source for ANSISqlGenerator.cs on Google Code (Here: http://subsonicproject.googlecode.com/svn/branches/Release_2.2/SubSonic/SqlQuery/SqlGenerators/). (This time being explicitly labeled as belonging to the 2.2 branch.) This is what I found:

    public virtual string GenerateCommandLine()
    {
        StringBuilder sb = new StringBuilder();

        //start with the SqlCommand - SELECT, UPDATE, INSERT, DELETE
        sb.Append(query.SQLCommand);
        string columnList;
        if(query.Aggregates.Count > 0)
            columnList = BuildAggregateCommands();
        else
        {
            if (query.IsDistinct)
                sb.Append(SqlFragment.DISTINCT);
            //set "TOP"
            sb.Append(query.TopSpec);

            // DISTINCT"
            sb.Append(query.DistinctSpec); <<Specific to 2.2 codebase

            //decide the columns

So it appears that the 2.2 code works because it uses the SqlFragment.DISTINCT constant. Whats very interesting is that a few lines later, query.DistinctSpec is appended! Looks like query.DistinctSpec has always been broken! (If it had worked, every SELECT query generated would always have had a DISTINCT keyword injected! Ack!)

Anyone have thoughts on a fix? Personally, since Select.Distinct() assigns a value to query.DistinctSpec, I'm inclined to have it override it's base class implementation (which is what currently gets called.)

WORKAROUND:

Casting a SqlQuery back to Select works:

SqlQuery sql = new Select(Widgets.Columns.WidgetType)
                   .From(Widgets.Schema)
                   .OrderAsc(Widgets.Columns.WidgetType);

((Select)sql).Distinct();

Environment:

• SubSonic 2.2
  
• Visual Studio .NET 2005
  
• SQL Server 2000
  

Sample Table (called 'Sample')

• SampleID, type=uniqueidentifier, Primary Key
  
• SampleName, type=varchar, size=25
  

Setup Scaffold Control:

1. Create a new page (e.g. Sample.aspx).
2. Add the scaffold control to the page. (e.g. <ss:Scaffold id="scaffold" runat="server"/>
3. Set the Mode (ScaffoldMode?) property to 'Automatic'.
4. Set the TableName property to 'Sample'.

Reproduce The Error:

1. Open the page with the scaffold control.
2. Select the sample table from the table list.
3. Click the 'Add' button.
   Note: The 'Add' screen only has a text box for the 'SampleName' field/column.
   
4. Enter some text in the 'SampleName' text box.
5. Click the 'Save' button.

• An exception is throw complaining about entering a NULL value into the 'SampleID' column.

When I Googled this issue I found references to a couple of 'resolved' work items, but all of the links to the original issues were broken - probably lost when subsonic was moved to github.

I would greatly appreciate any help with this problem.