Executing a stored procedure with a Blob parameter

[Rohan]

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

 

[Ady]

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.

[Rohan]

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

[Ady]

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.

[Rohan]

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

[Ady]

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.