Loading basic selfservice object

Posts   
1  /  2
 
    
Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 26-Oct-2007 08:30:57   

Hi team,

I'm really impressed to see Sybase support has been added - thanks for this. It's enabled me to convince my managers to give your highly regarded product a try... I just connected to our Sybase ASE 12.5.3 db and generated selfservicing style entities without issue.

I'm trying to load a ScriptEntity (myScript), which has a 1:n relationship with ScriptLineEntity collection (i.e. myScript.ScriptLine). The ScriptEntity element does NOT get populated, HOWEVER the collection (ScriptLineEntity) does! (it returns 4 records, which is correct) I must be doing something rather silly..

ScriptEntity myScript = new ScriptEntity(39534);

..where 39534 is the pk and does exist. This property is a decimal type in .NET, and the relevant column is of type numeric(6,0) in Sybase. The myScript.IsNew property reports true, however the myScript.ScriptLine[0].IsNew reports false (and is populated.. so this sounds right)

Any ideas where I should start looking?

Environment: LLBLGen: 2.5 FINAL DEMO Sybase ADO.NET driver: 1.15.186.0 Visual Studio 2005/.NET 2.0 Windows XP Pro Sp2

Walaa avatar
Walaa
Support Team
Posts: 14643
Joined: 21-Aug-2005
# Posted on: 26-Oct-2007 09:53:17   

First start by capturing the generated queries, and run them manually against the database (post them here if possible). And see if the query for the main entity returns a result.

To know how to capture the generated queries, please consult the manual's section: Using the generated code -> Troubleshooting and debugging

Also, would you please post a complete code snippet?

Thanks.

Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 29-Oct-2007 00:30:17   

Hi Walaa,

Thanks for the response, that debug tracing is a very cool feature.

My source code is simple, it's just calling the entities created by LLBLGen without alteration - would you like these also? No exceptions are being raised.

decimal dTestKey = 39534;
try {
    ScriptEntity myScript = new ScriptEntity(dTestKey);
    grdMain.DataSource = myScript.ScriptLine;
} catch (Exception ex) {
      ExHandler.Get().HandleException("LLBLGen Test", ex);
}

VS did warn about the conflicting versions of the Sybase.Ase.Client assembly, and created the following redirect tag in app.config:

<assemblyIdentity name="Sybase.Data.AseClient" publicKeyToken="26E0F1529304F4A7" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-1.15.186.0" newVersion="1.15.186.0"/>

Following is the captured SQL for the main entity (Script) which did not populate the ScriptEntity object. I ran this query using Aqua Data Studio against the db and it returns the matching row. I also tried calling the same query manually using the same 1.15.186.0 Sybase.Data.AseClient assembly + C#. The @ScriptId1 parameter was of DbType Decimal, with a precision of 6. This also also returned the relevant row.

SELECT sybivr.dbo.ITV_SCRIPT_TRN.SCRIPT_ID AS ScriptId, sybivr.dbo.ITV_SCRIPT_TRN.SHOW_ID AS ShowId, sybivr.dbo.ITV_SCRIPT_TRN.ORIGINAL_LIVE_SCRIPT AS OriginalLiveScript, sybivr.dbo.ITV_SCRIPT_TRN.VERSION_NUMBER AS VersionNumber, sybivr.dbo.ITV_SCRIPT_TRN.CREATION_DATE AS CreationDate, sybivr.dbo.ITV_SCRIPT_TRN.LAST_CHANGED_DATE AS LastChangedDate, sybivr.dbo.ITV_SCRIPT_TRN.LAST_CHANGED_TIME AS LastChangedTime, sybivr.dbo.ITV_SCRIPT_TRN.USER_ID AS UserId, sybivr.dbo.ITV_SCRIPT_TRN.TIME_LENGTH AS TimeLength, sybivr.dbo.ITV_SCRIPT_TRN.TIME_SLOT_ID AS TimeSlotId, sybivr.dbo.ITV_SCRIPT_TRN.PRESENTERS AS Presenters, sybivr.dbo.ITV_SCRIPT_TRN.GUESTS AS Guests, sybivr.dbo.ITV_SCRIPT_TRN.PRODUCERS AS Producers, sybivr.dbo.ITV_SCRIPT_TRN.SCRIPT_TYPE AS ScriptType, sybivr.dbo.ITV_SCRIPT_TRN.AIR_DATE AS AirDate, sybivr.dbo.ITV_SCRIPT_TRN.SCHEDULER AS Scheduler, sybivr.dbo.ITV_SCRIPT_TRN.COMMENTS AS Comments, sybivr.dbo.ITV_SCRIPT_TRN.SHOW_SEQ AS ShowSeq, sybivr.dbo.ITV_SCRIPT_TRN.ISS_SERVICE_NAME AS IssServiceName, sybivr.dbo.ITV_SCRIPT_TRN.ISS_CAMPAIGN AS IssCampaign, sybivr.dbo.ITV_SCRIPT_TRN.ISS_CHANNEL_ID AS IssChannelId, sybivr.dbo.ITV_SCRIPT_TRN.ISS_CHANNEL_ENHANCEMENT_ID AS IssChannelEnhancementId, sybivr.dbo.ITV_SCRIPT_TRN.ISS_SERVICE_PROVIDER_ID AS IssServiceProviderId, sybivr.dbo.ITV_SCRIPT_TRN.PAL_BUDGET AS PalBudget, sybivr.dbo.ITV_SCRIPT_TRN.PAL_TARGET AS PalTarget, sybivr.dbo.ITV_SCRIPT_TRN.SHOW_THEME AS ShowTheme, sybivr.dbo.ITV_SCRIPT_TRN.SHOW_DESCRIPTION AS ShowDescription, sybivr.dbo.ITV_SCRIPT_TRN.ACTIVE_DATE_TIME AS ActiveDateTime, sybivr.dbo.ITV_SCRIPT_TRN.CATEGORY_MANAGER AS CategoryManager, sybivr.dbo.ITV_SCRIPT_TRN.UPDATE_USER AS UpdateUser, sybivr.dbo.ITV_SCRIPT_TRN.UPDATE_DATETIME AS UpdateDatetime, sybivr.dbo.ITV_SCRIPT_TRN.L_COLOUR AS LColour, sybivr.dbo.ITV_SCRIPT_TRN.SHOW_NAME AS ShowName, sybivr.dbo.ITV_SCRIPT_TRN.START_TIME AS StartTime, sybivr.dbo.ITV_SCRIPT_TRN.END_TIME AS EndTime, sybivr.dbo.ITV_SCRIPT_TRN.SLOT_ID AS SlotId, sybivr.dbo.ITV_SCRIPT_TRN.PRESENTERS_CODES AS PresentersCodes, sybivr.dbo.ITV_SCRIPT_TRN.GUESTS_CODES AS GuestsCodes, sybivr.dbo.ITV_SCRIPT_TRN.PRODUCERS_CODES AS ProducersCodes, sybivr.dbo.ITV_SCRIPT_TRN.MODELS AS Models, sybivr.dbo.ITV_SCRIPT_TRN.GUEST_PHONES AS GuestPhones, sybivr.dbo.ITV_SCRIPT_TRN.FREE_POSTAGE AS FreePostage, sybivr.dbo.ITV_SCRIPT_TRN.DIGITAL_GUIDE_SYNOPSIS AS DigitalGuideSynopsis FROM sybivr.dbo.ITV_SCRIPT_TRN WHERE ( sybivr.dbo.ITV_SCRIPT_TRN.SCRIPT_ID = @ScriptId1)
Parameter: @ScriptId1 : VarNumeric. Length: 6. Precision: 6. Scale: 0. Direction: Input. Value: binary lob.

