Re: [GENERAL] Using MS Access front-end with PG

[Edward Macnaghten]

Paul Lambert wrote:
I've got an MS Access front end reporting system that has previously 
used MS SQL server which I am moving to Postgres.


Are you using "PassThrough" queries?  It is not clear


The front end has several hundred if not thousand inbuilt/hard-coded 
queries, most of which aren't working for the following reasons:
1.) Access uses double quotes ("") as text qualifiers, PG uses single 
quotes. ('')


What Access uses should not make any  difference.  In pass through 
queries I as MS-SQL uses single quotes same as PostGres, and "attached 
table" queries all this gets transalated at the Access to ODBC layer. 


2.) The Like function in SQL Server is case insensitive, PG it is case 
sensitive. The ilike function is not recognised by Access and it tries 
to turn that into a string, making my test (like "ilike 'blah'")


The only way the "ilike" can be passed from ACCESS to Postgres is 
through pass through queries.  This is probably not what you want 
though.  I do not actually know how MS-Access translates the "Like" 
operator at the ACCESS-> ODBC layer (probably just converts the search 
string to use % and _ from * and ?).  I do not know if it is possible to 
switch off case sensitivity in Postgres though



Has anyone had any experience with moving an access program from SQL 
server to PG?


Yes, but some time ago.  I did not have the case sensitivity problem as 
I knew that was a non-standard feature and did not rely on it while 
developing the MS-SQL solution though (just call me smartypants :-)).  I 
still had to change a few things though (I cannot remember what, sorry).


Eddy

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Using MS Access front-end with PG]

[Edward Macnaghten]

Joshua D. Drake wrote:

You could preface all your queries with something like:

select * from foo where lower(bar) = lower('qualifer');

But that seems a bit silly.


And also it would prevent the optimizer from using any indexes on 
"bar".  Not a good idea.


Eddy


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] problems with currval and rollback

[Edward Macnaghten]

Without seeing the SQL statements you executed in the session it is 
difficult to see your problem.


Getting a current sequence after a rollback is no problem (in 8.0 
anyway).  Please note though, the sequence itself is NOT rolled back.  
This is correct behaviour.  Currval will return the last sequence 
retrieved for the session (using nextval),  even if other sessions have 
accessed the sequence in the intervening time.


Eddy


Assad Jarrahian wrote:


Hi all,
  So I started to make some changes with my code here and there
(nothing to do with the relevant lines) and suddenly currval and
rollback don't work.


 





What happens is that the it inserts a db, but cannot get a value back
using currval  (ERROR: ERROR: currval of sequence "lm_id_seq" is not
yet defined in this session 55000  ) , yet the db gets the addlm
inserted.


Shouldn't it rollback?
Furthermore, why would currval suddenly stop working?

Much thanks for your feedback in advance.

-assad
 




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] MS Access to PostgreSQL

[Edward Macnaghten]

William Shatner wrote:
I have recently migrated from MS Access to PostgreSQL.Previously I had
a SQL command
  ResultSet aGroupResultSet = aGroupPathStmt.executeQuery(
  "SELECT \"groupID\",\"fullpath\" FROM \"groups\" WHERE
\"fullpath\" Like '" +
  aPath + "'");

where aPath was equal to  'folder\another folder\%'.

Are you sure?  In MS-Access JET engine it uses the '*' character instead 
of the '%' one as a "like" wildcard.

Two things you can try...  If you have attached the table "groups" in 
MS-Access and are using it through JET (as the code you provided would 
suggest) then try changing the "%" character to "*" - the JET  engine 
will convert that to % for you, whereas it may escape the "%" character 
you have supplied to keep the behaviour the same as JET.

The other possibility is to use the "dbPassThrough" parameter and 
execute it as a pass through query, here the SQL is sent to the 
PostgreSQL engine unchanged.

All in all I am sure this is an MS-Access problem rather than a Postgres 
one.

Eddy

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] How best to represent relationships in a database generically?

[Edward Macnaghten]

Lincoln Yeoh wrote:

Hi, importantly do searches and other processing by those relationships.

So, what would be the best way to store them so that a search for the 
relationship like "grass is to cow", will also turn up cow is to 
tiger, and goat is to tiger, and fish is to penguin (and penguin is to 
bigger fish ;) ), and electricity is to computer. And a search for cow 
is to goat, could turn up tiger is to lion, and goat is to cow.


Is the only way to store all the links explicitly? e.g. have a huge 
link table storing stuff like obj => cow, subj => grass, type => 
consumes, probability=90% ( => means points/links to). Or even just 
have one table (links are objects too).


Hi

This is a generic database design problem rather than a Postgres or SQL 
one, but here goes


Excuse ASCII art..

What you really have is a multi - multi relationship, such as


   A  <--->  B


Where A is a table containing grass, cow, fish
and B is the table containing cow, tiger and penguin

I know, A and B are the same table, so the multi - relationship is in fact

  A <> A

As you cannot have a multi-multi relationship in a RDBMS, you need a 
"link" table...


 A  ---> C  C < --- A

This would be represented as tables as something like

create table thingy (
  thingy_key varchar(12)  primary key,
  thingy_desc  varchar(30)
  
 );
or whatever

and...

create table munchies (
 eater varchar(12) not null,
 dinner varchar(12)  not null
 probablility_pc number(4,2)
 constraing pkey_munchies primary key(eater, dinner) );

or whatever, where "eater" and "dinner" are foreign keys for "thingy_key"

The munchies table can get big, but do not worry about that.  It is 
small and RDBMS (especially Postgres) should handle it well even on a 
smallish machine.


Hope that makes sense

Eddy



---(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