v.5.5.3 Adapter / MSSQL / TypedDataTable from sproc

Hi, today I fighted with strange behavior of TypedView resultset from sproc.

I have several apps using the same MSSQL database.

Sometimes I need to insert new column into some table. Simple job, even I do not need rebuild all apps using this changed table. Old apps doesn't know about new field, but work fine, can read and also can write (write if new column can be null of course.) The same for views, I can insert columns and old apps without resync will continue working fine.

Yesterday I changed common sproc returning resultset, I inserted another column. I expected that old apps will work fine as in sproc resultset is just extra column.

Today I realized strange behavior for changed sproc. Say I had in sproc: SELECT PartNo, LotNo, IsOK... I inserted column SerialNo and changed sproc to SELECT PartNo, LotNo, SerialNo, IsOK...

Old programs since that change returned in "IsOK" field always FALSE, finally I found out, that in fact LLBLGen takes fields by index, not by names and therefore it still takes data from 3rd column (originally IsOK column) but now 3rd field is string SerialNo, and IsOK therefore returns FALSE for any row.

EDIT: seems it returns DBnull and that is converted to False by typedview code for IsOK field

        Public Property [IsOk]() As System.Boolean
            Get
                Return CType(IIf(IsIsOkNull(), TypeDefaultValue.GetDefaultValue(GetType(System.Boolean)), Me(_parent.IsOkColumn)), System.Boolean)
            End Get
            Set
                Me(_parent.IsOkColumn) = Value
            End Set
        End Property

Is this behavior for sproc by design or is it some bug? For entities and views I can insert new fields without any problem for old apps, just in case of stored procedures I had this problem.

EDIT: Q:Why I do such dirty things like inserting fields into tables, view and sproc without rebuilding all apps using common tables/views/sproc? A: I make apps for automotive industry running in 247, impossible to test and update all mfg. apps communicating with various equipment at the same time. I never faced any issue with modified tables or views, just today having issue with modified sproc. Instead of returning OK status, all products reported today FALSE status and it took me whole day to find out the reason is: inserted field in sproc

I made sample app incl test DB see attached.

In DB there is TestTable having 1 row with column IsOK having TRUE value

If you run app then tvTable(0).IsOk = True

Now just uncomment in Db sproc row: --'xx' as InsertedField1, and run app

Now tvTable(0).IsOk returns FALSE !!!

And uncomment in Db sproc also row: --'zz' as InsertedField2,

And now there is exception: String was not recognized as a valid Boolean.Couldn't store <zz> in IsOk Column. Expected type is Boolean.

The final behavior does matter how fields are named, also if there is additional bool field in sproc after IsOK fields etc.

For Entities and Views, the field names are used in the executed query (in the select list). So the framework always retrieves the fields that are pre-defined in the Designer. You might have an entity with 20 fields, but you only need 5 fields and that's how you have designed your model. So the framework only fetches what's needed as a performance mandate.

When it comes to Stored Procedures, you don't have a Select list, you have to call the stored procedure and read the resultSet. For that the framework uses a DataReader, and for performance reasons only processes the number of columns that's known at design time, when the resultSet was read and reverse engineered in the Designer.

So if you are going to add columns to the stored procedure without using the Designer to re-read the schema and re-generate the code accordingly, you'll need to add these columns at the end of the resultSet.

Thanks for explanation, Walaa.

As resultset is mapped to TypedView, isn't there a chance to check column names returned by sproc and raise exception to avoid side effects I faced?

Column names are checked indeed but after the column count limitation. What's the use case behind inserting columns instead of appending them?

From real point of view, colums can be aded, I insert them in order to keep SQL statement keeping some "rules" like calculated columns as the last ones, if I need LotNo, then I inset LotNo bellow PartNo field, if using GroupBy, then such columns are before SUMs etc. It makes SQL statement more readable.

I can live with with current behavior, but I have to keep it in mind, that sometimes it depends on order of columns, and sometimes I can get FALSE value even there is TRUE in table, which was very confusing.

Thus I asked if there is some simple way for you to handle properly column names like you do in Entities or Views.

I know after 8 years with LLBL that I can rely on your sharp brains If you say not possible to do anything, ok I'll accept that.

Looking at the code, it should work, but in practice maybe not. I'll try your repro

"IsOK" is in the resultset "IsOk" is in the typed view.

What it does is this: it first checks if the name at ordinal X in the resultset is the same as the name of the column at offset X in the typedview. In the resultset you have "InsertedField2" at that ordinal, in the typedview you have "IsOk".

It then checks if it can find a field with the same name in the resultset. That name is "IsOk", however it can't find it (as the name is "IsOK", not "IsOk"). It therefore keeps the ordinal X for the source of 'IsOk' in the typed view (as it might be renamed in the typedview, as is the case here).

So this setup works OK as long as the fields aren't renamed in the typedview compared to the fields in the resultset. Here, the field is renamed (from IsOK to IsOk), so inserting a field won't work as it can't find back the 'IsOk' field in the resultset coming from the stored procedure.

