473,289 Members | 2,155 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

value too long error

Hi:

Whenever i try to insert the data, size of which is greater than that of column datatype size, I got the exception value too long for.....

However this was not in postgresql7.2.

Can anyone please tell me, is there any way so that i wont get this exception. Please help me as soon as possible

Thanks in advance.

Rajat.

Nov 23 '05 #1
9 2306
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Just don't try to save anything into a field that is too short to store the
value. Doesn't make too much sense, since what doesn't fit into the field
would be cut off anyways. If you need this, have your application either
restrict the input field length or cut the extensive part off before you
store it to the database.
On Friday 27 February 2004 11:00 pm, Rajat Katyal wrote:
Hi:

Whenever i try to insert the data, size of which is greater than that of
column datatype size, I got the exception value too long for.....

However this was not in postgresql7.2.

Can anyone please tell me, is there any way so that i wont get this
exception. Please help me as soon as possible

Thanks in advance.

Rajat.


- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAQEBcjqGXBvRToM4RAv2TAKC8CmB4/pJWSk7H3/RDjn38RxBM4QCeKl/O
CIf6DMxms1Y81DBd/9lHBwY=
=b4bg
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2
Ya, through application we can provide field input validation but iam working on the tool which transfers the data from some specifed database to another. Here user itself wants if value is too long for the target column thentruncates the text and insert into the target field. It was there in postgres version 7.2 but not in postgres 7.3.

At database level how can we provide a check if text size is greater than that of field size then truncate it?

Thanks and Regards,
Rajat.

----- Original Message -----
From: "Uwe C. Schroeder" <uw*@oss4u.com>
To: "Rajat Katyal" <ra****@intelesoftech.com>; <pg***********@postgresql.org>
Sent: Saturday, February 28, 2004 12:46 PM
Subject: Re: [GENERAL] value too long error

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Just don't try to save anything into a field that is too short to store the
value. Doesn't make too much sense, since what doesn't fit into the field
would be cut off anyways. If you need this, have your application either
restrict the input field length or cut the extensive part off before you
store it to the database.


On Friday 27 February 2004 11:00 pm, Rajat Katyal wrote:
Hi:

Whenever i try to insert the data, size of which is greater than that of
column datatype size, I got the exception value too long for.....

However this was not in postgresql7.2.

Can anyone please tell me, is there any way so that i wont get this
exception. Please help me as soon as possible

Thanks in advance.

Rajat.


- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAQEBcjqGXBvRToM4RAv2TAKC8CmB4/pJWSk7H3/RDjn38RxBM4QCeKl/O
CIf6DMxms1Y81DBd/9lHBwY=
=b4bg
-----END PGP SIGNATURE-----


Nov 23 '05 #3
On Saturday 28 February 2004 08:36, Rajat Katyal wrote:
Ya, through application we can provide field input validation but iam
working on the tool which transfers the data from some specifed database to
another. Here user itself wants if value is too long for the target column
then truncates the text and insert into the target field. It was there in
postgres version 7.2 but not in postgres 7.3.

At database level how can we provide a check if text size is greater than
that of field size then truncate it?


Unfortunately, you can't do this with a BEFORE INSERT trigger, since the
type-checking (which includes length) is done before the trigger will get
called.

You could however:
1. Have a duplicate table, except with unlimited varchar fields and import
into that. Once the batch is in, move it to the destination table with the
relevant substr()
2. You *should* be able to do the same, but with a before trigger that trims,
then inserts to the destination table before returning NULL.
3. You might even be able to use a view with a rule that instead trims the
relevant text fields. Not sure about this one.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4
"Rajat Katyal" <ra****@intelesoftech.com> writes:
Whenever i try to insert the data, size of which is greater than that of co=
lumn datatype size, I got the exception value too long for.....=20 However this was not in postgresql7.2.


IIRC, older versions would just silently truncate the data to the
specified column width. We concluded that that was not per spec.
7.3 and later make you do it the SQL-spec way, which is to explicitly
truncate the data. You can do that with a substring operation or by
casting, for instance
INSERT INTO foo VALUES('an overly long string'::varchar(10));

