SQL Insert size limitaion?

Hello.

I have a problem with sybase. I would like to to insert a big
 data (2 Mbytes) into a column of text type. But I failed to do
so.  But I managed to insert 1M-byte file into the table.

Is there a limit to the size of an INSERT command ?

Thanks in advance.


tatenoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkotekCommented:
Hello tateno,

There is one limit for IMAGE and TEXT datatypes.
One 'record' has to be on a single database device = maximum size is about 2GB. But if you want to manipulate with files larger than 255 bytes, you have to use the writetext and readtext commands.
Do you get some error message or is there something in server's error log?
0
albertspCommented:
It is not true that there is a limit of 255 bytes for inserting data. When you have text or image columns you should be able to insert files of 2 Mb using the INSERT command.

However: INSERT is logging your transaction. So check your transactionlog on its size.

You can use the writetext command for inserting TEXT columns. This is a nonlogged operation. This means that you should dump your database after the insert.
The database option 'select into/bulkcopy' must be enabled when using writetext.

Albert
0
tatenoAuthor Commented:
Edited text of question.
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

tatenoAuthor Commented:
Edited text of question.
0
bfrancisCommented:
Could you provide a little information about what development language (PB, VB, C++) you are using and what type version of the sybase client library you are using?  
0
albertspCommented:
Tateno

I don't see what you edited in your question. As a matter of fact I expected a comment from your side on my comment.

As said before. There is no limit on Size using the INSERT statement.

Albert
0
bfrancisCommented:
alberto,
There is actually a limit on size when using an insert statement.  It comes from the client side.  If you are using DB-Library it is the DBTEXTLIMIT connection property and if ypu are using the CT-Library it is the  CS_TEXTLIMIT connection property.
0
bfrancisCommented:
BTW jkotek the above comment is actually your answer.  If you need help with how to set this we will need some more info about the client side tools you are using.
0
tatenoAuthor Commented:
Hello.

 I appriciate everybody's quick responces.

Albert, I tried to  edit my original text after I found mistyeping in it.

I am in charge of  a project of making a web application with php 3.0 +  apache +  sybase on Solaris. According to phpinfo(), sybase client is Sybase DB-Library/10.0.4.

After I  failed to insert 2MB text file into a table.  I printed the sql excuted in the program as a text file. And I  executed the sql script  with isql, like isql -Uxx -Pxx -Sxxxxx  -i  xxxxx.sql .  I succeeded in inserting
1MB text. I did same thing about  2MB file text.
But  it failed. So I think it has nothing to do with the php program.

Is there any way to  change the size of limitation ?

0
tatenoAuthor Commented:
I am sorry, mistakenly I rejected : bfrancis's answer.
I don't know hot to restore it.
0
bfrancisCommented:
Did you find how to set the DBTEXTLIMIT connection property or do you need more help?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tatenoAuthor Commented:
Thanks.
0
tatenoAuthor Commented:
Thank you very much for every comment to my question.
Now I am thinking about using writetext statement for realize the required functions.

Tateno
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.