Subsonic 2.2/2.1 bug

subsonic

This is not a question but a bug in subsonic 2.1/2.2

The method

public QueryCommand BuildUpdateCommand(Query qry)

in Dataprovider.cs

generates and invalid Update statement if the Table to be updated is a member of a Schema, i.e. a Schema other than "dbo".

The offending line is

sql.Append(qry.Provider.DelimitDbName(table.Name));

The resulting SQL is "Update <tableName> SET....."
However the correct SQL should be "Update <SchemaName>.<tableName>...."

Commenting out the above line and replacing it with:

sql.Append(table.QualifiedName);

generates the correct SQL.

Best Answer

Reporting this to the product's developers may be a better option in getting it solved.

Related Solutions

Inserting a new record pattern in SubSonic 3

First and foremost: the t4 templates are there for you to change as needed with SS3. That was the main idea with using T4 - I don't want to back you into my silliness :).

To the question at hand - I think this might be a bug in our templates that refuses to stick a value into the PK field:

    ISqlQuery BuildInsertQuery(T item) {
        ITable tbl = _db.FindTable(typeof(T).Name);
        Insert query = null;
        if (tbl != null) {
            var hashed = item.ToDictionary();
            query = new Insert(_db.Provider).Into<T>(tbl); ;
            foreach (string key in hashed.Keys) {
                IColumn col = tbl.GetColumn(key);
                if (!col.IsPrimaryKey) {
                    query.Value(key, hashed[key]);
                }
            }
        }
        return query;
    }

In this, our check should actually be...

                if (!col.IsPrimaryKey && !col.AutoIncrement) {
                    query.Value(key, hashed[key]);
                }

In this way, the non-identity will be inserted. But in reading your issue here, it sounds to me like you're not trying to insert into a non-identity.

The email you sent me doesn't say anything about PKs as identity - your PK was a thing called "NAme" which is a string type and not and identity (auto-increment).

I'm wondering about when I cannot get around this issue--when I have to add a new record to the database and have an identity as my primary key.

This is what SubSonic assumes - that your PK is an IDENTITY column. If you ONLY have an IDENTITY column, we can't help you because this is a deadlocked table in that you can't insert any value into it, therefor you can't tick the IDENTITY column. Your only recourse at this point is to SET IDENTITY INSERT="off", which defeats the purpose.

Hopefully this will answer your question? If I'm not getting it - can you do this for me:

One sentence: what can't you do and what's the error
What did you expect

Thanks Will and I hope I'm not being thick.

Subsonic alias query – how

new Select("GTekst = ArrGruppe.Tekst", "GLTekst = ArrGruppeLinie.Tekst")
  .From(ArrGruppeLinie.Schema)
  .InnerJoin(ArrGruppe.Columns.Id, ArrGruppeLinie.Columns.ArrGruppeID)
  .Where(ArrDeltager.Columns.Kategori).IsLessThan(20)
  .And(Arrgruppe.Columns.Tekst).Like("mytext");

If not try upgrading to the latest version of SubSonic http://code.google.com/p/subsonicproject/downloads/list because you may be hitting the following issue (fixed in 2.2)

Google Issue 31 - Where Expression not formatting correctly with qualified column name

Best Answer

Related Solutions

Inserting a new record pattern in SubSonic 3

Subsonic alias query – how

Related Topic