The 2nd query called by LLBLGen, which did populate the collection entity ok (i.e. myScript.ScriptLine) - is:

SELECT sybivr.dbo.ITV_SCRIPT_LINE_TRN.SCRIPT_LINE_NO AS ScriptLineNo, sybivr.dbo.ITV_SCRIPT_LINE_TRN.SCRIPT_ID AS ScriptId, sybivr.dbo.ITV_SCRIPT_LINE_TRN.PRODUCT_NO AS ProductNo, sybivr.dbo.ITV_SCRIPT_LINE_TRN.SELLING_PRICE AS SellingPrice, sybivr.dbo.ITV_SCRIPT_LINE_TRN.POSTAGE_PACKAGING AS PostagePackaging, sybivr.dbo.ITV_SCRIPT_LINE_TRN.SCRIPT_PRICE AS ScriptPrice, sybivr.dbo.ITV_SCRIPT_LINE_TRN.START_QTY AS StartQty, sybivr.dbo.ITV_SCRIPT_LINE_TRN.QTY_SOLD AS QtySold, sybivr.dbo.ITV_SCRIPT_LINE_TRN.CUMMULATIVE_AIR_TIME AS CummulativeAirTime, sybivr.dbo.ITV_SCRIPT_LINE_TRN.AIR_TIME_PAID AS AirTimePaid, sybivr.dbo.ITV_SCRIPT_LINE_TRN.PLANNED_AIR_TIME AS PlannedAirTime, sybivr.dbo.ITV_SCRIPT_LINE_TRN.APPEARANCE_SEQUENCE AS AppearanceSequence, sybivr.dbo.ITV_SCRIPT_LINE_TRN.PREVIEW AS Preview, sybivr.dbo.ITV_SCRIPT_LINE_TRN.RETAIL_PRICE AS RetailPrice, sybivr.dbo.ITV_SCRIPT_LINE_TRN.LAST_TIME AS LastTime, sybivr.dbo.ITV_SCRIPT_LINE_TRN.COMMENTS AS Comments, sybivr.dbo.ITV_SCRIPT_LINE_TRN.NZ_POSTAGE_PACKAGING AS NzPostagePackaging, sybivr.dbo.ITV_SCRIPT_LINE_TRN.ASIA_POSTAGE_PACKAGING AS AsiaPostagePackaging, sybivr.dbo.ITV_SCRIPT_LINE_TRN.US_SELLING_PRICE AS UsSellingPrice, sybivr.dbo.ITV_SCRIPT_LINE_TRN.TVSN_PRICE AS TvsnPrice, sybivr.dbo.ITV_SCRIPT_LINE_TRN.ITV_PROMPT_TO_BUY AS ItvPromptToBuy, sybivr.dbo.ITV_SCRIPT_LINE_TRN.ITV_PRODUCT_DESCRIPTION AS ItvProductDescription, sybivr.dbo.ITV_SCRIPT_LINE_TRN.PRODUCT_STATUS AS ProductStatus, sybivr.dbo.ITV_SCRIPT_LINE_TRN.COLOUR_SIZE_QTY AS ColourSizeQty, sybivr.dbo.ITV_SCRIPT_LINE_TRN.PREVIOUS_PRICE AS PreviousPrice, sybivr.dbo.ITV_SCRIPT_LINE_TRN.L_PRICES AS LPrices, sybivr.dbo.ITV_SCRIPT_LINE_TRN.STRAP_LEVEL_ONE AS StrapLevelOne, sybivr.dbo.ITV_SCRIPT_LINE_TRN.STRAP_LEVEL_TWO AS StrapLevelTwo, sybivr.dbo.ITV_SCRIPT_LINE_TRN.L_COLOUR AS LColour, sybivr.dbo.ITV_SCRIPT_LINE_TRN.UPDATE_USER AS UpdateUser, sybivr.dbo.ITV_SCRIPT_LINE_TRN.UPDATE_DATETIME AS UpdateDatetime, sybivr.dbo.ITV_SCRIPT_LINE_TRN.STRAP_LEVEL_ONE_DESC AS StrapLevelOneDesc, sybivr.dbo.ITV_SCRIPT_LINE_TRN.STRAP_LEVEL_TWO_DESC AS StrapLevelTwoDesc, sybivr.dbo.ITV_SCRIPT_LINE_TRN.L_COLOUR_STRAP_L1 AS LColourStrapL1, sybivr.dbo.ITV_SCRIPT_LINE_TRN.L_COLOUR_STRAP_L2 AS LColourStrapL2, sybivr.dbo.ITV_SCRIPT_LINE_TRN.STRAP_LEVEL_THREE AS StrapLevelThree, sybivr.dbo.ITV_SCRIPT_LINE_TRN.STRAP_LEVEL_THREE_DESC AS StrapLevelThreeDesc, sybivr.dbo.ITV_SCRIPT_LINE_TRN.L_COLOUR_STRAP_L3 AS LColourStrapL3, sybivr.dbo.ITV_SCRIPT_LINE_TRN.DELAY_SALE_FLAG AS DelaySaleFlag, sybivr.dbo.ITV_SCRIPT_LINE_TRN.POTENTIAL_SALES AS PotentialSales, sybivr.dbo.ITV_SCRIPT_LINE_TRN.POTENTIAL_MARGIN AS PotentialMargin, sybivr.dbo.ITV_SCRIPT_LINE_TRN.L_PRICES_CODES AS LPricesCodes FROM sybivr.dbo.ITV_SCRIPT_LINE_TRN WHERE ( ( sybivr.dbo.ITV_SCRIPT_LINE_TRN.SCRIPT_ID = @ScriptId1))
    Parameter: @ScriptId1 : VarNumeric. Length: 6. Precision: 6. Scale: 0. Direction: Input. Value: binary lob.
