SubSonic: All your database are belong to us

Commented Issue: In Memory Filtering: Add support for integer comparisons, incl GreaterThan et al

MWSherman — Thu, 21 Aug 2008 04:06:15 GMT

I've added support in the new Collection.Filter() method for comparing integer types.

Newly supported Comparisons are:
SubSonic.GreaterThan
.GreaterOrEquals
.LessThan
.LessOrEquals
.NotEquals

Property and where values are tested to see if they can be cast using Int32.TryParse, and the filter is performed only if true for both.

Works well for me in initial testing, feedback is welcome. This is implemented in the C# template, perhaps someone can pitch in for VB.
Comments: ** Comment from web user: MWSherman **

By the way, this builds on: http://www.codeplex.com/subsonic/WorkItem/View.aspx?WorkItemId=10036

Created Issue: How user subsonic by different oracle user

maplye — Thu, 21 Aug 2008 04:03:44 GMT

I user the connection string at my develop environment.
"Data Source=testserver;User ID=testuser;Password=test;pooling=true;"

but at my product environment,the connection string:
"Data Source=testserver;User ID=prouser;Password=test;pooling=true;"

the User ID is different.

so, Generated sql is "select * from testuser.tablename" at the product environment.
this is error.

What should I do?

I generated code by "TestUser".

Created Issue: In Memory Filtering: Add support for integer comparisons, incl GreaterThan et al

MWSherman — Thu, 21 Aug 2008 03:54:11 GMT

Created Issue: SubStage Oracle Invoke Providers Error(version 2.1)

maplye — Thu, 21 Aug 2008 02:48:11 GMT

i set a oracle project in the substage,when i click the invoke providers button,i got the error:

System.IndexOutOfRangeException: SPSchema
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.OracleClient.OracleDataReader.GetOrdinal(String name)
at System.Data.OracleClient.OracleDataReader.get_Item(String name)
at SubSonic.DataService.GetSPSchemaCollection(String providerName)

so, i read the GetSPSchemaCollection source code,i found

//get the params
using(IDataReader rdr = GetSPParams(s, providerName))
{
while(rdr.Read())
{
sp.SchemaName = rdr["SPSchema"].ToString();
StoredProcedure.Parameter par = new StoredProcedure.Parameter();
provider.SetParameter(rdr, par);
par.QueryParameter = provider.MakeParam(par.Name);
par.DisplayName = Utility.GetParameterName(par.Name, provider);
sp.Parameters.Add(par);
}
rdr.Close();
}

the rdr datareader have not the "SPSchema" column,when the dataprovider is OracleDataProvider.

the OracleDataProvider class have the GetSPParams method.
the method execute SP_PARAM_SQL sql.

private const string SP_PARAM_SQL =
@"SELECT a.object_name, a.object_type, b.position, b.in_out,
b.argument_name, b.data_type, b.char_length, b.data_precision, b.data_scale
FROM user_objects a, user_arguments b
WHERE a.object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
AND a.object_id = b.object_id
AND a.object_name = :objectName";

but the sql don't have the "SPSchema" column.

Created Issue: AndExpression and OrExpression not overloaded for TableSchema.TableColumn

DougPickering — Mon, 18 Aug 2008 18:52:14 GMT

In the SqlQuery class the AndExpression and OrExpression methods are not overloaded in teh same way as And and Or. This caused me an issue recently when I wanted to use AndExpression with a datetime type. The standard method only accepts the column name as a string and is not able to tell that the paramater should be anything other than a string.

Please can the AndExpressiona dn OrExpression be overloaded in the same way as And and Or.

Commented Issue: controller template for update cannot tell accurately which fields are dirty (especially bit field)

DougPickering — Mon, 18 Aug 2008 18:46:48 GMT

It is probably easier to explain this with an example.

We have a SQL server table :

CREATE TABLE [PSCustomer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [nvarchar](50) NULL,
[RequiresAuthorisedIP] [bit] NOT NULL)

This creates a controller class with the following:
/// <summary>
/// Updates a record, can be used with the Object Data Source
/// </summary>
[DataObjectMethod(DataObjectMethodType.Update, true)]
public void Update(int CustomerID,string CustomerName,bool RequiresAuthorisedIP)
{
PSCustomer item = new PSCustomer();
item.MarkOld();
item.IsLoaded = true;

item.CustomerID = CustomerID;

item.CustomerName = CustomerName;

item.RequiresAuthorisedIP = RequiresAuthorisedIP;

item.Save(UserName);
}

If I call Update(1,"Test Customer",true); The RequiresAuthorisedIP field gets updated to 'true'. If I now call Update(1,"Test Customer",false); the RequiresAuthorisedIP remains as false.

