how to store byte() array into sql server image datetype using ORM?

11 posts, 0 answers
  1. gaurav
    gaurav avatar
    3 posts
    Member since:
    Jun 2009

    Posted 01 Jun 2009 Link to this post

    hi
    i am trying to implementing personalization into my asp.net application.

    my database table have column name "PageSettings" which have image datatype.

    i get byte() array from page to store into "PageSettings" column (Image data type).

    it work fine when i use SQL command to insert value , but when i use ORM then it give me error.

    in App.code  ORM have datatype "LONGVARBINARY" for sql image data type


    Please help me.
    Regards Gaurav
  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 04 Jun 2009 Link to this post

    Hi gaurav,
    we tried to insert a byte array into our database in field of type image. Everything worked fine for us. What I can advice you is to check wheather you are indeed trying to insert byte array (byte[]). Here is the mapping of our column:
     <field name="photo"
                  <extension key="db-column"
                    <extension key="db-type" value="LONGVARBINARY" /> 
                    <extension key="db-column-name" value="Photo" /> 
                  </extension> 
                </field> 
    private byte[] photo; 
            [Telerik.OpenAccess.FieldAlias("photo")] 
            public byte[] Photo 
            { 
                get { return photo; } 
                set { this.photo = value; } 
            } 
    if you face any difficulties can you please let us know the exact way you retrieve the byte array and the way you save it into the database.

    Best wishes,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. DevCraft banner
  4. gaurav
    gaurav avatar
    3 posts
    Member since:
    Jun 2009

    Posted 08 Jun 2009 Link to this post

    i try to make changes as you reply me, but still it gives me following errors.

    Telerik.OpenAccess.RT.sql.SQLException: Type not supported for setObject: -4
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.setObject(Int32 parameterIndex, Object x, Int32 targetSqlType, Int32 scale)
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.Telerik.OpenAccess.RT.sql.PreparedStatement.setObject(Int32 parameterIndex, Object x, Int32 targetSqlType)
       at OpenAccessRuntime.Relational.RelationalQueryResult.createFetchResult(Connection conParam, SqlDriver sqlDriver, Boolean scrollableParam, FetchSpec fetchSpec, RelationalCompiledQuery relationalCompiledQuery, RelationalStorageManager storageManager, Object[] paramVals, Int32 fetchSize, Int32 maxRows)
  5. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 09 Jun 2009 Link to this post

    Hello gaurav,
    can you please provide us the method that you use to retrieve the byte array and the method that you use to store it. We believe that the logic in one of those methods must be wrong because the operations you require are perfectly possible with our product and we have done them numerous times.

    Kind regards,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  6. gaurav
    gaurav avatar
    3 posts
    Member since:
    Jun 2009

    Posted 09 Jun 2009 Link to this post

    hi!
    let me telling you more detail.
    i am trying to inherit class System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider and overrides it's following methods

    Step 1
    ----------Overrides Function code---------------
    Protected Overrides Sub SavePersonalizationBlob(ByVal webPartManager As System.Web.UI.WebControls.WebParts.WebPartManager, ByVal path As String, ByVal userName As String, ByVal dataBlob() As Byte)

    '''' Call function which use ORM to save dataBlob()  into database

    end sub

    --------End Overrides function code

    Step 2
    -------Store procedure code-----------------------------

    Public Shared Function aspnetPersonalizationAllUsersSetPageSettings(ByVal scope As IObjectScope, ByVal applicationName As String, ByVal path As String, ByVal pageSettings As Byte(), ByVal currentTimeUtc As Date) As IQueryResult
            Dim query As IQuery = scope.GetSqlQuery("aspnet_PersonalizationAllUsers_SetPageSettings ?,?,?,?", Nothing, "VARCHAR ApplicationName,VARCHAR Path,LONGVARBINARY PageSettings,TIMESTAMP CurrentTimeUtc")
            Dim res As IQueryResult = query.Execute(New Object() {applicationName, path, pageSettings, currentTimeUtc})
            Dim count As Integer = res.Count 'executes the query

            Return res
        End Function

    -------- End Store procedure code




    Step 3

    ------------Following is the app.config file code which is generated by ORM

    <class name="AspnetPersonalizationAllUser">
                <field name="_pathId">
                  <extension key="db-column">
                    <extension key="db-type" value="GUID" />
                    <extension key="db-column-name" value="PathId" />
                  </extension>
                </field>
                <field name="_lastUpdatedDate">
                  <extension key="db-column">
                    <extension key="db-type" value="TIMESTAMP" />
                    <extension key="db-column-name" value="LastUpdatedDate" />
                  </extension>
                </field>
                <field name="_pageSettings">
                  <extension key="db-column">
                    <extension key="db-type" value="LONGVARBINARY" />
                    <extension key="db-column-name" value="PageSettings" />
                    <extension key="db-length" value="2147483647" />
                  </extension>
                </field>

                <field name="_aspnetPath">
                  <extension key="db-column">
                    <extension key="db-type" value="GUID" />
                    <extension key="db-column-name" value="PathId" />
                  </extension>
                </field>
                <extension key="db-do-not-create-table" value="true" />
                <extension key="db-table-name" value="aspnet_PersonalizationAllUsers" />
                <extension key="db-optimistic-locking" value="changed" />
              </class>


    -----------------------End App.Config File code---------------------------------

    Step 4
    -------------------------ReversMapping file code------------------------

    <procedure name="'aspnet_PersonalizationAllUsers_SetPageSettings'" method="aspnetPersonalizationAllUsersSetPageSettings">
                <parameter name="@ApplicationName" mode="IN" adoType="12" sqlType="nvarchar" length="256" scale="-1" />
                <parameter name="@Path" mode="IN" adoType="12" sqlType="nvarchar" length="256" scale="-1" />
                <parameter name="@PageSettings" mode="IN" adoType="2004" sqlType="image" length="2147483647" scale="-1" />
                <parameter name="@CurrentTimeUtc" mode="IN" adoType="93" sqlType="datetime" length="-1" scale="-1" />
            </procedure>

    ------End ReversMapping file code-------------------
  7. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 11 Jun 2009 Link to this post

    Hello gaurav,
    currently our GetSqlQuery method cannot operate properly with parameters of type LongVarBinary or VarBinary, thus making it impossible for the stored procedure to work as expected.  We are aware of this problem and we are working on fixing it. As a work around you should try and use Linq to achieve your goal.
    Greetings,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  8. Andreas Kaech
    Andreas Kaech avatar
    121 posts
    Member since:
    Sep 2004

    Posted 25 Jun 2009 Link to this post

    Hi Telerik,
    I've a strange behaviour when calling this Upload of an Binary File to "sql server":

                Guid newGuid = Guid.NewGuid();     
                var attachement1 = new Attachement1     
                                       {     
                                           Id = newGuid,     
                                           FileContent = bytes,     
                                           FileSize = iSize,     
                                           FileName = sFilename,     
                                           UploadDate = DateTime.Now,     
                                           Titel = "TEST"    
                                       };     
                scope.Transaction.Begin();     
                scope.Add(attachement1);     
                scope.Transaction.Commit();    
     
    ORM inserts before the Guid some digits(!) and runs in an error: "Insert of '123217374-23cef24e-6f17-461b-bd5e-44226a320f6f' failed: Telerik.OpenAccess.RT.sql.SQLException"
    In the class "Attachement1.cs", all looks Ok.

    Best regards,
    Andreas

  9. Andreas Kaech
    Andreas Kaech avatar
    121 posts
    Member since:
    Sep 2004

    Posted 30 Jun 2009 Link to this post

    Hi Telerik,
    one supplement to the last thread:
    The error (adding some digits to the Guid) occurs only, when I want to insert and the keyfield has a Guid value.
    I hope this helps.

    Best regards,
    Andreas
  10. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 02 Jul 2009 Link to this post

    Hello Andreas Kaech,
    this guid that you are seeing represents the transaction it that failed rather than the ID that is being inserted. You see this because for some reason your transaction could not be completed. Can you please provide us with the complete exception message and the stack trace. Judging by what you provided us so far it seems that you are trying to insert incompatible type into your table.

    Kind regards,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  11. Andreas Kaech
    Andreas Kaech avatar
    121 posts
    Member since:
    Sep 2004

    Posted 04 Jul 2009 Link to this post

    Hello Petar,
    two hints:
    1. When debugging I recognized the following:
    The Guid of the "to insert" Table-Id is '92622714-a526-4e7d-b944-2bf622e5c218' and the Error-Id is '685553526-92622714-a526-4e7d-b944-2bf622e5c218'. When I remove the primary key of this Table-Id for testing purpose, the transaction is Ok.
    2. In the Insert-String of the Error-Log, the Table-Id is missing:
    INSERT INTO [LebensplanSkizzieren] ([LastUpdateDate], [TextArea1], [TextArea2], [TextArea3], [UserId]) VALUES (?, ?, ?, ?, ?)

    Perhaps, there is a "mismatch" between ORM-transaction-Id and a "primary keyed" Table-Id with Data Type "uniqueidentifier".
    Here the complete Error-String (sorry, it's german):

    Insert of '685553526-92622714-a526-4e7d-b944-2bf622e5c218' failed: Telerik.OpenAccess.RT.sql.SQLException: Der Wert NULL kann in die 'Id'-Spalte, 'ePf.dbo.LebensplanSkizzieren'-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei INSERT.  
    Die Anweisung wurde beendet.  
       bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()  
       bei OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()  
       bei OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)  
    INSERT INTO [LebensplanSkizzieren] ([LastUpdateDate], [TextArea1], [TextArea2], [TextArea3], [UserId]) VALUES (?, ?, ?, ?, ?)  
    select scope_identity()  
    (set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: Der Wert NULL kann in die 'Id'-Spalte, 'ePf.dbo.LebensplanSkizzieren'-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei INSERT.  
    Die Anweisung wurde beendet.  
       bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()  
       bei OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()  
       bei OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes) 

    Best regards,
    Andreas
  12. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 07 Jul 2009 Link to this post

    Hi Andreas Kaech,
    we were not able to reproduce this behavior here locally. We believe that there is some mismatch in your configuration files. It seems that our ORM expects that the guid will be generated by the sql server and omits it in the query that is used for the insert. In order to find the reason for this odd behavior we will have to ask you to send us your configuration files if it is possible for you. This will greatly help us to locate the problem.

    Sincerely yours,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
Back to Top
DevCraft banner