daelmo avatar
daelmo
Support Team
Posts: 8152
Joined: 28-Nov-2005
# Posted on: 29-Oct-2007 03:00:25   

Hi Jamanga,

Everything seems to be OK with your code. So we are investigating this, we'll post the answer A.S.A.P.

Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 29-Oct-2007 04:17:36   

Ok thanks daelmo. Here are the table creation statements, if this helps. Within the project I renamed the ITV_SCRIPT_TRN to Script, and ITV_SCRIPT_LINE_TRN to ScriptLine prior to Generation.

CREATE TABLE dbo.ITV_SCRIPT_TRN ( 
    SCRIPT_ID                   numeric(6,0) NOT NULL,
    SHOW_ID                 varchar(12) NOT NULL,
    ORIGINAL_LIVE_SCRIPT        numeric(6,0) NULL,
    VERSION_NUMBER              int NOT NULL,
    CREATION_DATE               datetime NOT NULL,
    LAST_CHANGED_DATE           datetime NULL,
    LAST_CHANGED_TIME           datetime NULL,
    USER_ID                 varchar(30) NULL,
    TIME_LENGTH             numeric(8,2) NOT NULL,
    TIME_SLOT_ID                varchar(24) NOT NULL,
    PRESENTERS                  varchar(70) NULL,
    GUESTS                      varchar(70) NULL,
    PRODUCERS                   varchar(70) NULL,
    SCRIPT_TYPE             varchar(20) NOT NULL,
    AIR_DATE                    datetime NOT NULL,
    SCHEDULER                   varchar(30) NULL,
    COMMENTS                    varchar(90) NULL,
    SHOW_SEQ                    int NULL,
    ISS_SERVICE_NAME            varchar(60) NULL,
    ISS_CAMPAIGN                varchar(24) NULL,
    ISS_CHANNEL_ID              varchar(30) NULL,
    ISS_CHANNEL_ENHANCEMENT_ID  varchar(60) NULL,
    ISS_SERVICE_PROVIDER_ID     varchar(30) NULL,
    PAL_BUDGET                  numeric(10,2) NULL,
    PAL_TARGET                  numeric(10,2) NULL,
    SHOW_THEME                  varchar(80) NULL,
    SHOW_DESCRIPTION            varchar(80) NULL,
    ACTIVE_DATE_TIME            datetime NULL,
    CATEGORY_MANAGER            varchar(50) NULL,
    UPDATE_USER             varchar(50) DEFAULT user NULL,
    UPDATE_DATETIME         datetime DEFAULT GETDATE() NULL,
    L_COLOUR                    varchar(4) NULL,
    SHOW_NAME                   varchar(60) NULL,
    START_TIME                  datetime NULL,
    END_TIME                    datetime NULL,
    SLOT_ID                 varchar(12) NULL,
    PRESENTERS_CODES            varchar(30) NULL,
    GUESTS_CODES                varchar(30) NULL,
    PRODUCERS_CODES         varchar(30) NULL,
    MODELS                      varchar(30) NULL,
    GUEST_PHONES                varchar(70) NULL,
    FREE_POSTAGE                tinyint NULL,
    DIGITAL_GUIDE_SYNOPSIS      varchar(240) NULL,
    CONSTRAINT ITV_SCRIPT_PRI PRIMARY KEY(SCRIPT_ID)
)