The reason is that the field RequiresAuthorisedIP is not being seen as 'dirty'. This is because the customer record is never actually loaded so the the values of the old fields are set to the defaults for an empty record. In our case the bit field would be a default of 'false'. This result to ignore the change to false with the resulting SQL being:
UPDATE [PSCustomer] SET [CustomerName] = @CustomerName WHERE [CustomerID] = @CustomerID; SELECT @CustomerID AS id

I beleive there are other issues which have been raised in the past often describing this behaviour when they the desired result is to set a field to null on update.
Comments: ** Comment from web user: DougPickering **

I have now gone through a lot of other items and found many talking about how update should work. I can understand the benefits of only updating the fields that have changed in the standard context of update and can also see the reason why in the controller class the record is not first read for performance reasons. I think there are two ways forward for the controller class, one is to provide a way to update all columns (bar the PK), the second is to first read the record before determining which records have changed. Perhaps even provide an update method for both.

Commented Issue: Schema of StoredProcedure

DougPickering — Mon, 18 Aug 2008 18:40:38 GMT

Subsonic should use known schema (owner) of stored procedure.

Subsonic 2.0 generates SPs class with StoredProcedure definitions:

public static StoredProcedure ProcedureName(string ProcedureParameter)
{
SubSonic.StoredProcedure sp = new SubSonic.StoredProcedure("ProcedueName" , DataService.GetInstance("SqlProvider"));
sp.Command.AddParameter("@ProcedureParameter", ProcedureParameter, DbType.String);
return sp;
}

But when the owner of this SP is not 'dbo', then te call* fails, because server cannot find stored procedure 'ProcedureName'. The workaround is to change sp.Command.CommandSql to 'owner' + sp.Command.CommandSql, but now, "part of my database belongs back to me" :-)

*) DataSet ds = SPs.ProcedureName("TEST").GetDataSet();
Comments: ** Comment from web user: DougPickering **

I think this may have been superceded in 2.1. Please see 17917

Commented Issue: WhereExpression("FREETEXT","COLUMN_NAME", "SearchString")

DougPickering — Mon, 18 Aug 2008 16:52:33 GMT

I added a WhereExpression, AndExpression and OrExpression.
Like - SqlQuery WhereExpression(string expression, string columnName, string value)

So that you can get something like "WHERE (FREETEXT(COLUMN_NAME, @FREETEXTCOLUMN_NAME))" with Query2
Comments: ** Comment from web user: DougPickering **

I have modified our own copy of SubSonic to add the ability to have a 'Contains' clause. This allows our code to use the freetext index we have and get dramatic speed improvements. I am not sure how this would be best implemented in such a way that it takes into account the other data providers needs. I have added 'contains' as a Comparison type in the same way as Equals, NotEquals etc. I then had to modify several ares to handle the comparison type and change dataprovider to use the correct syntax. It would be possible to substitute 'contains' with 'like' for other providers but I am not sure this is in the right spirit. But then how would it be best to handle freetext?

Commented Issue: Error "Incorrect syntax near the keyword 'WHERE'" (patch included)

DougPickering — Mon, 18 Aug 2008 16:29:40 GMT

The problem:
in some circustances an incorrect sql with empty parameters is generated when one tries to update a record that was not changed.

sample incorrect query generated:
exec sp_executesql N'UPDATE [dbo].[Articles] SET WHERE [ItemID] = @ItemID; SELECT @ItemID AS id',N'@ItemID int',@ItemID=10

thet returns follow error:
"Incorrect syntax near the keyword 'WHERE'"

fix:
the fix is actually really easy and needs only to add an extra check in active helper (ActiveHelper.cs)
please include the patch on repository when you can, it should be a simple and no-problem task
thanks
manu

patch follows
(attached as well, pick the one you want, they are the same)

Index: ActiveHelper.cs
===================================================================
--- ActiveHelper.cs (revision 442)
+++ ActiveHelper.cs (working copy)
@@ -144,6 +144,11 @@
if(!cmd.Parameters.Contains(modByParamName))
cmd.Parameters.Add(modByParamName, userName, DbType.String);
}
+
+ //if nothing has changed
+ if (cmd.Parameters.Count == 0)
+ return null;
+
//make sure to set the PKs
foreach(TableSchema.TableColumn col in BaseSchema.PrimaryKeys)
cmd.Parameters.Add(col.ParameterName, item.GetColumnValue<object>(col.ColumnName), col.DataType);
Comments: ** Comment from web user: DougPickering **

I can recreate on SQL Server 2005 using the example I give in [workitem:17917] and Update(99, null, false); This will try to update customer 99 but decide that null and false are both the default values and then not find any columns to update. The result is an update statement with no 'set value...'.

I am in favour of the patch above to prevent the error, however it also hides the issue described in 17917.

Commented Issue: Incorrect syntax near the keyword 'WHERE'.

DougPickering — Mon, 18 Aug 2008 16:15:53 GMT

