Обсуждение: Ok... I got most of it working but . . .

I am using arrays of double precision numbers in certain columns in my
table.  When I format the CString object (analogous to Glib::ustring
objects in the linux world) to update this column it looks like this:


"{0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00}"

However, when the odbc driver tries to update the new added record, I
get the following error message server side (and at my application):

    ERROR:  invalid input syntax for type double precision:
"{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}"

Evidently,  the odbc driver is deciding to optimize my query perhaps?
What am I doing wrong this time?

Thanks,

Bob Caryl

Is that supposed to be an SQL string, like
"INSERT INTO my_table (field1, field2, field3, field3) VALUES
(0.00,0.00,0.00)"

parentheis -- not curly braces?



Bob Caryl wrote:
> I am using arrays of double precision numbers in certain columns in my
> table.  When I format the CString object (analogous to Glib::ustring
> objects in the linux world) to update this column it looks like this:
>
>
> "{0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00}"
>
>
> However, when the odbc driver tries to update the new added record, I
> get the following error message server side (and at my application):
>
>    ERROR:  invalid input syntax for type double precision:
> "{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}"
>
> Evidently,  the odbc driver is deciding to optimize my query perhaps?
> What am I doing wrong this time?
>
> Thanks,
>
> Bob Caryl
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match

Greg Campbell wrote:

> Is that supposed to be an SQL string, like
> "INSERT INTO my_table (field1, field2, field3, field3) VALUES
> (0.00,0.00,0.00)"
>
> parentheis -- not curly braces?
>
>
>
> Bob Caryl wrote:
>
>> I am using arrays of double precision numbers in certain columns in
>> my table.  When I format the CString object (analogous to
>> Glib::ustring objects in the linux world) to update this column it
>> looks like this:
>>
>>
>> "{0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00}"
>>
>>
>> However, when the odbc driver tries to update the new added record, I
>> get the following error message server side (and at my application):
>>
>>    ERROR:  invalid input syntax for type double precision:
>> "{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}"
>>
>> Evidently,  the odbc driver is deciding to optimize my query
>> perhaps?  What am I doing wrong this time?
>>
>> Thanks,
>>
>> Bob Caryl
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>       choose an index scan if your joining column's datatypes do not
>>       match
>
To quote the documentation on the use of arrays in postgresql tables:

"To INSERT values into an array, you enclose all the array elements in
single quotes and braces ({}) and separate multipl elements with a comma."

Hence, if I have an array of double precision numbers called
misctendamt, and I wanted to insert 16 zero values into that array the
INSERT statement would look like this:

"INSERT INTO my_table (misctendamt) VALUES
'{0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00}'"


Unfortunately when using a derived MFC CRecordset object, I do not
actually write queries.  I have to use class members of my derived
CRecordset class that are actually variables corresponding to the
columns of my table.  So, for the array of double precision numbers, I
populate the CString variable with the
"{0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00}"
data, and then the odbc driver is apparently optimizing it to read
"{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}"  and it is that the postgresql
server is choking on.  The server wants to see those decimal points!

Bob Caryl

> >> However, when the odbc driver tries to update the new added record,
I
> >> get the following error message server side (and at my
application):
> >>
> >>    ERROR:  invalid input syntax for type double precision:
> >> "{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}"
> >>
> >> Evidently,  the odbc driver is deciding to optimize my query
> >> perhaps?  What am I doing wrong this time?

Doesn't look like it.  I tested in psql and server converted from array
of zeroes to array of double precision just fine.  Maybe you have some
older version of the server that doesn't have the cast.

What it looks like to me is that the server is trying to convert the
entire string to double precision.  Are you sure your tables has an
array type?

Observe: (note the last error message)
esp=# create table test (d character varying[]);
CREATE TABLE
esp=# drop table test;
DROP TABLE
esp=# create table test (d numeric(12,3)[]);
CREATE TABLE
esp=# insert into test values ('{0,0,0,0,0,0,0}');
INSERT 1121797 1
esp=# drop table test;
DROP TABLE
esp=# create table test (d double precision[]);
CREATE TABLE
esp=# insert into test values ('{0,0,0,0,0,0,0}');
INSERT 1121803 1
esp=# drop table test;
DROP TABLE
esp=# create table test (d double precision[]);
CREATE TABLE
esp=# insert into test values ('{0,0,0,0,0,0,a}');
ERROR:  invalid input syntax for type double precision: "a"
esp=#

Merlin