CREATE TABLE dbo.ITV_SCRIPT_LINE_TRN ( 
    SCRIPT_LINE_NO          numeric(7,0) NOT NULL,
    SCRIPT_ID               numeric(6,0) NOT NULL,
    PRODUCT_NO              varchar(16) NOT NULL,
    SELLING_PRICE           numeric(8,2) NOT NULL,
    POSTAGE_PACKAGING       numeric(8,2) NULL,
    SCRIPT_PRICE            numeric(8,2) NOT NULL,
    START_QTY               int DEFAULT 1 NOT NULL,
    QTY_SOLD                int NULL,
    CUMMULATIVE_AIR_TIME    numeric(8,2) NULL,
    AIR_TIME_PAID           varchar(2) DEFAULT 'N' NOT NULL,
    PLANNED_AIR_TIME        numeric(8,2) NULL,
    APPEARANCE_SEQUENCE     int NOT NULL,
    PREVIEW                 varchar(1) NULL,
    RETAIL_PRICE            numeric(8,2) NULL,
    LAST_TIME               datetime NULL,
    COMMENTS                varchar(80) NULL,
    NZ_POSTAGE_PACKAGING    numeric(8,2) NULL,
    ASIA_POSTAGE_PACKAGING  numeric(8,2) NULL,
    US_SELLING_PRICE        numeric(8,2) NULL,
    TVSN_PRICE              numeric(8,2) NULL,
    ITV_PROMPT_TO_BUY       varchar(30) NULL,
    ITV_PRODUCT_DESCRIPTION varchar(30) NULL,
    PRODUCT_STATUS          varchar(1) NULL,
    COLOUR_SIZE_QTY         int NULL,
    PREVIOUS_PRICE          numeric(8,2) NULL,
    L_PRICES                varchar(64) NULL,
    STRAP_LEVEL_ONE         varchar(128) NULL,
    STRAP_LEVEL_TWO         varchar(128) NULL,
    L_COLOUR                varchar(3) NULL,
    UPDATE_USER             varchar(30) DEFAULT user NULL,
    UPDATE_DATETIME         datetime DEFAULT GETDATE() NULL,
    STRAP_LEVEL_ONE_DESC    varchar(128) NULL,
    STRAP_LEVEL_TWO_DESC    varchar(128) NULL,
    L_COLOUR_STRAP_L1       varchar(16) NULL,
    L_COLOUR_STRAP_L2       varchar(16) NULL,
    STRAP_LEVEL_THREE       varchar(128) NULL,
    STRAP_LEVEL_THREE_DESC  varchar(128) NULL,
    L_COLOUR_STRAP_L3       varchar(16) NULL,
    DELAY_SALE_FLAG         tinyint DEFAULT 0 NULL,
    POTENTIAL_SALES         numeric(10,2) NULL,
    POTENTIAL_MARGIN        numeric(10,2) NULL,
    L_PRICES_CODES          numeric(10,2) NULL,
    CONSTRAINT ITV_SCRIPT_LINE_PRI PRIMARY KEY(SCRIPT_LINE_NO)
)
ALTER TABLE dbo.ITV_SCRIPT_LINE_TRN
    ADD CONSTRAINT FK_ITV_SCRIPT_LINE_SCRIPT
    FOREIGN KEY(SCRIPT_ID)
    REFERENCES dbo.ITV_SCRIPT_TRN(SCRIPT_ID)
GO
Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 29-Oct-2007 11:47:32   

The query trace shows: Parameter: @ScriptId1 : VarNumeric. Length: 6. Precision: 6. Scale: 0. Direction: Input. Value: binary lob.

so there's something wrong with a decimal/numeric value being send as a parameter, as it appears to become byte[], which is not correct.

Will look into it.

Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 29-Oct-2007 11:56:14   

Ok thanks, goodluck.

FYI Another "gotcha" with defining Sybase SQL parameters for varchar fields was to specify them as DbType.AnsiString NOT DbType.String - big performance difference.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 29-Oct-2007 11:59:24   

Jamanga wrote:

Ok thanks, goodluck.

FYI Another "gotcha" with defining Sybase SQL parameters for varchar fields was to specify them as DbType.AnsiString NOT DbType.String - big performance difference.

We pass AseDbType values to the parameter's CTor, so that should be OK (unless the provider's internal conversion table maps to DbType.String).

(edit) VarNumeric types are seen as 'binary lob' values by the tracer so the trace line shows correct trace info. It still can be that the problem is related to the type send to the db, we're investigating it.

(edit). the provider maps VarChar to AnsiString, so that should be ok.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 29-Oct-2007 13:01:40   