I got the error "Incorrect syntax near the keyword 'WHERE'." while executing Update method, for example:

DAL.Customer.Update(1,"Apple")

Running the profiler i found that the updated field is missing:

UPDATE [dbo].[Customer] SET WHERE [IDCustomer] = @IDCustomer;
Comments: ** Comment from web user: DougPickering **

There isn't enough information here for me to be certain, but this could be a symptom of the problem described in [workitem:17916]. In your example, is there a default value for the column which is set to "Apple"?

Created Issue: SQL server SPs class not generated with stored procedure schema name

DougPickering — Mon, 18 Aug 2008 16:12:26 GMT

When SubCommander generates the SPs class it places a stub for each stored procedure such as:

public static StoredProcedure CorrectMappings()
{
SubSonic.StoredProcedure sp = new SubSonic.StoredProcedure("CorrectMappings", DataService.GetInstance("PSystem"), "");

return sp;
}

If the stored procedure is in a schema other than dbo the schema name is not being placed as the last argument. The code above should read:

public static StoredProcedure CorrectMappings()
{
SubSonic.StoredProcedure sp = new SubSonic.StoredProcedure("CorrectMappings", DataService.GetInstance("PSystem"), "OtherSchema");

return sp;
}

Created Issue: controller template for update cannot tell accurately which fields are dirty (especially bit field)

DougPickering — Mon, 18 Aug 2008 16:00:17 GMT

Created Issue: Invoke Providers problem using Oracle

rustyUK — Mon, 18 Aug 2008 11:47:43 GMT

Hi

SubStage is great - well done!

I've tried Invoking Providers on an Oracle provider.

At first I got unhandled exception: SPSchema
I got the same error setting UseSPSs to false.

Then, telling it not to use SPs and setting ExcludeSPs to * I got unhandled exception
parsing "*". Quantifier {x,y} following nothing.

Cheers
Russ

Commented Issue: IsLoaded Flag May be Improperly Set to "true"

erickemp — Fri, 15 Aug 2008 19:55:35 GMT

If my id do not exist when I load an object, the flag IsLoaded is true, but I think that the logic is wrong. The flag IsLoaded should be set to true only when the data has been found (loaded), otherwise it should be set to false. On the past version version 2.0.3 the flag IsLoaded was set to false if no data had been loaded.

Example:
id do not exist
Product p = new Product(id)
p.IsLoaded is true

id exist
Product p = new Product(id)
p.IsLoaded is true
Comments: ** Comment from web user: erickemp **

Fixed in Revision 481.

Commented Issue: SqlDataProvider INFORMATION_SCHEMA Queries Should Respect Original Casing

erickemp — Fri, 15 Aug 2008 19:55:08 GMT

Some of the system column names in the const MANY_TO_MANY_CHECK_ALL in SqlDataProvider should be all upper case in order to work on a case sensitive server installation.

This is necessary even if the database itself uses a case insensitive collation - the query uses views from INFORMATION.SCHEMA which are defined in the master database, and the master database uses the (case sensitive) collation of the server instance.

See also: http://forums.subsonicproject.com/forums/p/3066/12743.aspx#12743

The joined patch did resolve the issue.
Comments: ** Comment from web user: erickemp **

Fixed in Revision 481.

Commented Issue: Manual Modification of Audit Field Can Create Update Exception

erickemp — Fri, 15 Aug 2008 19:54:34 GMT

When you have fields: ModifiedOn, ModifiedBy, CreatedBy, ModifiedBy in one of your table and you set by yourself those values, you get an exception:

Example:
CategorieTest c = new CategorieTest(id);
c.ModifiedOn = DateTime.Now;
c.Save("myUser");

I get this exception:
Column name 'ModifiedOn' appears more than once in the result column list.
Comments: ** Comment from web user: erickemp **

Fixed in Revision 481.

Commented Issue: Problem with ModifiedOn

erickemp — Fri, 15 Aug 2008 19:28:36 GMT

Thanks for the report... I've fixed this for my next check-in...

Commented Issue: Invalid column names in SqlDataProvider when used on case sensitive server

erickemp — Fri, 15 Aug 2008 19:27:08 GMT

Thanks for the report and the patch! I've fixed this for my next check-in...

Created Issue: Incorrect syntax near the keyword 'WHERE'.

dBijis — Fri, 15 Aug 2008 08:50:07 GMT

Commented Issue: Stored Procedures without parameters execution failure

MarcMez — Thu, 14 Aug 2008 21:56:32 GMT

With SQL Server 2005 provider , stored procedures without parameters failed when Execute is called. The Command object is not created and A NullObjectException is throwed.
Version used: 2.1
Comments: ** Comment from web user: MarcMez **

I believe this issue has been addressed already in 2.1.1?
See Work Item here: http://www.codeplex.com/subsonic/WorkItem/View.aspx?WorkItemId=17342