Executing a stored procedure with a Blob parameter

Thread is closed for posting
6 posts, 1 answers
  1. Rohan
    Rohan avatar
    6 posts
    Member since:
    Apr 2009

    Posted 21 May 2009 Link to this post

    Hi there, I am currently using SQL2008 with Open Access,

    I have created a stored procedure that requires a timestamp parameter...

    create procedure Update(

     

    @sourceId int, @sourceVersion timestamp, @destinationId int, @destinationVersion timestamp)
    begin
    end


    When i use Open Access to generate the procedure call it creates the following...

     

    scope.GetSqlQuery(

    "Update ?,?,?,?", null, "INTEGER sourceId, BLOB sourceVersion, INTEGER destinationId, BLOB destinationVersion");

     

     

     

    when i execute the query i get the following exception

    Telerik.OpenAccess.Exceptions.DataStoreException: Telerik.OpenAccess.RT.sql.SQLException: Type not supported for setObject: 2004

    Any ideas? Is blog the correct type? Should i changes some mappings?

    Thanks

    Rohan

     

  2. Ady
    Admin
    Ady avatar
    589 posts

    Posted 25 May 2009 Link to this post

    Hello Rohan,

     I did create a stored procedure with a timestamp parameter and reverse engineered that. The wizard uses a 'BIGINT' parameter and not BLOB in the call to GetSqlQuery.

     Can you try using a BIGINT instead of BLOB.

    Regards,
    Ady
    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. Rohan
    Rohan avatar
    6 posts
    Member since:
    Apr 2009

    Posted 25 May 2009 Link to this post

    Hi Ady,

    Thanks for your response, I have just upgraded to the following version 2009.1.520.1 and your right, the c# generated by the reverse engineering tool uses BIGINT

     
    create procedure dbo.Update(@name varchar(50), @parentId int, @parentVersion timestamp)
     
    "Update ?,?,?" , "VARCHAR name,INTEGER parentId,BIGINT parentVersion"

    This time i get the following exception

    Telerik.OpenAccess.Exceptions.DataStoreException: Query failed: System.FormatException: Input string was not in a correct format.

    Thanks

    Rohan

  4. Ady
    Admin
    Ady avatar
    589 posts

    Posted 26 May 2009 Link to this post

    Hello Rohan,

    What is the .NET type of the parentVersion parameter ? You might need to convert it to the appropriate integral type if it is a string.

    Best wishes,
    Ady
    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.
  5. Rohan
    Rohan avatar
    6 posts
    Member since:
    Apr 2009

    Posted 26 May 2009 Link to this post

    Hi there Ady,

    I have a table which has a timestamp column, when i use the reverse enginering tool it identifies my column as a timestamp column and uses it for concurrency control.  At this point it doesn't create a field or public property which will allow my to inspect the version, i need access to this version so that i can send it over the wire at a later stage in the application.  Because i need access to this value i manually edited the app.config and added the additional field

    <field name="version">
     <extension key="db-column">
      <extension key="db-type" value="BIGINT" />
      <extension key="db-column-name" value="Version_Timestamp" />
     </extension>
    </field>

    with the following

    [Telerik.OpenAccess.FieldAlias("version")]
    public long Version
    {
        get { return version; }            // The version field in of type long
        set { this.version = value; }
    }

    This gave me the following exception

    Unhandled Exception: Telerik.OpenAccess.Exceptions.DataStoreException: Error reading field ConsoleApplication.Entity.version from ResultSet: System.
    InvalidCastException: Specified cast is not valid.

    I updated the app.config so that the version is a varbinary

    <field name="version">
      <extension key="db-column">
        <extension key="db-type" value="VARBINARY" />
        <extension key="db-column-name" value="Version_Timestamp" />
      </extension>
    </field>

    and my property to

    [Telerik.OpenAccess.FieldAlias("version")]
    public byte[] Version
    {
        get { return version; }                // The version field in of type byte[]
        set { this.version = value; }
    }

    This worked...

    So when i call my stored procedure i am passing in a byte array.

    If i make the call using ADO.NET i use the following parameter definition

    command.Parameters.Add("@parentVersion", SqlDbType.Timestamp).Value = parentVersion;

    It tells me that the SqlDbType.Timestamp expects

    System.Array of type System.Byte. Automatically generated binary numbers,
    // which are guaranteed to be unique within a database. timestamp is used typically
    // as a mechanism for version-stamping table rows. The storage size is 8 bytes

    Cheers

    Rohan

  6. Answer
    Ady
    Admin
    Ady avatar
    589 posts

    Posted 03 Jun 2009 Link to this post

    Hi Rohan,

     Sorry for the delayed reply.
    Thanks to your detailed explanation, I could reproduce your case. The GetSqlQuery method does not support VARBINARY parameters.
    We are working on supporting this and should be available in the near future.
    Can you achieve what you want using OQL/LINQ?

    Your Telerik points have been updated

    Sincerely yours,
    Ady
    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