[GENERAL] RE: Using Postgres with Access 2000
1) Make sure the odbc data source you are using has the read only flag checked off. 2) Create a primary key index for the table. Access97 requires a primary key or it will not allow you to edit or add any rows. -Original Message- From: Scott Teglasi [SMTP:[EMAIL PROTECTED]] Sent: Friday, January 05, 2001 11:35 AM To: '[EMAIL PROTECTED]' Subject:RE: Using Postgres with Access 2000 Let me rephrase my words. :) I have created postgresql tables with pgaccess. I am unable to add new records to those tables using Microsoft Access 2000. How do I remedy this situation, and also, is there a way to create tables within Microsoft Access 2000 on the Postgresql server. appologies for any confusion.. :) Scott Teglasi -Original Message- From: Scott Teglasi [mailto:[EMAIL PROTECTED]] Sent: Friday, January 05, 2001 1:25 PM To: '[EMAIL PROTECTED]' Subject: Using Postgres with Access 2000 Hello. I am trying to use Microsoft Access 2000 with Postgresql 7.0.2 installed on a Mandrake 7.2 box. I have configured my ODBC driver and DSN on the windows box, and have configured Postgresql to allow my machine to connect, and have installed its ODBC driver as well. I can view and edit tables, however I'm unable to add new records to any of the tables that I've created using pgaccess. My question is, how can I allow people to add records to tables using access, and also, is there a way to create new tables on the postgres server using Access as well? A reply would be greatly appreciated, thank you. Scott Teglasi
[GENERAL] RE: Table msysconf
Is there any documentation on what can be configured between PostgreSQL and Access? -Original Message- From: Jose Soares [SMTP:[EMAIL PROTECTED]] Sent: Thursday, January 04, 2001 3:59 AM To: Siuda Pawe? DI Centrala Cc: '[EMAIL PROTECTED]' Subject:Re: Table msysconf Siuda Pawe? DI Centrala wrote: > Hi !!! > > I am novice to Linux and PostgreSQL, so it is possible my question is > stupid. > Exactly I have 2 problems: > > 1) From time to time I have following info: > > ERROR: Table msysconf does not exist > > Is it any system table? > Is it important? > My database seems to be OK. I supose you are using MS-Access -->ODBC-->PostgreSQL because M$-Access checks for a table named msysconf you can optionally create this table on PostgreSQL to configure the connection between Access->PostgreSQL, if you don't have this table Access uses the default values. Jose'
[GENERAL] Numeric and money
Hello Everyone, I am in the process of migrating my Access97 application to PostgreSQL. So far everything looks great with one exception. I converted my currency fields in Access to numeric(9,2) as recommended in the PostgreSQL documentation. Many things to don't play well with the numeric the data type. Here are some examples: create table tst (id int, amount numeric(9,2)); insert into tst values (1, 1.10); insert into tst values (2, 1.00); insert into tst values (2, 2.00); select * from tst where amount = 1; -- works select * from tst where amount = 1.1; -- fails select * from tst where amount = 1.10; -- fails select amount::varchar from tst; -- fails select amount::money from tst; -- fails select id || ', ' || id from tst; -- works select id || ', ' || amount from tst; -- fails >From within Access, I can't update any table with a numeric data type because of the "select * from tst where amount = 1.1;" failure. These limitations have caused me to wonder what other PostgreSQL users are using for their money values? Is numeric(9,2) the best choice for money? I think that adding numeric to text and text to numeric operators will fix most of these issues. I plan to add these operators very soon and thought I would ask if anyone has done this before and could provide me an example or two before I start. Does anyone know of any internal functions that already exist to convert numeric to text so that I don't have to write one? I know that psql successfully does this. Thanks, Michael Davis Database Architect and Senior Software Engineer, Seva Inc. Office: 303-460-7360Fax: 303-460-7362 Mobile: 720-320-6971 Email: [EMAIL PROTECTED]
[GENERAL] RE: sequences and tables
Try: --This shows all default values for columns SELECT DISTINCT c.relname, a.attname, t.typname, pa.adsrc, a.atttypid FROM pg_attribute a, pg_class c, pg_type t, pg_attrdef pa WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND c.oid = pa.adrelid AND a.attnum = pa.adnum AND upper(c.relname) = 'TABLE_NAME'; --This shows all columns that reference a sequence SELECT DISTINCT c.relname, a.attname, t.typname, pa.adsrc, a.atttypid FROM pg_attribute a, pg_class c, pg_type t, pg_attrdef pa WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND c.oid = pa.adrelid AND a.attnum = pa.adnum AND upper(pa.adsrc) like '%SEQUENCE_NAME%'; -Original Message- From: Nelio Alves Pereira Filho [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, January 03, 2001 7:13 AM To: [EMAIL PROTECTED] Subject:sequences and tables Where do I find information about a sequence? I'd like to know what are the table and column referenced by a sequence. As its name may be truncated, it's not a consistent place to look for. The system tables didn't seem to help. Thanks -- Nelio Alves Pereira Filho IFX Networks - www.ifx.com.br +55 11 3365-5863 [EMAIL PROTECTED]
[GENERAL] pg_dump/psql < db.out issue
I have several tables with text fields that contain single quotes (').
Pg_dump exports these tables and the single quotes (') okay. Psql,
however, will not import the data into the tables because of the single
quote (') in one of the columns in one of the records. Any idea how to
work around this other than using the -d/-D option in pg_dump? I like the
-d option in pg_dump, however, importing the data via pgsql is much slower
with this option.
Thanks in advance, Michael Davis
Re: [GENERAL] postgreSQL for storing a database of documents
Your e-mail did not arrive at its intended destination. You need to send it to Michael J. Davis, not Michael Davis From: dustin sallings on 05/05/99 01:57 AM To: mtony @ iname.com@SMTP@EXCHANGE cc: pgsql-general @ postgreSQL.org@SMTP@EXCHANGE Subject:Re: [GENERAL] postgreSQL for storing a database of documents On Tue, 4 May 1999, Mark Fleming wrote: // That a good solution, but now that XML is starting to break off it // kinda puts PostgreSQL users at a disadvantage. We have to have // static XML documents on on disk in order to use the format and have // external search engines to search for content in the documents. It // is not that big of a headache, but is an inconvenience. I will be // doing some work on XML soon for my job and they are going straight // Oracle because they want to create "searchable" XML documents on the // fly to post and distribute on the web. Very LARGE documents. Like // the legal code for NYC and such. When Postgre starts using large // objects it is going to make a lot of noise. Especially for // "start-up" companies. What do you mean when Postgres starts using large objects? It has for quite some time now. I chose not to use it because I don't like the way the data is stored on disk, though. -- Principal Member Technical Staff, beyond.comThe world is watching America, pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <[EMAIL PROTECTED]> |Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE L__ and America is watching TV. __
Re: [GENERAL] postgreSQL for storing a database of documents
Your e-mail did not arrive at its intended destination. You need to send it to Michael J. Davis, not Michael Davis. From: Mark Fleming on 05/04/99 02:23 PM To: pgsql-general @ postgreSQL.org@SMTP@EXCHANGE cc: Subject:Re: [GENERAL] postgreSQL for storing a database of documents Ari Halberstadt wrote: > Raphael Finkel <[EMAIL PROTECTED]> wrote: > >Is PostgreSQL appropriate for a database of documents? I see several > >potential problems. > > > >1. Tuples are limited to 8KB. I assume even the "text" type is limited that > >way. But my documents may be longer than that. > > This would pretty much nix the idea of using postgres for a document > repository. You'd need to split the data into separate rows in another > table. I've done this for my bulletin board (though most posts should be > less than 8K in my case), but I think it would be even more of a hastle > with documents. What you can do instead is store descriptive information in > the DB and store the actual documents in the file system with a field in > the document table containing the path to the file. A few years ago I > worked on a commercial document management program that stored attributes > in an Oracle database but kept the actual documents on disk. > That a good solution, but now that XML is starting to break off it kinda puts PostgreSQL users at a disadvantage. We have to have static XML documents on on disk in order to use the format and have external search engines to search for content in the documents. It is not that big of a headache, but is an inconvenience. I will be doing some work on XML soon for my job and they are going straight Oracle because they want to create "searchable" XML documents on the fly to post and distribute on the web. Very LARGE documents. Like the legal code for NYC and such. When Postgre starts using large objects it is going to make a lot of noise. Especially for "start-up" companies. -- Mark Fleming Student ICQ# 16171200 e-mail: [EMAIL PROTECTED] http://www.globaldrum.com/mfleming http://coolbox.bowiestate.edu/~mfleming
Re: [GENERAL] advice on buying sun hardware to run postgres
Your e-mail did not arrive at its intended destination. You need to send it to Michael J. Davis, not Michael Davis. From: Bruce Momjian on 05/04/99 12:39 PM To: Chris Bitmead @SMTP@EXCHANGE cc: pgsql-general @ postgreSQL.org@SMTP@EXCHANGE Subject:Re: [GENERAL] advice on buying sun hardware to run postgres > Bruce Momjian wrote: > > > There is. Linux/*BSD use only a big kernel lock, or > > several big kernel locks. > > I think you're talking silly. BSD has one big lock, but my count there > are around 60 locks in Linux 2.2. Solaris has thousands of locks. > > OK, *BSD has one, Linux has 60, and Solaris has thousands. This was my point. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
RE: [GENERAL] How to get seq after insert
The safest way is to select the nextval('seq_name') and then insert using
this value.
-Original Message-
From: Brian [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, April 14, 1999 2:59 PM
To: [EMAIL PROTECTED]
Subject:[GENERAL] How to get seq after insert
I have a sequence in a table that increments upon insert. After
doing the
insert, is their a way (function maybe?) to get the sequences value
without having to do another select?
Brian
-
Brian Feeny (BF304) [EMAIL PROTECTED]
318-222-2638 x 109 http://www.shreve.net/~signal
Network Administrator ShreveNet Inc. (ASN 11881)
RE: [GENERAL] pg_dump error
Try vacuum. You could also try dropping and recreating your indexes. -Original Message- From: Stefano Zampieri [SMTP:[EMAIL PROTECTED]] Sent: Saturday, April 03, 1999 3:56 AM To: [EMAIL PROTECTED] Subject:[GENERAL] pg_dump error hi, I've experienced an error while doing pg_dump dbname > dump_file "failed sanity check, type with oid 145670 was not found" the same error for pg_dump -s no errors with pg_dump -t tablename for each table of the db Does anyone know what this mean and how to fix it? -postgres 6.3.2 RH linux 4.2 Thank you in advance --Stefano
RE: [GENERAL] limit select question
It entered into beta test this week. There are several features that I really need so am jumping in early. You have to know how/where to get the source code because it will not even be available for download until next week. -Original Message- From: Kevin Heflin [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, March 31, 1999 12:30 AM To: Michael Davis Cc: PGSQL-General (E-mail) Subject:RE: [GENERAL] limit select question On Wed, 31 Mar 1999, Michael Davis wrote: > Good point. I am running 6.5. So it has been fixed. I am not sure what > patch is needed to make this work on 6.4.2. Is version 6.5 to be had some where, or is it only in beta? I sure could use that feature on a project I'm working on right now... Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
RE: [GENERAL] limit select question
Good point. I am running 6.5. So it has been fixed. I am not sure what patch is needed to make this work on 6.4.2. -Original Message- From: Kevin Heflin [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, March 31, 1999 12:26 AM To: Michael Davis Cc: PGSQL-General (E-mail) Subject:RE: [GENERAL] limit select question On Wed, 31 Mar 1999, Michael Davis wrote: > Select * From table_name limit 1; I'm running postgres6.4.2, and this query fails with: ERROR: parser: parse error at or near "1" I was told I could gain this feature by adding the 6.4.2 feature patch. But when I tried installing the patch, anything and everything I tried in psql failed with an error on 'select' Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
RE: [GENERAL] limit select question
Select * From table_name limit 1; -Original Message- From: Kevin Heflin [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, March 30, 1999 10:00 PM To: PGSQL-General (E-mail) Subject:[GENERAL] limit select question I've been asked if there is a way to LIMIT the number of results displayed when doing a SELECT with postgresql, by a former user of mysql. Wasn't sure if there is an easy way to do this or not. I've don't it before, but in a very round about way... any suggestions. > with mySQL and SELECT queries > you can LIMIT the number of results displayed to the screen. I can't > seem to find any such switch in the SELECT commands in postgres. Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
RE: [GENERAL] using Oids to retrieve a row
I use the following technique: add sequence to the primary key of the table look up the sequence the primary key insert a record using (including) the primary key re-query the newly inserted record using the primary key Thanks, Michael -Original Message- From: David O'Farrell [SMTP:[EMAIL PROTECTED]] Sent: Friday, March 26, 1999 5:39 AM To: [EMAIL PROTECTED] Subject:[GENERAL] using Oids to retrieve a row anyone know if it is possible to INSERT a row , "save" the oid for this row and subsequently select it based only on the oid ? -- ~~ David O'Farrell AerSoft Limited mailto:[EMAIL PROTECTED] 2 Northumberland Avenue, Dun Laoghaire,Co. Dublin Direct Phone 353-1-2145950 Phone: 01-2301166 Fax: 01-2301167 ~~
RE: [GENERAL] Comments on columns?
I created functions to add comments based on the table and column name rather than using the oids. I include these calls as part of my table creation scripts. I assume that by initdb you mean when the database is created than when Postgres is started. If the oids can change every time you start Posgtres, this would be a problem. I can upload my functions for integrating into Postgres as a standard way to add comments (if requested). Maybe we should consider attaching descriptions to a table and or column name rather than an oid? On another note, I have been following the thread about passing NULL parameters into a C function call. What is happening with this? If a parameter of a C function call contains NULL, the function is either not called or returns NULL. I would like to change this so that my C function can return a value even though its parameters are NULL. Thanks, Michael -Original Message- From: Bruce Momjian [SMTP:[EMAIL PROTECTED]] Sent: Friday, March 12, 1999 11:31 AM To: Michael Davis Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Comments on columns? > I did that and it works great. I would like \dd tablename to show the table > comment and its column comments. Yes, I would like this too, and have considered it. The problem I have had with system tables is that the oid of the table and columns is generated at initdb time, and not assigned constant values as the pg_proc entries are. I could modify psql to show comments, but I can't figure out how to get the system tables to show this. In fact, the TODO list has: * allow pg_descriptions when creating types, tables, columns, and functions -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
RE: [GENERAL] Comments on columns?
I did that and it works great. I would like \dd tablename to show the table comment and its column comments. -Original Message- From: Bruce Momjian [SMTP:[EMAIL PROTECTED]] Sent: Friday, March 12, 1999 7:43 AM To: Michael Davis Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Comments on columns? > Are there any plans to have psql support comments on columns? For example, > I would like to be able to add a comment on a column of a table and have \dd > display the table comment in addition to any/all column comments. Find the oid of the column in pg_attribute, and insert into pg_description. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] Comments on columns?
Are there any plans to have psql support comments on columns? For example, I would like to be able to add a comment on a column of a table and have \dd display the table comment in addition to any/all column comments. Thanks, Michael
RE: [GENERAL] SQL Question
Yes, the "having" clause is supported in 6.4.2 with some restrictions as outlined in the documentation on the web site. -Original Message- From: Matthew [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, March 09, 1999 10:46 PM To: [EMAIL PROTECTED] Subject:[GENERAL] SQL Question I am using RH5.2 / Postgre 6.3.2 I need a query that has a having clause. In 6.3.2 it says that having is not supported yet. I looked at the changes in 6.4 and it appears that 6.4.2 supports this. Is this true? Also I have found 6.4.2 rpms but no data rpm so now when I try to use psql it says pgReadData() -- backend closed the channel unexpectedly. Please help. Matt
[GENERAL] Size of a table in bytes?
Does anyone have a query or function that will return the size (in bytes) of a table?
[GENERAL] Questions about functions and loops
1) When I run the function below I get an error at "select". If I put the select statement in single quotes, I get an error at "loop". Any suggestions how to fix this? 2) When I install the following function: CREATE FUNCTION InstallPermissions(varchar, varchar) RETURNS int2 AS ' DECLARE options ALIAS FOR $1; username ALIAS FOR $2; BEGIN FOR row IN select * from pg_tables LOOP REVOKE ALL ON row.tablename FROM username; GRANT options on row.tablename TO username; END LOOP; return 0; END; ' LANGUAGE 'plpgsql'; I get a dir listing displayed in the psql window between each statement. See the output below. It makes the output verbose and difficult to read. This happens when installing the function, not when running the function. Any idea what could be causing this and how to fix it? Here is an example of the output (scaled back for brevity, the list of files is quite a bit larger): mp=> CREATE FUNCTION InstallPermissions(varchar, varchar) RETURNS int2 AS ' mp'> DECLARE mp'> .Xdefaults .bash_logout .bashrc .screenrc ... mp'> options ALIAS FOR $1; mp'> .Xdefaults .bash_logout .bashrc .screenrc ... mp'> username ALIAS FOR $2; mp'> BEGIN mp'> FOR row IN ''select * from pg_tables'' LOOP mp'> .Xdefaults .bash_logout .bashrc .screenrc ... mp'> .Xdefaults .bash_logout .bashrc .screenrc ... mp'> .Xdefaults .bash_logout .bashrc .screenrc ... mp'> REVOKE ALL ON row.tablename FROM username; mp'> .Xdefaults .bash_logout .bashrc .screenrc ... mp'> .Xdefaults .bash_logout .bashrc .screenrc ... mp'> .Xdefaults .bash_logout .bashrc .screenrc ... mp'> GRANT options on row.tablename TO username; mp'> .Xdefaults .bash_logout .bashrc .screenrc ... mp'> END LOOP; mp'> .Xdefaults .bash_logout .bashrc .screenrc ... mp'> return 0; mp'> END; ' LANGUAGE 'plpgsql'; CREATE Thanks, Michael
RE: [GENERAL] Tree structure
You could try select spaces(pointer::int4) || Number; Where spaces() is a function that inserts "pointer" number of spaces (or dashes if you want to create your own function). Not sure if spaces() exists in Postgres or not, but it seems I read about it or a similar function somewhere. -Original Message- From: Kaare Rasmussen [SMTP:[EMAIL PROTECTED]] Sent: Friday, February 26, 1999 1:06 AM To: [EMAIL PROTECTED] Subject:[GENERAL] Tree structure I can't figure this one out. I need a tree structure like this Number Pointer 10 21 31 42 50 61 75 This should somehow show up like this Number 1 2 4 3 6 5 7 The whole excercise is because I'd like to show a tree structure: 1 - 2 - - 4 - 3 - 6 5 - 7 Is this possible with PostgreSQL?
[GENERAL] Comments on tables, functions, etc.
How can I add a comment to a table, function, etc. that will should up in a /dd comment in psql?
RE: [GENERAL] Checkboxes on MSAccess and PostgreSQL
I wrote a function that takes a boolean parameter and returns int2.
-Original Message-
From: Valerio Santinelli [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, February 24, 1999 1:10 PM
To: Michael Davis
Cc: [EMAIL PROTECTED]
Subject:Re: [GENERAL] Checkboxes on MSAccess and PostgreSQL
That's the same thing I did today to get rid of the boolean problem.
Now I've
got another one ;)
I want to use my data contained in the table filled with boolean
stuff and I'd
like to convert all the booleans to int2. I know there's a way to do
that by
doing something like:
INSERT INTO mynewtable SELECT field1, field2, (booleanfield='t'),
field3,...
FROM myoldtable;
but "(booleanfield='t')" gives another boolean value. I think I
should typecast
it into an int2 but I don't know how to do that since there isn't a
function
liek a bool_to_int2.
Any ideas ?
Michael Davis wrote:
> I defined my Access boolean fields as int2 in Postgres. This, in
> combination with removing the "Bools as Char" flag in the ODBC
driver
> appears to work. However, I have not been able to much testing.
>
> -Original Message-
> From: Valerio Santinelli [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, February 24, 1999 4:25 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED];
> [EMAIL PROTECTED]
> Subject:Re: [GENERAL] Checkboxes on MSAccess and
PostgreSQL
>
> Jose' Soares wrote:
>
> > Valerio Santinelli wrote:
> > >
> > > First of all.. thanks to everybody for helping me out
witht the
> > > "sequence" stuff. :)
> > >
> > > I've got another questions for you dudes. When using
a CheckBox
> in a
> > > Form written in MSAccess that's related to a table in
a
> > > PostgreSQL database, if I simply turn its status from
ON to OFF
> it works
> > > fine, while if i'm doing the opposite it won't work.
> > >
> > > I noticed that when exporting the table from MSAccess
to
> PostgreSQL the
> > > "yes/no" fields all became char(1) and not boolean..
maybe this
> could be
> > > the problem.
> > >
> > > I also noticed from the logs that when updating the
status from
> ON to
> > > OFF the UPDATE goes out with something like
field_name='0'
> > > I'm not sure it should use the "'" since it's more
like a
> numerical, no
> > > ?
> > >
> > > Thanks again,
> > >
> > > Valerio Santinelli
> > > [EMAIL PROTECTED]
> >
> > You have to uncheck "Bools as Char" on Advanced options
Driver on
> ODBC
> > Data Source Administrator
> > and then you have to create an operator for bool=int4
like this:
> >
> > /* M$Access tratta il valore booleano come un intero
0=false o
> -1=true
> >mentre invece PostgreSQL lo tratta come una stringa:
> >'true','t','1','y','yes','false','f','0','n','no'
> >Questo script crea l'operatore = e implicitamente <>
per bool e
> int4
> >per compatibilita' con M$Access.
> > */
> > drop operator = (bool,int4);
> > drop function MsAccessBool(bool,int4);
> >
> > create function MsAccessBool(bool,int4)
> > returns bool
> > as ''
> > language 'internal';
> >
> > create operator = (
> > leftarg=bool,
> > rightarg=int4,
> > procedure=MsAccessBool,
> > com
RE: [GENERAL] Checkboxes on MSAccess and PostgreSQL
I defined my Access boolean fields as int2 in Postgres. This, in combination with removing the "Bools as Char" flag in the ODBC driver appears to work. However, I have not been able to much testing. -Original Message- From: Valerio Santinelli [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, February 24, 1999 4:25 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject:Re: [GENERAL] Checkboxes on MSAccess and PostgreSQL Jose' Soares wrote: > Valerio Santinelli wrote: > > > > First of all.. thanks to everybody for helping me out witht the > > "sequence" stuff. :) > > > > I've got another questions for you dudes. When using a CheckBox in a > > Form written in MSAccess that's related to a table in a > > PostgreSQL database, if I simply turn its status from ON to OFF it works > > fine, while if i'm doing the opposite it won't work. > > > > I noticed that when exporting the table from MSAccess to PostgreSQL the > > "yes/no" fields all became char(1) and not boolean.. maybe this could be > > the problem. > > > > I also noticed from the logs that when updating the status from ON to > > OFF the UPDATE goes out with something like field_name='0' > > I'm not sure it should use the "'" since it's more like a numerical, no > > ? > > > > Thanks again, > > > > Valerio Santinelli > > [EMAIL PROTECTED] > > You have to uncheck "Bools as Char" on Advanced options Driver on ODBC > Data Source Administrator > and then you have to create an operator for bool=int4 like this: > > /* M$Access tratta il valore booleano come un intero 0=false o -1=true >mentre invece PostgreSQL lo tratta come una stringa: >'true','t','1','y','yes','false','f','0','n','no' >Questo script crea l'operatore = e implicitamente <> per bool e int4 >per compatibilita' con M$Access. > */ > drop operator = (bool,int4); > drop function MsAccessBool(bool,int4); > > create function MsAccessBool(bool,int4) > returns bool > as '' > language 'internal'; > > create operator = ( > leftarg=bool, > rightarg=int4, > procedure=MsAccessBool, > commutator='=', > negator='<>', > restrict=eqsel, > join=eqjoinsel > ); Some time ago I got this message and I tried to make booleans work with my database, but there's been no way to do that. First of all, during the "DROP MsAccessBool" my PostgreSQL database engine reports that the function can't be dropped because it's an internal (built-in). Second, it seems to replace it by creating the function (i don't know how it could if the function already exists..) but then again when I'm updating a record in a table with booleans it simply doesn't let me do that. I'm desperately seeking help now.. I don't really know how to solve this problem. Thanks in advance Valerio Santinelli [EMAIL PROTECTED]
RE: [GENERAL] Replication of databases (esp. postgres)
I was thinking about creating a trigger on every table that would write an
insert, update, or delete statement into a log file. Each replicated
database would pro-actively read these and post them.
-Original Message-
From: Thomas Antepoth [SMTP:[EMAIL PROTECTED]]
Sent: Monday, February 15, 1999 9:16 PM
To: [EMAIL PROTECTED]
Subject:Re: [GENERAL] Replication of databases (esp.
postgres)
Dustin,
thank you for your answer.
On Mon, 15 Feb 1999, dustin sallings wrote:
> // {Replication of r/w Databases in a two pass scheme w/
renumbering}
> Just one note. Renumbering isn't all that important. I did
one
> that had a non-numeric unique ID for identity type columns.
I thought of a field named db_id. This field combined with
a unique (at least to the local database) counter should give a
unique index to each relation.
The rule to follow is: Every record in a relation with the
same db_id and the same rec_id belongs together.
But think of this situation:
DB 1 gets a relation.
table1table2
record1 record1
record2
Now this relation gets replicated to DB 2.
After this is done, DB 2 adds a record3 to
table2 this relation.
Question: Which db_id should DB 2 use?
His own? In this way record3 wouldn't belong
to this relation.
The id of DB 1? In this way the data may
not be unique to DB 1 as DB 1 might enter
the same values for record3 for a completely
other relation and a collision is created.
So i dropped the idea of having a db_id field
to identify the record of a relation w/o
renumbering the relations by a master.
have a nice day! t++