It's a tad inconsistent that explicit and implicit casts to varchar(N)
act differently, but that's what the SQL spec says to do, AFAICS.
I guess it's reasonable --- the old behavior could result in unintended
data loss.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #5
On Sat, 28 Feb 2004, Rajat Katyal wrote:
Hi:

Whenever i try to insert the data, size of which is greater than that of column datatype size, I got the exception value too long for.....

However this was not in postgresql7.2.

Can anyone please tell me, is there any way so that i wont get this exception. Please help me as soon as possible


As mentioned earlier, this is against spec (and for good reason,
databases, by default, shouldn't just toss away data that doesn't fit,
they should throw an error and prevent accidental data loss.)

That said, the easiest way to do this is to make the field a text type,
not a limited varchar, then create a before trigger that uses substring to
chop all but the first x characters and insert them.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #6
scott.marlowe wrote:
On Sat, 28 Feb 2004, Rajat Katyal wrote:
Hi:

Whenever i try to insert the data, size of which is greater than that of column datatype size, I got the exception value too long for.....

However this was not in postgresql7.2.

Can anyone please tell me, is there any way so that i wont get this exception. Please help me as soon as possible


As mentioned earlier, this is against spec (and for good reason,
databases, by default, shouldn't just toss away data that doesn't fit,
they should throw an error and prevent accidental data loss.)

That said, the easiest way to do this is to make the field a text type,
not a limited varchar, then create a before trigger that uses substring to
chop all but the first x characters and insert them.


(This just seemed like a good time to do a brain-dump)

I was thinking about this question, and the various answers.

In OO programming, the generally accepted rule is that a program shouldn't
access class values directly, but the class should have methods to set
and retrieve the data. This allows internal representations to change
without affecting the public API of the class. It also allows data
validation to occur, if needed.

I'm just wondering how far this rule of thumb could/should be extended to
databases? I mean, you could say: "Nobody does a direct INSERT, but always
calls a stored procedure that stores the result." I don't know how much
this might break the mindset of the client developer.

Anyway, it's one possible solution to the problem. But (to me) it's a
potentially new way of looking at things.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #7

Bill Moran <wm****@potentialtech.com> writes:
In OO programming, the generally accepted rule is that a program shouldn't
access class values directly, but the class should have methods to set
and retrieve the data. This allows internal representations to change
without affecting the public API of the class. It also allows data
validation to occur, if needed.
Note that this is largely a C++ rule. In C++ accessing class members is very
different and much less flexible than using class methods. Other OO languages
are not universally so hobbled.
I'm just wondering how far this rule of thumb could/should be extended to
databases? I mean, you could say: "Nobody does a direct INSERT, but always
calls a stored procedure that stores the result." I don't know how much
this might break the mindset of the client developer.


This is a popular old-school database approach. Personally I find it
incredibly annoying, but I can see its advantages as well. But to me stored
procedures just don't seem like nearly a powerful enough abstraction tool to
make them worth all the pain this approach entails.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #8
Greg Stark wrote:
Bill Moran <wm****@potentialtech.com> writes:
In OO programming, the generally accepted rule is that a program shouldn't
access class values directly, but the class should have methods to set
and retrieve the data. This allows internal representations to change
without affecting the public API of the class. It also allows data
validation to occur, if needed.


Note that this is largely a C++ rule. In C++ accessing class members is very
different and much less flexible than using class methods. Other OO languages
are not universally so hobbled.


Really? I can only assume you're referring to languages such as perl that have
ties (or equivalent capability). In that case, you're still accessing the data
through a method, it's just a more abstract abstraction. Or are you referring
to something else?
I'm just wondering how far this rule of thumb could/should be extended to
databases? I mean, you could say: "Nobody does a direct INSERT, but always
calls a stored procedure that stores the result." I don't know how much
this might break the mindset of the client developer.


This is a popular old-school database approach. Personally I find it
incredibly annoying, but I can see its advantages as well. But to me stored
procedures just don't seem like nearly a powerful enough abstraction tool to
make them worth all the pain this approach entails.


