Thread: Re: pgsql-interfaces-digest V1 #180

Hi again,
    Since my first Email apparantly went into the bit bucket, I thought
I'd try again. The following is a post from last year :). Perhaps
some one can give an authoratative answer.

Thanks,
Bill

> Date: Thu, 31 Dec 1998 15:16:59 +0000
> From: "Bill Hutto" <bill@hammondgroves.com>
> Subject: Money type mapping to VB5 Jet workspace
>
> Hi,
>     I'm having problems with the mapping of the money type to an
> appropriate type in Visual Basic 5.0. Presently, it is coming across
> as type float8, and if that were acceptable to me, it isn't when I
> update, because I get an error '524':
>
>  parser: attribute 'amount'  is of type 'money' but expression is of
> type 'float8'
> You will need to rewrite or cast the expression.
>
> I can successfully update only with an ODBC direct workspace, where I
> can add single quotes around the value. I would prefer to be able to
> use the Jet workspace, however, as well as data bound controls.
> Is it possible to have it map to the currency data type?
>
> Thanks,
> Bill
>
> ------------------------------
>
> Date: Thu, 31 Dec 1998 13:58:31 -0800
> From: "Ken J. Wright" <ken@ori-ind.com>
> Subject: Re: [INTERFACES] Money type mapping to VB5 Jet workspace
>
> This is also a problem with the BDE & also with libpq. The odbc driver
> could/should? fix this, but then what about libpq? The docs say the money
> type is going away someday. Should we avoid using it? I believe a proper
> fix involves both the backend & frontends. Forcing quotes around a numeric
> value throws everyone off. Developer comments?
>
> Ken
>
> At 03:16 PM 12/31/98 +0000, you wrote:
> >Hi,
> >    I'm having problems with the mapping of the money type to an
> >appropriate type in Visual Basic 5.0. Presently, it is coming across
> >as type float8, and if that were acceptable to me, it isn't when I
> >update, because I get an error '524':
> >
> > parser: attribute 'amount'  is of type 'money' but expression is of
> >type 'float8'
> >You will need to rewrite or cast the expression.
> >
> >I can successfully update only with an ODBC direct workspace, where I
> >can add single quotes around the value. I would prefer to be able to
> >use the Jet workspace, however, as well as data bound controls.
> >Is it possible to have it map to the currency data type?
> >
> >Thanks,
> >Bill
> >
> >
>
> ------------------------------
>
> End of pgsql-interfaces-digest V1 #180
> **************************************
>
>

Bill Hutto wrote:

> Hi again,
>         Since my first Email apparantly went into the bit bucket, I thought
> I'd try again. The following is a post from last year :). Perhaps
> some one can give an authoratative answer.
>

Actually, two different people responded to your mail.  However, it doesn't look
like they CC'd it to you.   Maybe you should register on the pgsql-interfaces
list.

> > Date: Thu, 31 Dec 1998 15:16:59 +0000
> > From: "Bill Hutto" <bill@hammondgroves.com>
> > Subject: Money type mapping to VB5 Jet workspace
> >
> > Hi,
> >       I'm having problems with the mapping of the money type to an
> > appropriate type in Visual Basic 5.0. Presently, it is coming across
> > as type float8, and if that were acceptable to me, it isn't when I
> > update, because I get an error '524':
> >
> >  parser: attribute 'amount'  is of type 'money' but expression is of
> > type 'float8'
> > You will need to rewrite or cast the expression.
>

Its not exactly clear what you are trying to do.  But I'll take a guess at it.
In the postgres database, the "amount" field is obviously of type money.  The
sql statement, which I haven't seen, is probably trying to do an expression on
that column.  Unfortunately, you can't easily do expressions on a money field in
Postgres, without casting it or something like that.  And since the Jet is just
sending the statement, it isn't going to work.  (I know you mentioned quotes,
but the error message above suggests this has nothing to do with quotes -- it is
some expression that is killing it.  And besides, if it were a quote thing, the
error message would have been different.)

I recommend forgetting about the money type, and just stick with float for now.
When we have full Numeric() support, then you can use a SQL_NUMERIC or
SQL_DECIMAL to get the right precision.  You will not be able to use dollar
signs and commas, but I don't think that is a show stopper, is it?

If you need further convincing, there isn't even a "money" data type on the odbc
side.  Money is merely an attribute of a result set column.  And worse in odbc
3.0, I don't see any mention whatsoever of money, even as an attribute!   So, it
makes it very difficult to convert the field within the driver.

If I've missed what you are trying to do, then send the "psqlodbc.log" file of
your session so I can see the exact statement.

Byron

> Its not exactly clear what you are trying to do.  But I'll take a guess at it.
> In the postgres database, the "amount" field is obviously of type money.  The
> sql statement, which I haven't seen, is probably trying to do an expression on
> that column.  Unfortunately, you can't easily do expressions on a money field in
> Postgres, without casting it or something like that.  And since the Jet is just
> sending the statement, it isn't going to work.  (I know you mentioned quotes,
> but the error message above suggests this has nothing to do with quotes -- it is
> some expression that is killing it.  And besides, if it were a quote thing, the
> error message would have been different.)

I'm assuming the error was caused by the Jet passing a float on
the update. It probably has something to do with DAO. I tested
ODBCDirect using a SQL Insert and was able to pull it off (with the
quotes).

>
> I recommend forgetting about the money type, and just stick with float for now.
> When we have full Numeric() support, then you can use a SQL_NUMERIC or
> SQL_DECIMAL to get the right precision.  You will not be able to use dollar
> signs and commas, but I don't think that is a show stopper, is it?

<snip>

I was just asking if the Money type could be converted to the
appropriate type (SQL_NUMERIC according to my docs) so that I could
bind the column to a control, as well as just generally use DAO to
manipulate the data, thereby making it easier to use any DBMS with
little mods. If I have to wait for the Numeric type implementation, I
can probably handle that :).

Thanks,
Bill