I can't reproduce it:


[Test]
public void GetEntityWithDecimalID()
{
    // first insert the entity
    DecTestEntity toSave = new DecTestEntity();
    try
    {
        toSave.Id = 1.0M;
        toSave.Name = "1.0M";
        Assert.IsTrue(toSave.Save());

        // load it back
        DecTestEntity toLoad = new DecTestEntity(1.0M);
        Assert.IsFalse(toLoad.IsNew);
        Assert.AreEqual(1.0M, toLoad.Id);
        Assert.AreEqual("1.0M", toLoad.Name);
    }
    finally
    {
        toSave.Delete();
    }
}

works OK.

so: 1) do you have any authorizers present for this entity? 2) you're sure the db you connect to is the correct one and sybivr.dbo.ITV_SCRIPT_TRN has indeed the record you specified in the C# code?

The parameter is send as VarNumeric (dbType).

Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO TestDB.dbo.DecTest (ID, Name) VALUES (@Id, @Name) Parameter: @Id : VarNumeric. Length: 6. Precision: 6. Scale: 0. Direction: Input. Value: 1. Parameter: @Name : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "1.0M".

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT TestDB.dbo.DecTest.ID AS Id, TestDB.dbo.DecTest.Name FROM TestDB.dbo.DecTest WHERE ( TestDB.dbo.DecTest.ID = @Id1) Parameter: @Id1 : VarNumeric. Length: 6. Precision: 6. Scale: 0. Direction: Input. Value: 1,0.

Method Exit: CreateSelectDQ Method Enter: CreateDeleteDQ(6) Method Enter: CreateSingleTargetDeleteDQ(3) Generated Sql query: Query: DELETE FROM TestDB.dbo.DecTest WHERE ( ( TestDB.dbo.DecTest.ID = @Id1)) Parameter: @Id1 : VarNumeric. Length: 6. Precision: 6. Scale: 0. Direction: Input. Value: 1.

(I've adjusted the tracer so it now shows the value for varnumeric parameters, but that's not of any influence on your query).

Btw: decimal dTestKey = 39534; sets dTestKey to the INT 39534. A conversion takes place below the surface.

(edit) I now notice that the parameter values for insert and select differ in .NET type apparently, according to the tracer! (I've dutch numeric settings, hence the ',' in 1,0). Looking into this (the parameters are created using the same routine, the string producer routine is the same... so it should show 1,0 in 3 queries not just with select... )

(edit) very strange... all three times the value is 1 (decimal), no difference between them whatsoever but when I call Value.ToString() on the parameter for the select I get 1,0 and with the others two queries (insert/delete) I get 1 confused


?((IDbDataParameter)this.Parameters[0]).Value.Equals(1.0M)
true
?((IDbDataParameter)this.Parameters[0]).Value.ToString()
"1"

.

.NET weirdness I guess.

Nevertheless... I don't know what causes the problem with your simple query...

Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 31-Oct-2007 00:47:11   

Hi Otis

Sorry for the delay in responding. That's good that you are using the actual AseDbTypes, I agree if the sybase driver doesn't handle these conversions correctly then we have even bigger problems.. simple_smile

Otis wrote:

I can't reproduce it:


[Test]
public void GetEntityWithDecimalID()
{
    // first insert the entity
    DecTestEntity toSave = new DecTestEntity();
    try
    {
        toSave.Id = 1.0M;
        toSave.Name = "1.0M";
        Assert.IsTrue(toSave.Save());

        // load it back
        DecTestEntity toLoad = new DecTestEntity(1.0M);
        Assert.IsFalse(toLoad.IsNew);
        Assert.AreEqual(1.0M, toLoad.Id);
        Assert.AreEqual("1.0M", toLoad.Name);
    }
    finally
    {
        toSave.Delete();
    }
}

works OK.

so: 1) do you have any authorizers present for this entity? 2) you're sure the db you connect to is the correct one and sybivr.dbo.ITV_SCRIPT_TRN has indeed the record you specified in the C# code?

  1. No Authorisers. The user has full db privileges. I can access these records using the same ADO.NET driver + statement generated by LLBLGen using either the AseCommand OR my generic IDbCommand objects directly within C# - so getting returning the data from the db should not be a problem..?
  2. Yes smile . It would actually be impossible for LLBLGen to have loaded the collection entity objects (ScriptLine) otherwise, since there is a foreign key relationship with the key's owner (ScriptId). To be clear, the collection entity object within the Script entity (myScript.ScriptLine) DOES create instantiated ScriptLineEntity objects with their properties correctly set (4 ScriptLineEntitys are created in my case). It the single entity owning object (myScript) which does not. I then tried to create a new single entity using the first one using the returned decimal key, eg:

ScriptLineEntity test2 = new ScriptLineEntity(myScript.ScriptLine[0].ScriptLineNo);

This also did NOT set any values within the ScriptLineEntity either - yet this row (PK value: 510389M) definitely exists because was instantiated into the LLBLGen collection<ScriptLineEntity> object list the line before.... Is there a difference in the way elements are created and mapped for a collection object perhaps?

I also tried testing a new very simple 4 property entity with no relations and an INT PK and it had the same symptoms - i.e. the query can be run fine elsewhere, however the entity does not get populated.

Otis wrote:

The parameter is send as VarNumeric (dbType).