I threw it out there for the sake of discussion. I can see advantages and
disadvantages. For example, I'm working on financial software, and
_everything_ is accessed through stored procedures. This is A Good Think
(in my opinion) because the software is designed to be easily integrated
with other systems. The last thing we'd want is someone getting the wrong
answer to a financial query because they don't understand the schema. A
much more reliable way is to have them
SELECT * FROM get_monthy_payment(month, account); so we know they're getting
the correct answer.

But I _can_ see the potential PITA this can cause. I guess it depends on
the circumstance.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #9
Bill Moran <wm****@potentialtech.com> writes:
Greg Stark wrote:

Note that this is largely a C++ rule. In C++ accessing class members is very
different and much less flexible than using class methods. Other OO languages
are not universally so hobbled.
Really? I can only assume you're referring to languages such as perl that have
ties (or equivalent capability). In that case, you're still accessing the data
through a method, it's just a more abstract abstraction. Or are you referring
to something else?


Well the original motivation is that in C++ member references are handled as C
structure member references which hard code the offset in the structure into
the code. This means nearly any change to your class such as changing member
ordering, changing data types, or adding a member anywhere but at the end
introduces ABI changes that no linker can detect.

Method references on the other hand are resolved by name by the linker. The
linker can resolve problems and you can provide legacy fall-back methods for
old code. So in C++ maintaining a stable ABI is much easier using method calls
than member references.

Languages such as Perl, or Lisp, or most any other OO languages not trying to
maintain C style efficiency resolve member references by name so you can add
or change the "order" of your members without introducing "abi"
incompatibility.
The last thing we'd want is someone getting the wrong answer to a financial
query because they don't understand the schema. A much more reliable way is
to have them

SELECT * FROM get_monthy_payment(month, account); so we know they're getting
the correct answer.


Of course that doesn't really change the need for the person writing the query
to understand the schema, it just changes which tools that person's working
with. Whoever wrote get_monthy_payment could just as easily get the query
wrong, especially since they seem to be a poor typist :)

You can do the same thing without depending on stored procedures by enforcing
that only low level modules of your application under the control of the same
schema-aware people get to write SQL queries. Upper level modules are only
allowed to call $account->get_monthly_payment($month).

This has the advantage that if get_monthly_payment involves doing several
queries and incorporating out-of-database information it can do so without
forcing people to use awkward database procedural languages or imposing awkard
apis.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Shufen | last post by:
Hi, I'm a newbie that just started to learn python, html and etc. I have some questions to ask and hope that someone can help me on. I'm trying to code a python script (with HTML) to get...
20
by: Glenn Venzke | last post by:
I'm writing a class with a method that will accept 1 of 3 items listed in an enum. Is it possible to pass the item name without the enum name in your calling statement? EXAMPLE: public enum...
8
by: Zlatko Matić | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the...
5
by: Jeong-Gun Lee | last post by:
I'm writing a code of writing a value to a specific memory address. ================================================================= #include <stdio.h> int main() { long air; long...
2
by: XML newbie: Urgent pls help! | last post by:
Hi, I am getting the error: Value of type 'String' cannot be converted to '1-dimensional array of Long'. in the following line for TextBox2.Text field : ...
29
by: garyusenet | last post by:
I'm trying to investigate the maximum size of different variable types. I'm using INT as my starting variable for exploration. I know that the maximum number that the int variable can take is:...
6
by: Bob Darlington | last post by:
I want to use the caption property for fields in a recordset as a condition in a loop. That is, I only want to consider those fields which have captions: For each fld in RecordsetName.Fields If...
14
by: Aman JIANG | last post by:
hi i need a fast way to do lots of conversion that between string and numerical value(integer, float, double...), and boost::lexical_cast is useless, because it runs for a long time, (about 60...
0
by: remya1000 | last post by:
i'm using VB.Net 2005 application program. i'm trying to convert VB6 code to VB.Net 2005. QSockB is DLL file. this is the code i used for VB6. This is code i'm using to create socket, when...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.