[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Insert size limitaion?

Posted on 2000-02-18
13
Medium Priority
?
567 Views
Last Modified: 2012-06-22
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.


0
Comment
Question by:tateno
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 2

Expert Comment

by:jkotek
ID: 2534618
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
 
LVL 1

Expert Comment

by:albertsp
ID: 2534829
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
 

Author Comment

by:tateno
ID: 2534892
Edited text of question.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:tateno
ID: 2534930
Edited text of question.
0
 
LVL 1

Expert Comment

by:bfrancis
ID: 2535641
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
 
LVL 1

Expert Comment

by:albertsp
ID: 2535730
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
 
LVL 1

Expert Comment

by:bfrancis
ID: 2535763
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
 
LVL 1

Expert Comment

by:bfrancis
ID: 2535810
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
 

Author Comment

by:tateno
ID: 2537700
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
 

Author Comment

by:tateno
ID: 2541280
I am sorry, mistakenly I rejected : bfrancis's answer.
I don't know hot to restore it.
0
 
LVL 1

Accepted Solution

by:
bfrancis earned 400 total points
ID: 2542074
Did you find how to set the DBTEXTLIMIT connection property or do you need more help?
0
 

Author Comment

by:tateno
ID: 2542633
Thanks.
0
 

Author Comment

by:tateno
ID: 2542660
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

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes Administrators rights are not enough. These cases call for the SYSTEM account. The process in this article outlines the steps required to execute commands using the SYSTEM account.
An overview of cyber security, cyber crime, and personal protection against hackers. Includes a brief summary of the Equifax breach and why everyone should be aware of it. Other subjects include: how cyber security has failed to advance with technol…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question