Yes, VarNumeric is correct, with a precision of 6. This should be all that is needed.. I am starting to think that the issue is not datatype related, because the collection container used this same type + value, and DOES get loaded.

Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO TestDB.dbo.DecTest (ID, Name) VALUES (@Id, @Name) Parameter: @Id : VarNumeric. Length: 6. Precision: 6. Scale: 0. Direction: Input. Value: 1. Parameter: @Name : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "1.0M".

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT TestDB.dbo.DecTest.ID AS Id, TestDB.dbo.DecTest.Name FROM TestDB.dbo.DecTest WHERE ( TestDB.dbo.DecTest.ID = @Id1) Parameter: @Id1 : VarNumeric. Length: 6. Precision: 6. Scale: 0. Direction: Input. Value: 1,0.

Method Exit: CreateSelectDQ Method Enter: CreateDeleteDQ(6) Method Enter: CreateSingleTargetDeleteDQ(3) Generated Sql query: Query: DELETE FROM TestDB.dbo.DecTest WHERE ( ( TestDB.dbo.DecTest.ID = @Id1)) Parameter: @Id1 : VarNumeric. Length: 6. Precision: 6. Scale: 0. Direction: Input. Value: 1.

(I've adjusted the tracer so it now shows the value for varnumeric parameters, but that's not of any influence on your query).

Btw: decimal dTestKey = 39534; sets dTestKey to the INT 39534. A conversion takes place below the surface.

