How to save and retrieve BLOB data with Sybase ASE 12.5

  • Thread starter Thread starter William Ryan
  • Start date Start date
W

William Ryan

Cam you tell us specifically what's giving you the problem?


Assuming you have an open connection already..

To Read
SqlCommand cmd;
SqlDataReader rdr;

int myBigColumn

Byte[] myBlob;

rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (rdr.Read())
{

myBlob = (Byte[]) rdr[0];
}

To Write it (this is from MSDN, it's not mine but I don't
think it's the same article...


using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public class EmployeeData
{
public static void Main()
{
DateTime hireDate = DateTime.Parse("5/21/99");
AddEmployee("Jones", "Mary", "Sales Representative",
hireDate, 5, "jones.bmp");
}

public static void AddEmployee(string lastName, string
firstName, string title, DateTime hireDate , int
reportsTo, string photoFilePath)
{
byte[] photo = GetPhoto(photoFilePath);

SqlConnection nwindConn = new SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial
Catalog=Northwind;");

SqlCommand addEmp = new SqlCommand("INSERT INTO
Employees (LastName, FirstName, Title, HireDate,
ReportsTo, Photo) " +
"Values(@LastName,
@FirstName, @Title, @HireDate, @ReportsTo, @Photo)",
nwindConn);

addEmp.Parameters.Add("@LastName",
SqlDbType.NVarChar, 20).Value = lastName;
addEmp.Parameters.Add("@FirstName",
SqlDbType.NVarChar, 10).Value = firstName;
addEmp.Parameters.Add("@Title",
SqlDbType.NVarChar, 30).Value = title;
addEmp.Parameters.Add("@HireDate",
SqlDbType.DateTime).Value = hireDate;
addEmp.Parameters.Add("@ReportsTo",
SqlDbType.Int).Value = reportsTo;

addEmp.Parameters.Add("@Photo", SqlDbType.Image,
photo.Length).Value = photo;

nwindConn.Open();

addEmp.ExecuteNonQuery();

nwindConn.Close();
}

public static byte[] GetPhoto(string filePath)
{
FileStream fs = new FileStream(filePath,
FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);

byte[] photo = br.ReadBytes((int)fs.Length);

br.Close();
fs.Close();

return photo;
}
}


I've used this same methodology to get it to work, and
while I avoid Blobs for the most part, it should work.

If you can isolate what isn't working, I can probably be
of more help.

Cheers,

Bill
 
Thanks for your reply,

I use Stored Procedure for all data access, but Sybase ASE
does not allow the use of text as parameter type. The
server that hosts my DB uses 8KB as the default page size,
therefore I could only use varchar(16000) as the
parameter. The 16000 limit is not enough for data storage.

I tried to follow KB309158, but OleDbCommandBuilder could
not construct the update or insert command (I receive the
following error: System.SystemException {"Dynamic SQL
generation is not supported against a SelectCommand that
does not return any base table information."}
System.SystemException)

I would like to know "how to save/retrieve text (not
image) type data on Sybase ASE 12.5 with ADO.NET".

Thanks

John Yung
-----Original Message-----
Cam you tell us specifically what's giving you the problem?


Assuming you have an open connection already..

To Read
SqlCommand cmd;
SqlDataReader rdr;

int myBigColumn

Byte[] myBlob;

rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (rdr.Read())
{

myBlob = (Byte[]) rdr[0];
}

To Write it (this is from MSDN, it's not mine but I don't
think it's the same article...


using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public class EmployeeData
{
public static void Main()
{
DateTime hireDate = DateTime.Parse("5/21/99");
AddEmployee("Jones", "Mary", "Sales Representative",
hireDate, 5, "jones.bmp");
}

public static void AddEmployee(string lastName, string
firstName, string title, DateTime hireDate , int
reportsTo, string photoFilePath)
{
byte[] photo = GetPhoto(photoFilePath);

SqlConnection nwindConn = new SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial
Catalog=Northwind;");

SqlCommand addEmp = new SqlCommand("INSERT INTO
Employees (LastName, FirstName, Title, HireDate,
ReportsTo, Photo) " +
"Values(@LastName,
@FirstName, @Title, @HireDate, @ReportsTo, @Photo)",
nwindConn);

addEmp.Parameters.Add("@LastName",
SqlDbType.NVarChar, 20).Value = lastName;
addEmp.Parameters.Add("@FirstName",
SqlDbType.NVarChar, 10).Value = firstName;
addEmp.Parameters.Add("@Title",
SqlDbType.NVarChar, 30).Value = title;
addEmp.Parameters.Add("@HireDate",
SqlDbType.DateTime).Value = hireDate;
addEmp.Parameters.Add("@ReportsTo",
SqlDbType.Int).Value = reportsTo;

addEmp.Parameters.Add("@Photo", SqlDbType.Image,
photo.Length).Value = photo;

nwindConn.Open();

addEmp.ExecuteNonQuery();

nwindConn.Close();
}

public static byte[] GetPhoto(string filePath)
{
FileStream fs = new FileStream(filePath,
FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);

byte[] photo = br.ReadBytes((int)fs.Length);

br.Close();
fs.Close();

return photo;
}
}


I've used this same methodology to get it to work, and
while I avoid Blobs for the most part, it should work.

If you can isolate what isn't working, I can probably be
of more help.

Cheers,

Bill
-----Original Message-----
Hi,

How can I save BLOB (text) data to Sybase ASE 12.5 with
ADO.NET?

I tried to follow MS Knowledge Base Article - 309158 (HOW
TO: Read and Write BLOB Data by Using ADO.NET with Visual
C# .NET), but it does not work.

Thanks

John Yung
.
Click to expand...
.
Click to expand...
 
Back
Top