We do this case sensitive as the code is universal and some databases are case sensitive (sql server also might be used with a case sensitive collation). So in short: in general don't rely on this, it might work, it might not.

Means, when I disable pascal casing in designer, it should be more safe.

And in case of entities or views, can this also happen?

Thanks Otis

No, switching off pascal casing doesnot solve it. I even delete generated folders and regenerate them again and rebuild TestApp. Strange

Just behavior was changed.

Pure sproc, IsOK=TRUE InsertedField1 -> IsOK=FALSE InsertedField2 ->IsOK=TRUE

Btw switching off Project/Settings/Element name construction/Make element name pascal casing and resync didnot change field names in resultset view to keep sproc casing I had to delete and reverse engineer again.

If there's a discrepancy between typed view column name and resultset column name (in your case: "IsOk" in the typedview and "IsOK" in stored proc resultset), it will search for the ordinal in which the typed view column is at in the stored proc resultset metadata (which comes from the datareader). As the column in the typedview is 'IsOk' and in the stored proc it's 'IsOK', it can't find 'IsOk' in the list of columns of the resultset of the stored proc.

So either: - rename the column in the typed view to IsOK or - rename the column in the stored procedure to IsOk

Names are synced if 'Sync mapped element names after relational model data sync' is switched on in project settings / database first development.

I changed in designer to IsOK and attached project file in my last reply. It doesnot solve anything. Did to try?

_columnIsOK = GeneralUtils.CreateTypedDataTableColumn("IsOK", "IsOK", GetType(System.Boolean), Me.Columns)

When I generate code from your project you attached on the latest 5.6 3 it works fine (uncommented both fields in the proc, removed the try catch so I see the exception, added a msg box show in the if if tvTable(0).IsOK <>true).

Will see why it doesn't work for you on 5.5

(my altered test form)

Imports TestApp

Public Class Form1

    Private connectionstring As String = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;MultipleActiveResultSets=True;Connect Timeout=10;", "Athena\SQLEXPRESS2017", "LLBLDB")

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Using adapter As New DatabaseSpecific.DataAccessAdapter(connectionstring)
            Dim tvTable As New TypedViewClasses.GetItemInfoResultTypedView

            DatabaseSpecific.RetrievalProcedures.FetchGetItemInfoResultTypedView(adapter, tvTable)
            If tvTable.Count > 0 Then
                If tvTable(0).IsOk <> True Then
                    MessageBox.Show("tvTable(0).isOk is false!")
                End If
            End If
        End Using
    End Sub
End Class

On 5.5.7 it works fine too. I don't know what's different on your end tho...

I checked inside the debugger, in DataTableFiller.cs, where the code is which makes the switch to the right ordinal, everything is done as planned now with IsOK as name for the typedviewcolumn.

Proc looks like:

