Thread: Problems with varchar/text
Hi out there, I've got a table with a field named "blabla", type is varchar(12). Now I want to insert "00083151" into this field, but after the INSERT the leading zeros are cutted, eg. in the DB is only "83151" Same Problem with type text... Any Idea how I can get the _complete_ Value with the leading zeros into the DB ? Thanks in Advance ! Florian
I'm using ColdFusion with a native PostgreSQL-Driver. OS is Linux, and my insert-statement looks like this: INSERT INTO billing (billnumber) VALUES (00086901) > Maybe you have forgot to quote the string. > Could you mail us the exact SQL that you use to insert the data. > Are you using ODBC? What OS/app is the client and server? > FMK wrote: >> Hi out there, >> >> I've got a table with a field named "blabla", type is varchar(12). >> >> Now I want to insert "00083151" into this field, but after the INSERT >> the leading zeros are cutted, eg. in the DB is only "83151" >> >> Same Problem with type text... >> >> Any Idea how I can get the _complete_ Value with the leading zeros >> into the DB ? >> >> Thanks in Advance ! >> >> Florian
Thanks to all of you for your answers !
We're using CF 4.5 on Linux, and there is a Postgres-Driver built in
(the Example-DBs of CF are Postgres-DBs afaik)
Greetings
/florian
> That's funny, I also use Cold Fusion (but on NT with ODBC drivers).
> Try using quotes: INSERT INTO billing (billnumber) VALUES ('00086901')
> ColdFusion with a native PostgreSQL-Driver? How do you set that up?
> FMK wrote:
>> I'm using ColdFusion with a native PostgreSQL-Driver. OS is Linux, and
>> my insert-statement looks like this:
>>
>> INSERT INTO billing (billnumber) VALUES (00086901)
>>
>> > Maybe you have forgot to quote the string.
>>
>> > Could you mail us the exact SQL that you use to insert the data.
>>
>> > Are you using ODBC? What OS/app is the client and server?
>>
>> > FMK wrote:
>>
>> >> Hi out there,
>> >>
>> >> I've got a table with a field named "blabla", type is varchar(12).
>> >>
>> >> Now I want to insert "00083151" into this field, but after the INSERT
>> >> the leading zeros are cutted, eg. in the DB is only "83151"
>> >>
>> >> Same Problem with type text...
>> >>
>> >> Any Idea how I can get the _complete_ Value with the leading zeros
>> >> into the DB ?
>> >>
>> >> Thanks in Advance !
>> >>
>> >> Florian
On Wed, 30 Aug 2000, FMK wrote:
> I'm using ColdFusion with a native PostgreSQL-Driver. OS is Linux, and
> my insert-statement looks like this:
>
> INSERT INTO billing (billnumber) VALUES (00086901)
>
> > Maybe you have forgot to quote the string.
> > Could you mail us the exact SQL that you use to insert the data.
Your answer is right there. Most languages are going to
see your INSERT statement as:
INSERT INTO billing (billnumber) VALUES (some_number)
If you want to insert a string, you need to quote it:
INSERT INTO billing (billnumber) VALUES ("00086901")
I don't know if you need double quotes, single quotes or
something else. They are quite often different (certainly
they are using the perl-postgres interface, which is what
I have some experience with).
But your interpretter is seeing a number (00086901) with leading,
insignificant zeros. It is deleting those zeros before executing
the statement. Just like 9.0000000000000000000000000000000000000
gets executed as 9.
Gord
Matter Realisations http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101 9504 182 St. NW Edmonton, AB, CA T5T 3A7
780/481-8019 ghaverla @ freenet.edmonton.ab.ca
I ran across some interesting (at least to me) initial results in regression testing 7.0.2 on my Ultra 2 running Solaris 2.5.1. The makefile in regress/sql deletes the create_function_1.sql, create_function_2.sql, copy.sql, contraints.sql and misc.sql files when you run make all. Then when you run make runtest, those tests fail (cause error messages) are the executable scripts are no longer there. I understand why int8 fails as a test (2.5.1 doesn't have int8's), and I am still looking into create_function_2, triggers, subselect and plpgsql. There seems to be quite a bit more than timezone and floating point roundoffs here. Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
create_function_2.out ERROR: Load of file /.../regress.so failed: ld.so.1: \ /.../bin/postmaster: fatal: relocation error: \ file /.../regress.so: symbol CurrentMemoryContext: \ referenced symbol not found Something wrong about that error message in the diffs file. Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
ghaverla@freenet.edmonton.ab.ca writes:
> I ran across some interesting (at least to me) initial results
> in regression testing 7.0.2 on my Ultra 2 running Solaris 2.5.1.
> The makefile in regress/sql deletes the create_function_1.sql,
> create_function_2.sql, copy.sql, contraints.sql and misc.sql
> files when you run make all. Then when you run make runtest,
> those tests fail (cause error messages) are the executable
> scripts are no longer there.
Huh? To prepare for a regression test you should do "make clean all" in
src/test/regress, not in its subdirectories. The files you mention are
supposed to get created from template files that are in regress/input.
If you're doing the make per instructions and that's not happening, then
there's something squirrely about your copy of make...
regards, tom lane
On Wed, 30 Aug 2000, Tom Lane wrote: > ghaverla@freenet.edmonton.ab.ca writes: > > I ran across some interesting (at least to me) initial results > > in regression testing 7.0.2 on my Ultra 2 running Solaris 2.5.1. > > > The makefile in regress/sql deletes the create_function_1.sql, > > create_function_2.sql, copy.sql, contraints.sql and misc.sql > > files when you run make all. Then when you run make runtest, > > those tests fail (cause error messages) are the executable > > scripts are no longer there. > > Huh? To prepare for a regression test you should do "make clean all" in > src/test/regress, not in its subdirectories. The files you mention are > supposed to get created from template files that are in regress/input. > If you're doing the make per instructions and that's not happening, then > there's something squirrely about your copy of make... I was running make in src/test/regress. I was running make clean, and then make all. I was trying to figure out why those files were never there when the tests ran, so I cd'd to sql and did make all. I observed that it was deleting the files. After I commented out the rm, things seemed to work. I know it was a hack, but I couldn't see why these files were getting deleted. It is a fairly recent GNU make, but I got it off a download site. Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
More Re: 7.0.2 regressions testing on Sparc running 2.5.1
From
ghaverla@freenet.edmonton.ab.ca
Date:
On Wed, 30 Aug 2000, Tom Lane wrote: > ghaverla@freenet.edmonton.ab.ca writes: > > The makefile in regress/sql deletes the create_function_1.sql, > > create_function_2.sql, copy.sql, contraints.sql and misc.sql > > files when you run make all. Then when you run make runtest, > > those tests fail (cause error messages) are the executable > > scripts are no longer there. I still don't know why make deletes those files, maybe I'll try to trace that later. The errors I'm seeing upon loading the regress.so file are a little baffling to me. The path mentioned in the error message to the shared object library is correct, and if I load the file into emacs, I can find the symbol it is complaining about (or at least a text string with that symbol, in this case the symbol is CurrentMemoryContext). If I run objdump on the file, I get the following for CurrentMemoryContext: 00000000 *UND* 00000000 CurrentMemoryContext I'm sort of guessing that somehow my mishmash of Solaris and GNU is building a bad shared object file. But having never debugged anything like this before, the going is slow. Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
The first ERROR listed in the diffs comes from CurrentMemoryContext not being locate din regress.so. I've never had a bug like this to trackdown before, so I don't know if the following is meaningful or not. This variable/symbol has an underfined address in every *.so present. If I go looking in just object modules, it is defined in 3 with valid addresses, all other object modules which define this symbol also have no defined address for it. src/backend/utils/mmgr/mcxt.o 00000004 g 0 .data 00000004 CurrentMemoryContext src/backend/utils/mmgr/SUBSYS.o 00000004 g 0 .data 00000004 CurrentMemoryContext src/backend/utils/SUBSYS.o 00007954 g 0 .data 00000004 CurrentMemoryContext Is this normal, that it is the same in 2 modules, but not the third? Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
ghaverla@freenet.edmonton.ab.ca writes:
> Is this normal, that it is the same in 2 modules, but not
> the third?
Looks normal enough to me --- that's just from the linker merging
modules together.
My bet is that you are chasing the wrong idea. I doubt there's anything
special about CurrentMemoryContext; probably it just happens to be the
first symbol the linker tries (and fails) to link while loading your
shared object file. I'd be looking at issues like whether you have
things configured for dynamic shared-object linking at all. For
example, on HPUX it's necessary to use a special linker switch (-E,
I think) while linking the main "postgres" executable, or else its
global symbols won't be set up to be accessible by dynamically loaded
objects. Dunno if there's a similar incantation needed on your
platform, but that seems like the sort of issue to be looking at.
regards, tom lane
On Fri, 1 Sep 2000, Tom Lane wrote: > ghaverla@freenet.edmonton.ab.ca writes: > > Is this normal, that it is the same in 2 modules, but not > > the third? > > Looks normal enough to me --- that's just from the linker merging > modules together. > > My bet is that you are chasing the wrong idea. I doubt there's anything > special about CurrentMemoryContext; probably it just happens to be the > first symbol the linker tries (and fails) to link while loading your > shared object file. I'd be looking at issues like whether you have > things configured for dynamic shared-object linking at all. For > example, on HPUX it's necessary to use a special linker switch (-E, > I think) while linking the main "postgres" executable, or else its > global symbols won't be set up to be accessible by dynamically loaded > objects. Dunno if there's a similar incantation needed on your > platform, but that seems like the sort of issue to be looking at. Okay, I'll try one more day of trying to build this (Tuesday I guess). If I am still not get anywhere I will have to go pick up a binary that someone else has fought with. If anyone ese has words of wisdom to help, they would be much appreciated. Have a great Labour Day weekend people! Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
That's funny, I also use Cold Fusion (but on NT with ODBC drivers).
Try using quotes: INSERT INTO billing (billnumber) VALUES ('00086901')
ColdFusion with a native PostgreSQL-Driver? How do you set that up?
FMK wrote:
> I'm using ColdFusion with a native PostgreSQL-Driver. OS is Linux, and
> my insert-statement looks like this:
>
> INSERT INTO billing (billnumber) VALUES (00086901)
>
> > Maybe you have forgot to quote the string.
>
> > Could you mail us the exact SQL that you use to insert the data.
>
> > Are you using ODBC? What OS/app is the client and server?
>
> > FMK wrote:
>
> >> Hi out there,
> >>
> >> I've got a table with a field named "blabla", type is varchar(12).
> >>
> >> Now I want to insert "00083151" into this field, but after the INSERT
> >> the leading zeros are cutted, eg. in the DB is only "83151"
> >>
> >> Same Problem with type text...
> >>
> >> Any Idea how I can get the _complete_ Value with the leading zeros
> >> into the DB ?
> >>
> >> Thanks in Advance !
> >>
> >> Florian
Maybe you have forgot to quote the string. Could you mail us the exact SQL that you use to insert the data. Are you using ODBC? What OS/app is the client and server? FMK wrote: > Hi out there, > > I've got a table with a field named "blabla", type is varchar(12). > > Now I want to insert "00083151" into this field, but after the INSERT > the leading zeros are cutted, eg. in the DB is only "83151" > > Same Problem with type text... > > Any Idea how I can get the _complete_ Value with the leading zeros > into the DB ? > > Thanks in Advance ! > > Florian
On Fri, 1 Sep 2000, Tom Lane wrote:
> My bet is that you are chasing the wrong idea.
...
> I'd be looking at issues like whether you have
> things configured for dynamic shared-object linking at all. For
> example, on HPUX it's necessary to use a special linker switch (-E,
> I think) while linking the main "postgres" executable, or else its
> global symbols won't be set up to be accessible by dynamically loaded
> objects. Dunno if there's a similar incantation needed on your
> platform, but that seems like the sort of issue to be looking at.
I think that's it. I did a make clean on the main makefile, and
ran make again. This time directing all output to a file.
There are a few places where -fPIC occurs (which is part of the
shared object business). However, doing a search about -fPIC
turned up:
Forum: comp.unix.unixware.misc
Subject: Re: building shared object with gcc/g++
Author: Casper H.S.Dik - Network Security Engineer
<Casper.Dik@Holland.Sun.Com>
...
Do not use ld directly.
Do not use "gcc -G"
Use gcc -shared
...
You must always link with gcc, including when linking shared
libraries.
-----------------------------------------------------------
So, I guess all of my compiles aren't quite right. Hopefully
this will fix things. I still possibly have an issue with
Run-time link paths. I have been adding -R /path/to/lib
but I guess the proper gcc incantation is
-Xlinker -R -Xlinker /path/to/lib
Have a great weekend.
Gord
Matter Realisations http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101 9504 182 St. NW Edmonton, AB, CA T5T 3A7
780/481-8019 ghaverla @ freenet.edmonton.ab.ca