(edit) I now notice that the parameter values for insert and select differ in .NET type apparently, according to the tracer! (I've dutch numeric settings, hence the ',' in 1,0). Looking into this (the parameters are created using the same routine, the string producer routine is the same... so it should show 1,0 in 3 queries not just with select... )

(edit) very strange... all three times the value is 1 (decimal), no difference between them whatsoever but when I call Value.ToString() on the parameter for the select I get 1,0 and with the others two queries (insert/delete) I get 1 confused


?((IDbDataParameter)this.Parameters[0]).Value.Equals(1.0M)
true
?((IDbDataParameter)this.Parameters[0]).Value.ToString()
"1"

.

.NET weirdness I guess.

Nevertheless... I don't know what causes the problem with your simple query...

I hear you... confused do you have a copy of the same ADO.NET driver by any chance 1.15.186.0? (runtime: v1.1.4322) Is there any difference in the demo version against the code you are testing against?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 31-Oct-2007 10:32:33   

Jamanga wrote:

  1. No Authorisers. The user has full db privileges. I can access these records using the same ADO.NET driver + statement generated by LLBLGen using either the AseCommand OR my generic IDbCommand objects directly within C# - so getting returning the data from the db should not be a problem..?

Authorizers as in: LLBLGen Pro authorizers, which could deny you load an entity. But as you don't use them, it's not the cause. simple_smile

  1. Yes smile . It would actually be impossible for LLBLGen to have loaded the collection entity objects (ScriptLine) otherwise, since there is a foreign key relationship with the key's owner (ScriptId). To be clear, the collection entity object within the Script entity (myScript.ScriptLine) DOES create instantiated ScriptLineEntity objects with their properties correctly set (4 ScriptLineEntitys are created in my case). It the single entity owning object (myScript) which does not. I then tried to create a new single entity using the first one using the returned decimal key, eg:

ScriptLineEntity test2 = new ScriptLineEntity(myScript.ScriptLine[0].ScriptLineNo);

This also did NOT set any values within the ScriptLineEntity either - yet this row (PK value: 510389M) definitely exists because was instantiated into the LLBLGen collection<ScriptLineEntity> object list the line before.... Is there a difference in the way elements are created and mapped for a collection object perhaps?

it uses a different routine, but it shouldn't mean it doesn't work, as the routine which loads data into the entity is the same.

Could you try to do the following for me, with the code you posted above? - place a breakpoint in ScriptLineEntityBase.cs, in the method: private bool Fetch(System.Decimal id, IPrefetchPath prefetchPathToUse, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) - run the testcode you have above. You should end up in that routine. Step over the lines. On the last line, the return statement, you should see that the field objects in the base.Fields object should be filled with values.

I also tried testing a new very simple 4 property entity with no relations and an INT PK and it had the same symptoms - i.e. the query can be run fine elsewhere, however the entity does not get populated.

Very strange. The code executed isn't sybase specific at all...

(snip) I hear you... confused do you have a copy of the same ADO.NET driver by any chance 1.15.186.0? (runtime: v1.1.4322) Is there any difference in the demo version against the code you are testing against?

We use 1.15.50 here. As we use the developer edition, we don't have a contract with sybase for updated versions of their provider. I can't find a download link for the updated version btw, but I don't think that's really important, as it should work with the newer one.

Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 31-Oct-2007 11:45:17   

Thanks for the update Otis. I'm home now (9:39pm Australian time) and will let you know tomorrow morning what is stored in this Fields property.

I think I remember stepping through the Fetch(System.Decimal id, IPrefetchPath prefetchPathToUse, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) method and noticed all parameters except the id were null.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 31-Oct-2007 11:47:19   

Jamanga wrote:

Thanks for the update Otis. I'm home now (9:39pm Australian time) and will let you know tomorrow morning what is stored in this Fields property.

I think I remember stepping through the Fetch(System.Decimal id, IPrefetchPath prefetchPathToUse, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) method and noticed all parameters except the id were null.

Yes, that's true, they're all null, as you're using the CTor which accepts only the PK value simple_smile

Thanks for testing this out.

Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 31-Oct-2007 23:50:29   

Hi Otis

I wrote the following function to examine the entity objects

        public static string EntityToString(CommonEntityBase anEntity) {
            StringBuilder sb = new StringBuilder();
            sb.Append("LLBL Entity: " + anEntity.LLBLGenProEntityName + "|");
            sb.Append("IsNew: " + anEntity.IsNew + "|");
            sb.Append("IsDirty: " + anEntity.IsDirty + "|");
            if (anEntity.Fields != null) {
                short iFieldPos = 0;
                sb.AppendFormat("Field ({0}) Details, State: {1}|",anEntity.Fields.Count, anEntity.Fields.State);
                foreach (IEntityField thisField in anEntity.Fields) {
                    iFieldPos++;
                    sb.AppendFormat("{0}. {1} Val:{2}, DbVal:{3}|", iFieldPos, thisField.Name, thisField.CurrentValue, thisField.DbValue);
                }
            }
            return sb.ToString();
        }

For the following call:

ScriptLineEntity test2 = new ScriptLineEntity(myScript.ScriptLine[0].ScriptLineNo);

where ScriptLineNo = 510389M

ScriptLineEntity.cs -> Fetch(System.Decimal scriptLineNo, IPrefetchPath prefetchPathToUse, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields)

..at start:

LLBL Entity: ScriptLineEntity IsNew: False IsDirty: False Field (42) Details, State: New 1. ScriptLineNo Val:, DbVal: 2. ScriptId Val:, DbVal: ... rest the same

..after fetch, just before return (base.Fields.State == EntityState.Fetched); i.e. before IsNew property is set by InitClassFetch()

LLBL Entity: ScriptLineEntity IsNew: False IsDirty: False Field (42) Details, State: New 1. ScriptLineNo Val:510389, DbVal: 2. ScriptId Val:, DbVal: ... rest the same

I also ran on the myScript.ScriptLine[0] i.e. one of the container entities that got returned and has the SAME key value, and got the following:

LLBL Entity: ScriptLineEntity
IsNew: False
IsDirty: False
Field (42) Details, State: Fetched
1. ScriptLineNo Val:510389, DbVal:510389
2. ScriptId Val:39534, DbVal:39534
3. ProductNo Val:055664, DbVal:055664
4. SellingPrice Val:285, DbVal:285
5. PostagePackaging Val:18.9, DbVal:18.9
6. ScriptPrice Val:285, DbVal:285
7. StartQty Val:0, DbVal:0
8. QtySold Val:, DbVal:
9. CummulativeAirTime Val:, DbVal:
10. AirTimePaid Val:B, DbVal:B
11. PlannedAirTime Val:0.05, DbVal:0.05
12. AppearanceSequence Val:1, DbVal:1
13. Preview Val:, DbVal:
14. RetailPrice Val:, DbVal:
15. LastTime Val:, DbVal:
16. Comments Val:PP 3 x $95., DbVal:PP 3 x $95.
17. NzPostagePackaging Val:, DbVal:
18. AsiaPostagePackaging Val:0, DbVal:0
19. UsSellingPrice Val:, DbVal:
20. TvsnPrice Val:285, DbVal:285
21. ItvPromptToBuy Val:, DbVal:
22. ItvProductDescription Val:, DbVal:
23. ProductStatus Val:P, DbVal:P
24. ColourSizeQty Val:, DbVal:
25. PreviousPrice Val:, DbVal:
26. LPrices Val:, DbVal:
27. StrapLevelOne Val:, DbVal:
28. StrapLevelTwo Val:, DbVal:
29. LColour Val:, DbVal:
30. UpdateUser Val:dbo, DbVal:dbo
31. UpdateDatetime Val:19/10/2007 10:37:16 AM, DbVal:19/10/2007 10:37:16 AM
32. StrapLevelOneDesc Val:, DbVal:
33. StrapLevelTwoDesc Val:, DbVal:
34. LColourStrapL1 Val:, DbVal:
35. LColourStrapL2 Val:, DbVal:
36. StrapLevelThree Val:, DbVal:
37. StrapLevelThreeDesc Val:, DbVal:
38. LColourStrapL3 Val:, DbVal:
39. DelaySaleFlag Val:, DbVal:
40. PotentialSales Val:, DbVal:
41. PotentialMargin Val:, DbVal:
42. LPricesCodes Val:, DbVal:

Hope this helps.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 01-Nov-2007 12:21:13   

I've no idea why it doesn't work. The code used to store the data in an entity is generic, i.e. used by all databases, and if it would contain a bug, everyone would have a problem with this.

The thing which is different with our tests is that we use an older provider (v1.1.5.50) and we also use a newer ASE version (v15). It could be that with 1 row, the datareader of sybase gives up and doesn't return the row properly, at least in the version you're using, however we haven't heard any complaints about this same issue...

IF the datareader gives up with 1 row, it also should be a problem with this code: ScriptLineCollection scriptLines = new ScriptLineCollection(); scriptLines.GetMulti(ScriptLineFields.ScriptLineNo==510389.0M);

that should then also return 0 rows, while fetching multiple rows should fetch rows. Correct? Sorry to put you through all this pain, however as we can't reproduce it ourselves, it's a wild guess what could be wrong. disappointed

Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 01-Nov-2007 13:39:15   

Hi Otis,

Thanks for the response, I'll check this out tomorrow and let you know.

It is confusing isn't it? Particularly because I've tested this 1.15+ driver using my C# classes to return a single row using only PK, and didn't experience a problem.. I imagine it must be annoying for you too.

It may be that our different combination of db versions + driver causes this issue - I wish I could give you access to this latest driver I am using if you are interested to check this? I can check with a Sybase representative to see if this is possible tomorrow morning (my time?) wink

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 01-Nov-2007 16:31:58   

That would be great simple_smile . I figured that otherwise a full ASE developer edition download also might contain the provider, so if it doesn't turn out to be possible, I'll try that instead. Thanks for the effort. simple_smile

Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 01-Nov-2007 23:03:40   

I tried:

ScriptLineCollection scriptLines = new ScriptLineCollection();
scriptLines.GetMulti(ScriptLineFields.ScriptLineNo == 510389.0M);

.. and it returned 1 row wink . Similarly:

decimal dTestId = 39534M;
ScriptEntity myScript = new ScriptEntity(dTestId);

ScriptCollection myScriptColl = new ScriptCollection();
myScriptColl.GetMulti(ScriptFields.ScriptId == dTestId);

myScript is unpopulated, myScriptColl[0] contains the Db values.

I've sent a mail to Sybase for permission to pass on the drivers. Hopefully they will respond soon, where should I send them?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 02-Nov-2007 12:23:09   

They should provide a download link for their client, so we can link to that for people who want to get the latest provider. The client itself is pretty big I think, (200MB if not more)

Hmm, as that code works, I have no idea why the individual pk fetch code doesn't. confused

Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 05-Nov-2007 05:12:55   

Hi Otis,

Bad news, Sybase finally got back to me and the ADO.NET driver is only available to customers who have purchased their software... or within the SDK (http://www.sybase.com/products/allproductsa-z/softwaredeveloperkit) which guess what? Costs $2000 AUD.. cry

Unless, that is, you can get in here: http://downloads.sybase.com and download SDK 15.0 ESD #10 (EBF 14847 or higher).

Assuming you can't, I'm not sure where to go from here - I was really hoping to rustle up a quick application for my managers before the end of this week using your framework because it really does impress me.

Have we run out of steps? Is there something else I could try?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 05-Nov-2007 11:03:50   

I'll download the developer edition again, it must contain the client I pressume. I'll do some tests today to see if it's the provider wink

(edit)Well, the EBF's do enlist the bug I found with their TOP and OR query processing (you do wonder why on earth they still have problems with simple queries at version 15 (!) sunglasses ), but no sign of any changes in the client. WHen I select OpenClient, I have to call technical support...

I'll try to download the full installer again. Oh no, it lets me register again... rage

I've managed to get hold of the latest SDK download EBF, which happens to be public for registered users. Gee, why did Sybase lose the interest of developers and 3rd party toolmakers, one wonders...

(edit). Ok, I installed that 111MB large EBF. I now have 2 ADO.NET dlls (no docs anywhere in sight), one build against .NET 1.1 and one build against .net 2.0 (Sybase.AdoNet2.AseClient.dll ). The funny thing is that in this dll, Sybase.AdoNet2.AseClient.dll, there are TWO namespaces, Sybase.Data.AseClient and Sybase.Data.AseClient1, both have an AseCommand class for example. frowning

Anyway, I'll use the original one, Sybase.Data.AseClient, which is now the same version as yours. I'll do the tests again.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 05-Nov-2007 12:28:43   

Well, this is what happens: - the query is correctly generated (when I copy it to Sybase Central's interactive SQL it works and returns a row) - the command is executed, an AseDataReader is returned - however, AseDataReader.Read() returns FALSE, so there's no row apparently. There is a fieldlist, but no row, so LLBLGen Pro exists from that routine, as no data can be read.

The reader isn't closed, the connection isn't closed (both are still open).

I'll now uninstall everything and re-install AseClient and re-run the tests to see what it returns with the older version of the ado.net provider.

Btw, this is the statement executed: dataSource = command.ExecuteReader(CommandBehavior.SingleRow);

dataSource is of type IDataReader.

WIth a collection fetch, this isn't the same, as the behavior is then Default, and not SingleRow. I think this is the main difference.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 05-Nov-2007 12:55:19   

As expected, rolling back to the ADO.NET provider 1.15.50, everything works again. All unittests pass.

So, long story short: Sybase has a bug in their ado.net provider. Sad thing is, according to the fixlist of 1.15.186, there are a few nasty bugs in 1.15.50 as well... (like not working pooling etc.).

Very strange though. Could you, once again, try the test directly with their ado.net code and use SingleRow as the datareader behavior command with command.ExecuteReader() ? Thanks.

Jamanga
User
Posts: 91
Joined: 21-Jul-2006
# Posted on: 05-Nov-2007 13:07:50   

Thanks for you efforts Frans

Wow that must be frustrating.. good detective work getting those drivers. sunglasses

I'll check the DataReader out in about 10 hours and let you know..

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 05-Nov-2007 13:25:30   

Jamanga wrote:

Thanks for you efforts Frans

Wow that must be frustrating.. good detective work getting those drivers. sunglasses

I'll check the DataReader out in about 10 hours and let you know..

Awesome, thanks for the effort! simple_smile