pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedM$-Access'97 and TIMESTAMPs
27+ messages / 5 participants
[nested] [flat]
* M$-Access'97 and TIMESTAMPs
@ 1998-06-09 10:18 Jose' Soares Da Silva <sferac@bo.nettuno.it>
0 siblings, 2 replies; 27+ messages in thread
From: Jose' Soares Da Silva @ 1998-06-09 10:18 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Byron Nikolaidis <byronn@insightdist.com>
Hi, all!
I created a table with a TIMESTAMP data type to use with M$-Access, because
Access uses such field to control concurrent access on records.
But I have a problem M$-Access doesn't recognize a TIMESTAMP type, it see
such fields as "text" instead of "date/time".
Is there a way to make Access recognize TIMESTAMPs ?
Thanks, Jose'
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-09 13:16 Byron Nikolaidis <byronn@insightdist.com>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
1 sibling, 2 replies; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-09 13:16 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; +Cc: pgsql-interfaces
Jose' Soares Da Silva wrote:
> Hi, all!
>
> I created a table with a TIMESTAMP data type to use with M$-Access, because
> Access uses such field to control concurrent access on records.
> But I have a problem M$-Access doesn't recognize a TIMESTAMP type, it see
> such fields as "text" instead of "date/time".
> Is there a way to make Access recognize TIMESTAMPs ?
> Thanks, Jose'
I could add TimeStamp as a supported data type of the odbc driver. Currently,
'abstime' is supported but not 'timestamp'.
Byron
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] M$-Access'97 and TIMESTAMPs
@ 1998-06-09 14:42 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
1 sibling, 1 reply; 27+ messages in thread
From: Jose' Soares Da Silva @ 1998-06-09 14:42 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Byron Nikolaidis <byronn@insightdist.com>
On Tue, 9 Jun 1998, Jose' Soares Da Silva wrote:
> Hi, all!
>
> I created a table with a TIMESTAMP data type to use with M$-Access, because
> Access uses such field to control concurrent access on records.
> But I have a problem M$-Access doesn't recognize a TIMESTAMP type, it see
> such fields as "text" instead of "date/time".
> Is there a way to make Access recognize TIMESTAMPs ?
> Thanks, Jose'
Also the following types are recognized as text:
int28
oid8
oidint2
oidint4
I forgot to say that I'm using :
PostgreSQL-6.3.2
Linyx ELF 2.0.33
psqlodbc-06.30.0243
M$-Access97
Ciao, Jose'
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-09 15:12 Byron Nikolaidis <byronn@insightdist.com>
parent: Byron Nikolaidis <byronn@insightdist.com>
1 sibling, 1 reply; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-09 15:12 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; pgsql-interfaces
Byron Nikolaidis wrote:
> I could add TimeStamp as a supported data type of the odbc driver. Currently,
> 'abstime' is supported but not 'timestamp'.
>
Also, the postgres "datetime" type is already supported as well.
Maybe that would work for you temporarily.
As a matter of fact, all the date/time types "look" the same since we now use
'ISO'.
Byron
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] M$-Access'97 and TIMESTAMPs
@ 1998-06-09 15:55 Byron Nikolaidis <byronn@insightdist.com>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
0 siblings, 0 replies; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-09 15:55 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; +Cc: pgsql-interfaces
Jose' Soares Da Silva wrote:
> Also the following types are recognized as text:
> int28
> oid8
> oidint2
> oidint4
>
Just a little history here...any data type that is not directly supported by the
odbc driver will get mapped to SQL_VARCHAR or SQL_LONGVARCHAR, depending on
driver 'data type options'. That allows you to view it and possibly update it,
if there is an appropriate operator. This is great compared to what the driver
used to do in the old days with unsupported types (i.e., crash with no
descriptive error message)!
For int28 and oid8, there is no SQL data type that maps. Text is the only way to
display it that I know of.
oidint2 and oidint4 are just integers I guess, and probably could be mapped to
SQL_SMALLINT and SQL_INTEGER, respectively.
Byron
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-09 17:25 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Byron Nikolaidis <byronn@insightdist.com>
1 sibling, 0 replies; 27+ messages in thread
From: Jose' Soares Da Silva @ 1998-06-09 17:25 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-interfaces
On Tue, 9 Jun 1998, Byron Nikolaidis wrote:
>
>
> Jose' Soares Da Silva wrote:
>
> > Hi, all!
> >
> > I created a table with a TIMESTAMP data type to use with M$-Access, because
> > Access uses such field to control concurrent access on records.
> > But I have a problem M$-Access doesn't recognize a TIMESTAMP type, it see
> > such fields as "text" instead of "date/time".
> > Is there a way to make Access recognize TIMESTAMPs ?
> > Thanks, Jose'
>
> I could add TimeStamp as a supported data type of the odbc driver. Currently,
> 'abstime' is supported but not 'timestamp'.
>
Thank you Byron.
I think this is great. M$-Access should work well with a timestamp field,
I have problems with concurrent access and I think it is because this data type.
Jose'
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-10 09:27 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 27+ messages in thread
From: Jose' Soares Da Silva @ 1998-06-10 09:27 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-interfaces
On Tue, 9 Jun 1998, Byron Nikolaidis wrote:
>
>
> Byron Nikolaidis wrote:
>
> > I could add TimeStamp as a supported data type of the odbc driver. Currently,
> > 'abstime' is supported but not 'timestamp'.
> >
>
> Also, the postgres "datetime" type is already supported as well.
> Maybe that would work for you temporarily.
> As a matter of fact, all the date/time types "look" the same since we now use
> 'ISO'.
My problem is that I need a TIMESTAMP data type defined in M$-Access because
M$-Access wants it to have best performance when it updates a table via ODBC.
M$-Access doesn't lock a record being modified, to allow control concurrent
access to data M$-Access reads again the record to verify if it was modified by
another user, before update it to database.
If there's a TIMESTAMP M$-Access verifies only, if this field was modified,
otherwise it verifies every field of the table, and obviously it is slower.
I beleave it would very useful if you could add this feature to psqlodbc.
Thanks, Jose'
>
> Byron
>
>
>
Ciao, Jose'
___, /
|_+_| /| / ~
~~~~~~~~~~~~~~~~~~~~~~~~~ | / | /| ~~~~~~~~~~~~~~~~~~~~~
Jose' Soares Da Silva ~ |/ | / | / "As armas e os Baroes
Progetto "OS LUSIADAS" ~ | |/| | /| assinalados, que da
SFERA CARTA SOFTWARE ~ /| / | | / | Occidental praia Lusitana
Via Bazzanese, 69 / | / | | /| | por mares nunca de antes
Casalecchio R. BO - Italy / | / | |/ | | navegados, passarono
http://www.sferacarta.com / |/____|_/__|_| ainda alem da Taprobana"
sferac@bo.nettuno.it /____|__| | __|___________ ~
Fax. ++39 51 6131537 ____________|_____|_/ LUSIADAS / (Luis de Camoes,
Tel. ++39 51 591054 \ o / Os Lusiadas, canto I)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~\~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-10 15:15 Byron Nikolaidis <byronn@insightdist.com>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
0 siblings, 1 reply; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-10 15:15 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; +Cc: pgsql-interfaces
Jose' Soares Da Silva wrote:
> My problem is that I need a TIMESTAMP data type defined in M$-Access because
> M$-Access wants it to have best performance when it updates a table via ODBC.
> M$-Access doesn't lock a record being modified, to allow control concurrent
> access to data M$-Access reads again the record to verify if it was modified by
> another user, before update it to database.
> If there's a TIMESTAMP M$-Access verifies only, if this field was modified,
> otherwise it verifies every field of the table, and obviously it is slower.
> I beleave it would very useful if you could add this feature to psqlodbc.
> Thanks, Jose'
>
I have absolutely no problem with adding the postgres 'timestamp' type, in fact, I
already added it.
But, the thing is, the postgres types abstime and datetime, ALREADY map to
SQL_TIMESTAMP!
I think, that this actually has to do with SQLSpecialColumns 'SQL_ROWVER'. Access
checks for this but we don't return anything. SQL_ROWVER is defined as the column(s)
in the specified table, if any, that are automatically updated by the data source when
any value in the row is updated by any transaction (as in SQLBase ROWID or Sybase
TIMESTAMP).
It seems to me, that this suggests that if we had a hidden timestamp column, Access
would use that to verify. I don't believe we have such a column in postgres?
Byron
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-10 17:45 Byron Nikolaidis <byronn@insightdist.com>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-10 17:45 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; pgsql-interfaces
Byron Nikolaidis wrote:
> Jose' Soares Da Silva wrote:
>
> > My problem is that I need a TIMESTAMP data type defined in M$-Access because
> > M$-Access wants it to have best performance when it updates a table via ODBC.
> > M$-Access doesn't lock a record being modified, to allow control concurrent
> > access to data M$-Access reads again the record to verify if it was modified by
> > another user, before update it to database.
> > If there's a TIMESTAMP M$-Access verifies only, if this field was modified,
> > otherwise it verifies every field of the table, and obviously it is slower.
> > I beleave it would very useful if you could add this feature to psqlodbc.
> > Thanks, Jose'
> >
>
I did some testing with SQLSpecialColumns 'SQL_ROWVER'. As I noted in my previous mail,
we dont return anything for this function in the driver. I tried hard-coding a column
that was a SQL_TIMESTAMP type (in my table it was a postgres 'datetime'). Access did use
that column. Here are the results:
test1 table
----------
a,c,d,e,f,g = int2
b,h = varchar
datetim = datetime
Access results without ROWVER (this is the way things currently are)
---------------------------------------------------------------------
BEGIN
update test1 set b='hello' where a=7 AND b='other' AND c=3 AND d=4 AND e is NULL AND f is
NULL AND g=5 AND h='stuff'
COMMIT
Access results with ROWVER
-------------------------------
BEGIN
update test1 set b='hello' where a=7 AND datetim = '1998-05-30 10:59:00';
select a,b,c,d,e,f,g,h,datetim where a=7;
COMMIT
Conclusion:
-----------
The update statement was definately smaller and only involved the key and the timestamp
column. The extra select that it does to verify no one has changed anything (using the
value of the timestamp) slowed the update down, though. I don't think the speed gain on
the smaller update statement makes up for the extra query. In either case, the backend
locking problem would still prevent the update if the table was opened by someone else (or
even the same application, as in our declare/fetch problem).
Also, something would have to be done to actually put a timestamp value in every time a
row was added or updated. Access actually prevented me from entering a value in my
'datetim' field because it assumed the dbms would fill it in. I guess you could use a
trigger to update the timestamp field. OR if we had a pseudo column that qualified, we
could use that, however when I tried using a pseudo column, Access barfed on me
complaining "Table TMP%#$$^ already exists". If I added the pseudo column to the output,
the message went away. I have no idea what the heck that means?
Any ideas or thoughts?
Byron
^ permalink raw reply [nested|flat] 27+ messages in thread
* RE: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-10 18:36 Krasnow, Greg <gak@hnc.com>
0 siblings, 1 reply; 27+ messages in thread
From: Krasnow, Greg @ 1998-06-10 18:36 UTC (permalink / raw)
To: 'Byron Nikolaidis' <byronn@insightdist.com>; Jose' Soares Da Silva <sferac@bo.nettuno.it>; pgsql-interfaces
I haven't looked at DATETIME stuff, but does Postgres not have something
similar to Oracle's SYSDATE? In Oracle you can set an Oracle DATE column to
have a default of SYSDATE. This way Oracle can fill in the column at the
time an insert is done.
- Greg
> -----Original Message-----
> From: Byron Nikolaidis [SMTP:byronn@insightdist.com]
> Sent: Wednesday, June 10, 1998 10:46 AM
> To: Jose' Soares Da Silva; interfaces postgres
> Subject: Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
>
>
>
>
> Byron Nikolaidis wrote:
>
> > Jose' Soares Da Silva wrote:
> >
> > > My problem is that I need a TIMESTAMP data type defined in M$-Access
> because
> > > M$-Access wants it to have best performance when it updates a table
> via ODBC.
> > > M$-Access doesn't lock a record being modified, to allow control
> concurrent
> > > access to data M$-Access reads again the record to verify if it was
> modified by
> > > another user, before update it to database.
> > > If there's a TIMESTAMP M$-Access verifies only, if this field was
> modified,
> > > otherwise it verifies every field of the table, and obviously it is
> slower.
> > > I beleave it would very useful if you could add this feature to
> psqlodbc.
> > > Thanks, Jose'
> > >
> >
>
> I did some testing with SQLSpecialColumns 'SQL_ROWVER'. As I noted in my
> previous mail,
> we dont return anything for this function in the driver. I tried
> hard-coding a column
> that was a SQL_TIMESTAMP type (in my table it was a postgres 'datetime').
> Access did use
> that column. Here are the results:
>
> test1 table
> ----------
> a,c,d,e,f,g = int2
> b,h = varchar
> datetim = datetime
>
> Access results without ROWVER (this is the way things currently are)
> ---------------------------------------------------------------------
> BEGIN
> update test1 set b='hello' where a=7 AND b='other' AND c=3 AND d=4 AND e
> is NULL AND f is
> NULL AND g=5 AND h='stuff'
> COMMIT
>
> Access results with ROWVER
> -------------------------------
> BEGIN
> update test1 set b='hello' where a=7 AND datetim = '1998-05-30 10:59:00';
> select a,b,c,d,e,f,g,h,datetim where a=7;
> COMMIT
>
> Conclusion:
> -----------
> The update statement was definately smaller and only involved the key and
> the timestamp
> column. The extra select that it does to verify no one has changed
> anything (using the
> value of the timestamp) slowed the update down, though. I don't think the
> speed gain on
> the smaller update statement makes up for the extra query. In either
> case, the backend
> locking problem would still prevent the update if the table was opened by
> someone else (or
> even the same application, as in our declare/fetch problem).
>
> Also, something would have to be done to actually put a timestamp value in
> every time a
> row was added or updated. Access actually prevented me from entering a
> value in my
> 'datetim' field because it assumed the dbms would fill it in. I guess
> you could use a
> trigger to update the timestamp field. OR if we had a pseudo column that
> qualified, we
> could use that, however when I tried using a pseudo column, Access barfed
> on me
> complaining "Table TMP%#$$^ already exists". If I added the pseudo
> column to the output,
> the message went away. I have no idea what the heck that means?
>
> Any ideas or thoughts?
>
> Byron
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-10 19:05 Byron Nikolaidis <byronn@insightdist.com>
parent: Krasnow, Greg <gak@hnc.com>
0 siblings, 2 replies; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-10 19:05 UTC (permalink / raw)
To: Krasnow, Greg <gak@hnc.com>; +Cc: Jose' Soares Da Silva <sferac@bo.nettuno.it>; pgsql-interfaces; pgsql-sql@postgreSQL.org <pgsql-sql@postgreSQL.org>
Krasnow, Greg wrote:
> I haven't looked at DATETIME stuff, but does Postgres not have something
> similar to Oracle's SYSDATE? In Oracle you can set an Oracle DATE column to
> have a default of SYSDATE. This way Oracle can fill in the column at the
> time an insert is done.
>
Yes, you are right, and I noticed Jose' earlier mail about this on the 'sql'
list.
If you do:
create table x (a timestamp DEFAULT CURRENT_TIMESTAMP, b varchar);
It works AND it puts in the current time at INSERT of the new row. (I noticed
if you use CURRENT_TIME instead, you get the time you created the table at, for
every row, which is not very useful.)
The only problem is that it doesn't change the value on an UPDATE!
Any thoughts?
Byron
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-11 08:16 Hannu <hannu@trust.ee>
parent: Byron Nikolaidis <byronn@insightdist.com>
1 sibling, 1 reply; 27+ messages in thread
From: Hannu @ 1998-06-11 08:16 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-interfaces
Byron Nikolaidis wrote:
>
> Krasnow, Greg wrote:
>
> > I haven't looked at DATETIME stuff, but does Postgres not have something
> > similar to Oracle's SYSDATE? In Oracle you can set an Oracle DATE column to
> > have a default of SYSDATE. This way Oracle can fill in the column at the
> > time an insert is done.
> >
>
> Yes, you are right, and I noticed Jose' earlier mail about this on the 'sql'
> list.
>
> If you do:
>
> create table x (a timestamp DEFAULT CURRENT_TIMESTAMP, b varchar);
>
> It works AND it puts in the current time at INSERT of the new row. (I noticed
> if you use CURRENT_TIME instead, you get the time you created the table at, for
> every row, which is not very useful.)
>
> The only problem is that it doesn't change the value on an UPDATE!
>
> Any thoughts?
Why not use the system column tmin for this purpose?
hannu=> create table test(i int);
CREATE
hannu=> insert into test values(5);
INSERT 17454 1
hannu=> select tmin,tmax,i from test;
tmin |tmax |i
-----------------------------+-------+-
Thu Jun 11 11:12:17 1998 EEST|current|5
(1 row)
hannu=> update test set i=2;
UPDATE 1
hannu=> select tmin,tmax,i from test;
tmin |tmax |i
-----------------------------+-------+-
Thu Jun 11 11:13:00 1998 EEST|current|2
(1 row)
------
Hannu
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [SQL] Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-11 09:44 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Byron Nikolaidis <byronn@insightdist.com>
1 sibling, 0 replies; 27+ messages in thread
From: Jose' Soares Da Silva @ 1998-06-11 09:44 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: Krasnow, Greg <gak@hnc.com>; pgsql-interfaces; pgsql-sql@postgreSQL.org <pgsql-sql@postgreSQL.org>
On Wed, 10 Jun 1998, Byron Nikolaidis wrote:
>
>
> Krasnow, Greg wrote:
>
> > I haven't looked at DATETIME stuff, but does Postgres not have something
> > similar to Oracle's SYSDATE? In Oracle you can set an Oracle DATE column to
> > have a default of SYSDATE. This way Oracle can fill in the column at the
> > time an insert is done.
> >
>
> Yes, you are right, and I noticed Jose' earlier mail about this on the 'sql'
> list.
>
> If you do:
>
> create table x (a timestamp DEFAULT CURRENT_TIMESTAMP, b varchar);
>
> It works AND it puts in the current time at INSERT of the new row. (I noticed
> if you use CURRENT_TIME instead, you get the time you created the table at, for
> every row, which is not very useful.)
>
> The only problem is that it doesn't change the value on an UPDATE!
>
> Any thoughts?
>
> Byron
>
Well, I think that it may be solved by creating a trigger, I've done this
and seem it works.
---------------------------------------------------------------------------
CREATE TABLE version_test (
username CHAR(10),
version TIMESTAMP
);
CREATE TRIGGER version
BEFORE INSERT OR UPDATE ON version_test
FOR EACH ROW
EXECUTE PROCEDURE version(version);
---------------------------------------------------------------------------
/*
* version.c
* $Modified: 9/6/98 by Jose' Soares Da Silva - inserito un campo timestamp.
*
* insert a value into a timestamp column in response to a trigger
* usage: version(data_time)
*/
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* -"- and triggers */
HeapTuple version(void);
HeapTuple version()
{
Trigger *trigger; /* to get trigger name */
Datum newval; /* new value of column */
char **args; /* arguments */
char *relname; /* triggered relation name */
Relation rel; /* triggered relation */
HeapTuple rettuple = NULL;
TupleDesc tupdesc; /* tuple description */
int attnum;
if (!CurrentTriggerData)
elog(ERROR, "version: i triggers non sono inizializati");
if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event))
elog(ERROR, "version: impossibile usare l'evento STATEMENT");
if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event))
elog(ERROR, "version: deve essere creata prima dell'evento");
if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
rettuple = CurrentTriggerData->tg_trigtuple;
else if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
rettuple = CurrentTriggerData->tg_newtuple;
else
elog(ERROR, "version: impossibile usare l'evento DELETE");
rel = CurrentTriggerData->tg_relation;
relname = SPI_getrelname(rel);
trigger = CurrentTriggerData->tg_trigger;
args = trigger->tgargs;
tupdesc = rel->rd_att;
CurrentTriggerData = NULL;
/* update the TIMESTAMP */
attnum = SPI_fnumber(tupdesc,args[1]);
newval = PointerGetDatum(now());
rettuple = SPI_modifytuple(rel, rettuple, 1, &attnum, &newval, NULL);
if (rettuple == NULL)
elog(ERROR, "version (%s): %d ritornato da SPI_modifytuple",
relname, SPI_result);
pfree(relname);
return (rettuple);
---------------------------------------------------------------------------
Ciao, Jose'
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-11 10:15 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 27+ messages in thread
From: Jose' Soares Da Silva @ 1998-06-11 10:15 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-interfaces
On Wed, 10 Jun 1998, Byron Nikolaidis wrote:
>
>
>
> Byron Nikolaidis wrote:
>
> > Jose' Soares Da Silva wrote:
> >
> > > My problem is that I need a TIMESTAMP data type defined in M$-Access because
> > > M$-Access wants it to have best performance when it updates a table via ODBC.
> > > M$-Access doesn't lock a record being modified, to allow control concurrent
> > > access to data M$-Access reads again the record to verify if it was modified by
> > > another user, before update it to database.
> > > If there's a TIMESTAMP M$-Access verifies only, if this field was modified,
> > > otherwise it verifies every field of the table, and obviously it is slower.
> > > I beleave it would very useful if you could add this feature to psqlodbc.
> > > Thanks, Jose'
> > >
> >
>
> I did some testing with SQLSpecialColumns 'SQL_ROWVER'. As I noted in my previous mail,
> we dont return anything for this function in the driver. I tried hard-coding a column
> that was a SQL_TIMESTAMP type (in my table it was a postgres 'datetime'). Access did use
> that column. Here are the results:
>
> test1 table
> ----------
> a,c,d,e,f,g = int2
> b,h = varchar
> datetim = datetime
>
> Access results without ROWVER (this is the way things currently are)
> ---------------------------------------------------------------------
> BEGIN
> update test1 set b='hello' where a=7 AND b='other' AND c=3 AND d=4 AND e is NULL AND f is
> NULL AND g=5 AND h='stuff'
> COMMIT
>
> Access results with ROWVER
> -------------------------------
> BEGIN
> update test1 set b='hello' where a=7 AND datetim = '1998-05-30 10:59:00';
> select a,b,c,d,e,f,g,h,datetim where a=7;
> COMMIT
>
> Conclusion:
> -----------
> The update statement was definately smaller and only involved the key and the timestamp
> column. The extra select that it does to verify no one has changed anything (using the
> value of the timestamp) slowed the update down, though. I don't think the speed gain on
> the smaller update statement makes up for the extra query. In either case, the backend
I don't know for sure, if in this way Access is faster, I red on Access
manual that it is faster using ROWVER during updates.
I think the extra select is to refresh the data on the Client side, otherwise
Access doesn't refresh the Client and it says that another user has
modified the record (but that other user is me).
> locking problem would still prevent the update if the table was opened by someone else (or
> even the same application, as in our declare/fetch problem).
>
> Also, something would have to be done to actually put a timestamp value in every time a
> row was added or updated. Access actually prevented me from entering a value in my
> 'datetim' field because it assumed the dbms would fill it in. I guess you could use a
> trigger to update the timestamp field. OR if we had a pseudo column that qualified, we
> could use that, however when I tried using a pseudo column, Access barfed on me
> complaining "Table TMP%#$$^ already exists". If I added the pseudo column to the output,
> the message went away. I have no idea what the heck that means?
>
> Any ideas or thoughts?
>
> Byron
Jose'
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-11 11:41 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Hannu <hannu@trust.ee>
0 siblings, 2 replies; 27+ messages in thread
From: Jose' Soares Da Silva @ 1998-06-11 11:41 UTC (permalink / raw)
To: Hannu <hannu@trust.ee>; +Cc: Byron Nikolaidis <byronn@insightdist.com>; pgsql-interfaces
On Thu, 11 Jun 1998, Hannu wrote:
> Byron Nikolaidis wrote:
> >
> > Krasnow, Greg wrote:
> >
> > > I haven't looked at DATETIME stuff, but does Postgres not have something
> > > similar to Oracle's SYSDATE? In Oracle you can set an Oracle DATE column to
> > > have a default of SYSDATE. This way Oracle can fill in the column at the
> > > time an insert is done.
> > >
> >
> > Yes, you are right, and I noticed Jose' earlier mail about this on the 'sql'
> > list.
> >
> > If you do:
> >
> > create table x (a timestamp DEFAULT CURRENT_TIMESTAMP, b varchar);
> >
> > It works AND it puts in the current time at INSERT of the new row. (I noticed
> > if you use CURRENT_TIME instead, you get the time you created the table at, for
> > every row, which is not very useful.)
> >
> > The only problem is that it doesn't change the value on an UPDATE!
> >
> > Any thoughts?
>
> Why not use the system column tmin for this purpose?
>
> hannu=> create table test(i int);
> CREATE
> hannu=> insert into test values(5);
> INSERT 17454 1
> hannu=> select tmin,tmax,i from test;
> tmin |tmax |i
> -----------------------------+-------+-
> Thu Jun 11 11:12:17 1998 EEST|current|5
> (1 row)
>
> hannu=> update test set i=2;
> UPDATE 1
> hannu=> select tmin,tmax,i from test;
> tmin |tmax |i
> -----------------------------+-------+-
> Thu Jun 11 11:13:00 1998 EEST|current|2
> (1 row)
>
> ------
> Hannu
>
One minute! what's that tmin?
What version do you have?
I have v6.3.2 and it doesn't know tmin.
prova=> create table test(i int);
CREATE
prova=> insert into test values(5);
INSERT 15650 1
prova=> select tmin,tmax,i from test;
ERROR: attribute 'tmin' not found
Jose'
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-11 15:53 Byron Nikolaidis <byronn@insightdist.com>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
1 sibling, 1 reply; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-11 15:53 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; +Cc: Hannu <hannu@trust.ee>; pgsql-interfaces
Hi,
After some testing with the ROWVER stuff, I have come up with the
following and wanted to see what people thought about it:
What I have now is if you have a timestamp type column and its field
name is "timestamp", the driver will report it as a ROWVER column in
SQLSpecialColumns. This causes Access to use it as a versioning
column. Does this seem too restrictive? Do you have other ideas of
what should constitute a versioning column? I think the field name
should be agreed upon and fixed in the driver, don't you? The other
caveat about this method is that you would have to supply a trigger to
update this 'timestamp' field on INSERT and UPDATE.
One of the things I can't get around in Access is using a column in
SQLSpecialColumns that does not appear in SQLColumns (i.e., a
pseudo-column). For example, even if we had a 'tmin' column (I know it
was removed for 6.3.2), or wanted to try using the 'xmin' column, we
couldn't use it as a pseudo column. We would have to display the
column. Access spits out an ugly message when I try to use a
pseudo-column ("Table TMP%#MAU@ already exists").
I have tested other types besides timestamps, like int4, and they seem
to work fine as a versioning column. So, we could use the postgres
'xmin' column, if we displayed it in SQLColumns. It seems to always
update so you wouldn't have to worry about a trigger. I'm not sure what
end-users would think about seeing these ugly columns in Access? Any
thoughts about that?
Please give me some feedback on this.
Byron
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-11 21:36 Byron Nikolaidis <byronn@insightdist.com>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
1 sibling, 1 reply; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-11 21:36 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; +Cc: pgsql-interfaces
Jose',
I got row versioning working with the "xmin" field. Since it is a system column, and
automatically updates, you would not need triggers all over the place on every table.
I made it a datasource option called "Row Versioning".
It works well with Access except for two minor caveats:
1) The driver has to report the 'xmin' field in SQLColumns. There is no way around
that I can see. Access, thus displays it. Users can of course hide the column if they
hate looking at it. And Access won't let you type into this field which is good.
2) You have to create an '=' operator for the xid type on the postgres side since one
does not already exist. I have the code to do it if you are interested (it is very
small). For 6.4, we would make sure this operator is added.
Byron
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-12 07:45 Hannu <hannu@trust.ee>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 27+ messages in thread
From: Hannu @ 1998-06-12 07:45 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; pgsql-interfaces
Byron Nikolaidis wrote:
>
> Hi,
...
>
> I have tested other types besides timestamps, like int4, and they seem
> to work fine as a versioning column. So, we could use the postgres
> 'xmin' column, if we displayed it in SQLColumns. It seems to always
> update so you wouldn't have to worry about a trigger. I'm not sure what
> end-users would think about seeing these ugly columns in Access? Any
> thoughts about that?
Is'nt there some way in ODBC to return some kind of _system_ columns,
like there are system tables ?
I think that PostgreSQL is not the only database that has system columns
Hannu
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-12 10:18 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 2 replies; 27+ messages in thread
From: Jose' Soares Da Silva @ 1998-06-12 10:18 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-interfaces
On Thu, 11 Jun 1998, Byron Nikolaidis wrote:
> Jose',
>
> I got row versioning working with the "xmin" field. Since it is a system column, and
> automatically updates, you would not need triggers all over the place on every table.
> I made it a datasource option called "Row Versioning".
>
> It works well with Access except for two minor caveats:
>
> 1) The driver has to report the 'xmin' field in SQLColumns. There is no way around
> that I can see. Access, thus displays it. Users can of course hide the column if they
> hate looking at it. And Access won't let you type into this field which is good.
>
> 2) You have to create an '=' operator for the xid type on the postgres side since one
> does not already exist. I have the code to do it if you are interested (it is very
> small). For 6.4, we would make sure this operator is added.
>
Ok Byron, I think this is a good solution.
In this way we don't need to add a TIMESTAMP to every table there's already a
XMIN present.
In any way, what's happen with TIMESTAMPs ? May we have Access recognize it as
DATETIME or we can't because Access beleaves that's a ROWVER ?
I would like to try it. Please Byron may you send me the code for '=' operator
for xid type.
Jose'
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-12 13:46 David Hartwig <daveh@insightdist.com>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
1 sibling, 2 replies; 27+ messages in thread
From: David Hartwig @ 1998-06-12 13:46 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; +Cc: Byron Nikolaidis <byronn@insightdist.com>; pgsql-interfaces
Here is is what you will need. I hope you know how to compile a shared lib for your
platform. This can be an adventure if you have not done it before. You will have to
adjust the path in the CREATE FUNCTION statement to your lib dir. Run the SQL after you
compile and install the shared lib. To test, do something like:
SELECT * FROM foo WHERE xmin = {some_constant}
I plan to submit a patch to make this a part of the 6.4 base release.
Jose' Soares Da Silva wrote:
> On Thu, 11 Jun 1998, Byron Nikolaidis wrote:
>
> > Jose',
> >
> > I got row versioning working with the "xmin" field. Since it is a system column, and
> > automatically updates, you would not need triggers all over the place on every table.
> > I made it a datasource option called "Row Versioning".
> >
> > It works well with Access except for two minor caveats:
> >
> > 1) The driver has to report the 'xmin' field in SQLColumns. There is no way around
> > that I can see. Access, thus displays it. Users can of course hide the column if they
> > hate looking at it. And Access won't let you type into this field which is good.
> >
> > 2) You have to create an '=' operator for the xid type on the postgres side since one
> > does not already exist. I have the code to do it if you are interested (it is very
> > small). For 6.4, we would make sure this operator is added.
> >
> Ok Byron, I think this is a good solution.
> In this way we don't need to add a TIMESTAMP to every table there's already a
> XMIN present.
> In any way, what's happen with TIMESTAMPs ? May we have Access recognize it as
> DATETIME or we can't because Access beleaves that's a ROWVER ?
> I would like to try it. Please Byron may you send me the code for '=' operator
> for xid type.
> Jose'
Attachments:
xidint4.c (text/plain, 334B) download
/* Insight Distribution Systems - System V - Apr 1998 */
static char accntnum_c[] = "@(#)accntnum.c 1.1 /sccs/sql/extend/s.accntnum.c 4/28/98 09:06:54";
#include <stdio.h> /* for sprintf() */
#include <string.h>
#include "postgres.h"
#include "utils/palloc.h"
bool
xidint4_eq(int32 arg1, int32 arg2)
{
return (arg1 == arg2);
}
xidint4.sql (text/plain, 325B) download
-- Insight Distribution Systems - System V - Apr 1998
-- %W% :%P% %G% %U%"
create function xidint4_eq(xid,int4)
returns bool
as '/ceo/pgsql/lib/xidint4.so'
language 'c';
create operator = (
leftarg=xid,
rightarg=int4,
procedure=xidint4_eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-12 13:53 Byron Nikolaidis <byronn@insightdist.com>
parent: Hannu <hannu@trust.ee>
0 siblings, 0 replies; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-12 13:53 UTC (permalink / raw)
To: hannu@trust.ee; +Cc: pgsql-interfaces
Hannu wrote:
> Is'nt there some way in ODBC to return some kind of _system_ columns,
> like there are system tables ?
>
> I think that PostgreSQL is not the only database that has system columns
>
Yes, the 'pseudo' column attribute in SQLSpecialColumns does this. I am
setting it for the xmin field, but Access never even bothers to look at it (it
never binds the column or does GetData). It is only interested in the column
name!?!
You would think that you could report system columns like 'xmin' just in
SQLSpecialColumns and not have to also specify them in SQLColumns. But as I
said, if I don't also specify the column in SQLColumns, Access barfs with the
message "Table TMP%#MAU@ already exists." when I try to open the table. The
linking phase works fine though.
It doesn't make much sense.
If you have any ideas what the heck that message means, PLEASE TELL ME!!!!
Byron
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-12 13:57 Byron Nikolaidis <byronn@insightdist.com>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
1 sibling, 1 reply; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-12 13:57 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; +Cc: pgsql-interfaces
Jose' Soares Da Silva wrote:
> Ok Byron, I think this is a good solution.
> In this way we don't need to add a TIMESTAMP to every table there's already a
> XMIN present.
> In any way, what's happen with TIMESTAMPs ? May we have Access recognize it as
> DATETIME or we can't because Access beleaves that's a ROWVER ?
> I would like to try it. Please Byron may you send me the code for '=' operator
> for xid type.
> Jose'
I added the postgres timestamp type and mapped it to SQL_TIMESTAMP, so Access recognizes it
now as a Date/Time. It does not automatically recognize it is a ROWVER. Did you read
somewhere that it did that? In my testing it never did, UNLESS, I mentioned the column in
SQLSpecialColumns and specifically said it was a ROWVER.
Byron
P.S., Dave Hartwig is sending you the code for the xid '=' operator.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-12 14:38 David Hartwig <daveh@insightdist.com>
parent: David Hartwig <daveh@insightdist.com>
1 sibling, 0 replies; 27+ messages in thread
From: David Hartwig @ 1998-06-12 14:38 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; Byron Nikolaidis <byronn@insightdist.com>; pgsql-interfaces
Oops! Here's a better way without the shared module.
-- Overload int4eq
create function int4eq(xid,int4)
returns bool
as ''
language 'internal';
create operator = (
leftarg=xid,
rightarg=int4,
procedure=int4eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);
David Hartwig wrote:
> Here is is what you will need. I hope you know how to compile a shared lib for your
> platform. This can be an adventure if you have not done it before. You will have to
> adjust the path in the CREATE FUNCTION statement to your lib dir. Run the SQL after you
> compile and install the shared lib. To test, do something like:
>
> SELECT * FROM foo WHERE xmin = {some_constant}
>
> I plan to submit a patch to make this a part of the 6.4 base release.
>
> Jose' Soares Da Silva wrote:
>
> > On Thu, 11 Jun 1998, Byron Nikolaidis wrote:
> >
> > > Jose',
> > >
> > > I got row versioning working with the "xmin" field. Since it is a system column, and
> > > automatically updates, you would not need triggers all over the place on every table.
> > > I made it a datasource option called "Row Versioning".
> > >
> > > It works well with Access except for two minor caveats:
> > >
> > > 1) The driver has to report the 'xmin' field in SQLColumns. There is no way around
> > > that I can see. Access, thus displays it. Users can of course hide the column if they
> > > hate looking at it. And Access won't let you type into this field which is good.
> > >
> > > 2) You have to create an '=' operator for the xid type on the postgres side since one
> > > does not already exist. I have the code to do it if you are interested (it is very
> > > small). For 6.4, we would make sure this operator is added.
> > >
> > Ok Byron, I think this is a good solution.
> > In this way we don't need to add a TIMESTAMP to every table there's already a
> > XMIN present.
> > In any way, what's happen with TIMESTAMPs ? May we have Access recognize it as
> > DATETIME or we can't because Access beleaves that's a ROWVER ?
> > I would like to try it. Please Byron may you send me the code for '=' operator
> > for xid type.
> > Jose'
>
> ------------------------------------------------------------------------
> /* Insight Distribution Systems - System V - Apr 1998 */
> static char accntnum_c[] = "@(#)accntnum.c 1.1 /sccs/sql/extend/s.accntnum.c 4/28/98 09:06:54";
>
> #include <stdio.h> /* for sprintf() */
> #include <string.h>
> #include "postgres.h"
> #include "utils/palloc.h"
>
> bool
> xidint4_eq(int32 arg1, int32 arg2)
> {
> return (arg1 == arg2);
> }
>
> ------------------------------------------------------------------------
> -- Insight Distribution Systems - System V - Apr 1998
> -- %W% :%P% %G% %U%"
>
> create function xidint4_eq(xid,int4)
> returns bool
> as '/ceo/pgsql/lib/xidint4.so'
> language 'c';
>
> create operator = (
> leftarg=xid,
> rightarg=int4,
> procedure=xidint4_eq,
> commutator='=',
> negator='<>',
> restrict=eqsel,
> join=eqjoinsel
> );
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-12 15:58 David Hartwig <daveh@insightdist.com>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
1 sibling, 0 replies; 27+ messages in thread
From: David Hartwig @ 1998-06-12 15:58 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; +Cc: Byron Nikolaidis <byronn@insightdist.com>; pgsql-interfaces
Byron has not yet posted the version that makes use of the xmin attribute. We have to keep on
him. He can be a real slacker.
Jose' Soares Da Silva wrote:
> I compiled and I installed successful the xid operator, I tried it
> using: SELECT * FROM foo WHERE xmin = {some_constant}
> and it works.
> I download psqlodbc.dll v06.03.0244, I tried it but seems that doesn't work yet.
> Did I forget something ?
> Thanks in any way for your work David.
> I'm going for vaccations next week, so forgive me if I don't replay for a week.
> Jose'
>
> On Fri, 12 Jun 1998, David Hartwig wrote:
> > > > 2) You have to create an '=' operator for the xid type on the postgres side since one
> > Here is is what you will need. I hope you know how to compile a shared lib for your
> > platform. This can be an adventure if you have not done it before. You will have to
> > adjust the path in the CREATE FUNCTION statement to your lib dir. Run the SQL after you
> > compile and install the shared lib. To test, do something like:
> >
> > SELECT * FROM foo WHERE xmin = {some_constant}
> >
> > I plan to submit a patch to make this a part of the 6.4 base release.
> >
> >
> >
> >
> > Jose' Soares Da Silva wrote:
> >
> > > On Thu, 11 Jun 1998, Byron Nikolaidis wrote:
> > >
> > > > Jose',
> > > >
> > > > I got row versioning working with the "xmin" field. Since it is a system column, and
> > > > automatically updates, you would not need triggers all over the place on every table.
> > > > I made it a datasource option called "Row Versioning".
> > > >
> > > > It works well with Access except for two minor caveats:
> > > >
> > > > 1) The driver has to report the 'xmin' field in SQLColumns. There is no way around
> > > > that I can see. Access, thus displays it. Users can of course hide the column if they
> > > > hate looking at it. And Access won't let you type into this field which is good.
> > > >
> > > > 2) You have to create an '=' operator for the xid type on the postgres side since one
> > > > does not already exist. I have the code to do it if you are interested (it is very
> > > > small). For 6.4, we would make sure this operator is added.
> > > >
> > > Ok Byron, I think this is a good solution.
> > > In this way we don't need to add a TIMESTAMP to every table there's already a
> > > XMIN present.
> > > In any way, what's happen with TIMESTAMPs ? May we have Access recognize it as
> > > DATETIME or we can't because Access beleaves that's a ROWVER ?
> > > I would like to try it. Please Byron may you send me the code for '=' operator
> > > for xid type.
> > > Jose'
> >
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-12 16:00 Byron Nikolaidis <byronn@insightdist.com>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
1 sibling, 0 replies; 27+ messages in thread
From: Byron Nikolaidis @ 1998-06-12 16:00 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; +Cc: David Hartwig <daveh@insightdist.com>; pgsql-interfaces
Jose' Soares Da Silva wrote:
> I download psqlodbc.dll v06.03.0244, I tried it but seems that doesn't work yet.
> Did I forget something ?
I haven't finished the new version (.0245) of the driver yet. It might be today.
Byron
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-12 17:33 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: David Hartwig <daveh@insightdist.com>
1 sibling, 2 replies; 27+ messages in thread
From: Jose' Soares Da Silva @ 1998-06-12 17:33 UTC (permalink / raw)
To: David Hartwig <daveh@insightdist.com>; +Cc: Byron Nikolaidis <byronn@insightdist.com>; pgsql-interfaces
I compiled and I installed successful the xid operator, I tried it
using: SELECT * FROM foo WHERE xmin = {some_constant}
and it works.
I download psqlodbc.dll v06.03.0244, I tried it but seems that doesn't work yet.
Did I forget something ?
Thanks in any way for your work David.
I'm going for vaccations next week, so forgive me if I don't replay for a week.
Jose'
On Fri, 12 Jun 1998, David Hartwig wrote:
> > > 2) You have to create an '=' operator for the xid type on the postgres side since one
> Here is is what you will need. I hope you know how to compile a shared lib for your
> platform. This can be an adventure if you have not done it before. You will have to
> adjust the path in the CREATE FUNCTION statement to your lib dir. Run the SQL after you
> compile and install the shared lib. To test, do something like:
>
> SELECT * FROM foo WHERE xmin = {some_constant}
>
> I plan to submit a patch to make this a part of the 6.4 base release.
>
>
>
>
> Jose' Soares Da Silva wrote:
>
> > On Thu, 11 Jun 1998, Byron Nikolaidis wrote:
> >
> > > Jose',
> > >
> > > I got row versioning working with the "xmin" field. Since it is a system column, and
> > > automatically updates, you would not need triggers all over the place on every table.
> > > I made it a datasource option called "Row Versioning".
> > >
> > > It works well with Access except for two minor caveats:
> > >
> > > 1) The driver has to report the 'xmin' field in SQLColumns. There is no way around
> > > that I can see. Access, thus displays it. Users can of course hide the column if they
> > > hate looking at it. And Access won't let you type into this field which is good.
> > >
> > > 2) You have to create an '=' operator for the xid type on the postgres side since one
> > > does not already exist. I have the code to do it if you are interested (it is very
> > > small). For 6.4, we would make sure this operator is added.
> > >
> > Ok Byron, I think this is a good solution.
> > In this way we don't need to add a TIMESTAMP to every table there's already a
> > XMIN present.
> > In any way, what's happen with TIMESTAMPs ? May we have Access recognize it as
> > DATETIME or we can't because Access beleaves that's a ROWVER ?
> > I would like to try it. Please Byron may you send me the code for '=' operator
> > for xid type.
> > Jose'
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
@ 1998-06-12 17:37 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 27+ messages in thread
From: Jose' Soares Da Silva @ 1998-06-12 17:37 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-interfaces
On Fri, 12 Jun 1998, Byron Nikolaidis wrote:
>
>
> Jose' Soares Da Silva wrote:
>
> > Ok Byron, I think this is a good solution.
> > In this way we don't need to add a TIMESTAMP to every table there's already a
> > XMIN present.
> > In any way, what's happen with TIMESTAMPs ? May we have Access recognize it as
> > DATETIME or we can't because Access beleaves that's a ROWVER ?
> > I would like to try it. Please Byron may you send me the code for '=' operator
> > for xid type.
> > Jose'
>
>
> I added the postgres timestamp type and mapped it to SQL_TIMESTAMP, so Access recognizes it
Good.
> now as a Date/Time. It does not automatically recognize it is a ROWVER. Did you read
> somewhere that it did that? In my testing it never did, UNLESS, I mentioned the column in
> SQLSpecialColumns and specifically said it was a ROWVER.
I beleave we don't need that Access recognize timestamp as a ROWVER, now that
we have an xmin that do this.
Thanks, Jose'
^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~1998-06-12 17:37 UTC | newest]
Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-09 10:18 M$-Access'97 and TIMESTAMPs Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-06-09 13:16 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-09 15:12 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-10 09:27 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-06-10 15:15 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-10 17:45 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-11 10:15 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-06-09 17:25 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-06-09 14:42 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-06-09 15:55 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-10 18:36 RE: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs Krasnow, Greg <gak@hnc.com>
1998-06-10 19:05 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-11 08:16 ` Hannu <hannu@trust.ee>
1998-06-11 11:41 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-06-11 15:53 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-12 07:45 ` Hannu <hannu@trust.ee>
1998-06-12 13:53 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-11 21:36 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-12 10:18 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-06-12 13:46 ` David Hartwig <daveh@insightdist.com>
1998-06-12 14:38 ` David Hartwig <daveh@insightdist.com>
1998-06-12 17:33 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-06-12 15:58 ` David Hartwig <daveh@insightdist.com>
1998-06-12 16:00 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-12 13:57 ` Byron Nikolaidis <byronn@insightdist.com>
1998-06-12 17:37 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-06-11 09:44 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox