| Author |
Topic |
|
GenericUserName
Starting Member
4 Posts |
 Posted - 2005-11-28 : 12:27:08
|
We are currently transitioning from an old DOS system using binary file storage to a windows-based option using relational database storage. Our database model is complete, and we have a working client, but the problem is that we need to be able to take the new relational data and put it back in the old file format for processing. We have developed a translator that uses .NET to compile the file together from database records, but it's sluggish at best, and when we deal with large files this utility becomes somewhat useless. I would like to accomplish a majority of the file compilation from within TSQL. I know it has support for varbinary and blob, and the former cannot be used because we may be dealing with upwards of 20,000 bytes, sometimes even 100,000. I would like to compile one massive blob in the binary file format we use from a stored procedure that I can grab from within .NET and write to a file and perform some other operations.A few questions:1. I know Blob cannot be declared as a variable in TSQL, but I can still use it within a temporary table, correct?2. Is there a size limit on the Blob field? I've heard that 8000 was some magic number for any data size in TSQL, does that apply to BLOB too?3. If this method will work, what are some functions I need to know in order to append data to the blob? I only need to append to the end, I'll be building the file in the order that is needed. Is there a CONCAT that works with BLOB?4. Is there anything that you think would hinder this hack from working? I'm new to TSQL and don't know if there is common knowledge or bad practice stuff that would keep it from being a good solution.5. Is there a better solution to this problem than trying to do everything within TSQL? Should I not fool with building a blob in tSQL, and just abstract the heavier parts of the data into a procedure that returns a varbinary?Appreciate any guidance on this.  |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-11-28 : 12:48:30
|
| Well, you may want to look into the textcopy.exe, read about it on BOL.________________________________________________Drinking German Beer... fun.Listening to an accordian player play ACDC...priceless |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-28 : 13:40:27
|
| 1) yes2) about 2G I think. 8000 is the max size for a varcharhave a look athttp://www.mindsdoor.net/SQLTsql/InsertTextData.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-28 : 13:41:39
|
| Check out READTEXT, WRITETEXT and other commands that are related which will be displayed when you search online. SQL Server offers two "blob"ish datatypes IMAGE, TEXT both of which can be manipulated using these functions. As you'll read both IMAGE and TEXT fields are actually stored outside of the table itself and only a pointer to the data is stored inside the table. The documentation whill show you how to grab the pointer, then manipulate the data based on offsets. |
|
|
|
GenericUserName
Starting Member
4 Posts |
Posted - 2005-11-28 : 13:59:49
|
| Would it make more sense for me to actually write the file from within the stored procedure, i.e. remove temporary tables and blobs? And then pass path of the created file to the main application?If so, what is the best way to accomplish binary file writing within a stored procedure? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-28 : 14:04:07
|
| Why are you holding the data in a database? Could you hold it in files and keep the path in the database?For creating a file from an SP look at bcp.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
GenericUserName
Starting Member
4 Posts |
Posted - 2005-11-28 : 14:40:03
|
| Unless I'm missing something, I don't see how I could use BCP the data I need to. There's no way I could get the data into a flat format of any kind. Basically, I have a collection of floats and integers, and then some fixed length strings, packed into bytes and written to the file in random orders. Then, I have loops within the binary file, each with a constant defining the number of times it loops, and each iteration writes a new set of binary data to the file. It's a fairly complex structure as it is very scalable but rigidly defined structure-wise. I basically will have to query each value to place as I convert it to binary and write it to the file. In addition, we are tracking the process of this and will need to perform this from a .NET application, so the whole thing has to be encapsulated within that. I initially thought of passing a binary blob to the .NET application, but it seems compiling a blob may be more difficult than just writing the file from within tSQL, though Google isn't being very helpful on that subject.My main difficulty is that I don't know how to write the file. I've written text files using xp_cmdshell and redirection, but never messed with writing binary data. I don't need to be able to read it back in, just write it. So far, the only thing I see that could work would be to query each value I need, convert it to varbinary, put it in a temporary table, and call BCP to append that field to the file I want to, and when it's all done to pass that file path to the .NET app and drop the temp table. That does seem rather convoluted though, and almost as resource-intensive as the current method, but if it's the only way... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-29 : 03:14:32
|
| http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspxMadhivananFailing to plan is Planning to fail |
|
|
|
|
Working with Blob data