USE [LLBLDB]
GO
/****** Object:  StoredProcedure [dbo].[GetItemInfo]    Script Date: 24-4-2020 17:22:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetItemInfo]


AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

 SELECT
    TraceHeaderID,
    PartNo,
    LotNo,
    Barcode,
    ComponentBlockNo,   
    'xx' as InsertedField1, 
    'zz' as InsertedField2, 
    PanelBarcode,
    IsOK
    
  FROM TestTable

END

I use 5.5.3 I plan to move to 5.7 when released. Maybe that was some bug in 5.5.3

Edit: entities and view works fine, even in pascal casing. Good news for me Just sproc resultsets behaves strange sometimes.

EDIT 2: If you comment line with "InsertedField2" then it returns FALSE in 5.5.3 , I already noted that in my reply on 24-Apr-2020 15:17:29

SELECT
    TraceHeaderID,
    PartNo,
    LotNo,
    Barcode,
    ComponentBlockNo,   
    'xx' as InsertedField1, 
    --'zz' as InsertedField2,   
    PanelBarcode,
    IsOK
    
  FROM TestTable

Rosacek wrote:

I use 5.5.3 I plan to move to 5.7 when released. Maybe that was some bug in 5.5.3

Edit: entities and view works fine, even in pascal casing. Good news for me Just sproc resultsets behaves strange sometimes.

EDIT 2: If you comment line with "InsertedField2" then it returns FALSE in 5.5.3 , I already noted that in my reply on 24-Apr-2020 15:17:29

SELECT
    TraceHeaderID,
    PartNo,
    LotNo,
    Barcode,
    ComponentBlockNo,   
    'xx' as InsertedField1, 
    --'zz' as InsertedField2,   
    PanelBarcode,
    IsOK
    
  FROM TestTable

entities work fine as the fields in the resultset are based on the fields in the entities. The proc with both fields uncommented should result in an exception, at least that's what you said earlier?

In the debugger, IsOK's value for the typedview is read from the 9th column of the resultset, (0 being the first), if both fields are uncommented, which is correct.

I'm sorry, but I used your repro, with the latest 5.5 runtime build, it worked fine, IF the typed view column field is equal named to the stored proc column name (and I've explained why). Not sure what I should do to make it fail, as I don't see how it should still fail.

Uncommenting only 1 field will return false, if the typedview field is named 'IsOk' (so lowercase k'). This is because the code can't find the 'IsOk' column in the storedproc resultset (as it's called 'IsOK') and therefore keeps the ordinal for the source of that column to 6 (which is now PanelBarCode).

it worked fine, IF the typed view column field is equal named to the stored proc column name

As you could see in attached files (I attached again VS & LLBL projects), name of stored proc columns are exactly same both in SQL and in generated GetItemInfoResultTypedView.vb

Even in this case I get FALSE, when I uncomment line in sproc "'xx' as InsertedField1"

My 5.5.3 date is 2019-05-07 10:09

If you could not find the issue, I will live with that and will try in 5.7 later on.

P.S. I hate such weird problems, it just takes time to all, sorry for that.

There's indeed a bug with the handling of the columns. It's weird that with 2 uncommented fields it apparently works, but with 1 uncommented field it fails. Will check that out too. The code which tries to find back the columns of the view in the resultset is flawed, in any case, so you're not going crazy

The code currently runs into 'InsertedField1', sees it's not in the typedview, sees that at that spot, the 'PanelBarCode' field is located in the typedview, and creates a projector for that field instead. However, the next field it runs into is ... PanelBarCode, and it happily creates a projector for that too, while it already has done so. As the loop is a for loop based on the min(typedview fieldcount, resultsetcolumns), it's done and never handles the IsOK field.

(edit). With 2 uncommented fields (so 2 inserted fields) it works, because the field 'InsertedField2' is at the spot of IsOK, and it tries to find that back when it runs into 'InsertedField2'. So in that situation it works, which is sheer luck Thanks for your persistence in this, so we unearthed the bug after all

I thought of this story thru weekend.

In case of reverse engineering typedview from sproc, it is very dangerous to use any renaming of fields in TypedView, e.g. pascal casing as it can lead to notfound column and using ordinal index. And when column order is changed or another column added to sproc, then quiet hard to detect errors can happen.

Sometimes happen that SQL table, view or sproc is changed by someone else (customer IT guys or IS vendor). If fields are renamed or missing or datatype was changed, then exception is raised and very quickly problem is solved. But not in case of sproc.

Would you consider to insert another step into column matching logic:

in case sensitive columnname is not found then columnname insensitive search happen, and if not found then ordinal index is used (or do not use ordinal index at all and throw exception like when missing column in entities or typedview raise exception)

that's actually indeed what we've implemented now (it first tries to search for the column case sensitively and then case insensitively if it's not found). It's better indeed, as it might miss cases otherwise.

5.5.7 is up shortly.

v5.5.7 and 5.6.3 are now available which fix this issue.

it first tries to search for the column case sensitively and then case insensitively if it's not found

And if still not found then exception is thrown or column index is used?

Btw I found that FALSE was returned due to default casting on bool datatype from varchar SQL column. Means do you check compaible datatypes if you insist of using column index? Just to make sure FALSE value comes from SQL and not from casting like I mentioned in the initial post

Return CType(IIf(IsIsOkNull(), TypeDefaultValue.GetDefaultValue(GetType(System.Boolean)), Me(_parent.IsOkColumn)), System.Boolean)

Rosacek wrote:

it first tries to search for the column case sensitively and then case insensitively if it's not found

And if still not found then exception is thrown or column index is used?

column index is used as before if the datatable doesn't have any columns, the method is also used for filling a datatable without any columns, or not enough columns. If the resultset field isn't found in the datatable (typedview), we ignore the resultsetfield from the resultset and move on to the next. It won't get a projector and the value isn't used.

Btw I found that FALSE was returned due to default casting on bool datatype from varchar SQL column. Means do you check compaible datatypes if you insist of using column index? Just to make sure FALSE value comes from SQL and not from casting like I mentioned in the initial post

Return CType(IIf(IsIsOkNull(), TypeDefaultValue.GetDefaultValue(GetType(System.Boolean)), Me(_parent.IsOkColumn)), System.Boolean)

No we don't check / verify types. We create a column projector for a column in the datatable, where we specify which column from the resultset the value comes from. If the datatable doesn't have any columns, they're created based on the types of the resultset.

We can't introduce a type check now, as that would be a breaking change. The code is for filling datatables and only goes wrong when a typedview/list based on a datatable is filled with a resultset that's incompatible with the datatable instance. In general this situation will go away over time anyway as it's recommeded to generate typedviews/lists as poco classes nowadays as fetching them is faster/eats less memory.

In general, it shouldn't go wrong in your situation if you add fields to a stored proc resultset: the original columns should be found back no matter what and projected properly.

The fix for this has a side effect where columns which are mapped in a typedview but have e.g. underscores (e.g. customer_id -> CustomerID) aren't picked up. This problem will be addressed in 5.6.4 and 5.7.1. (see: https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=27074)