Thread: RE: pgsql-interfaces-digest V1 #562

RE: pgsql-interfaces-digest V1 #562

From
"Brian Haney"
Date:
I'm a newbie to PostgreSQL ODBC applications, so please forgive me if the is
a FAQ.

I'm writing a VBA/Excel app (new to that, too) and am having great success
with retrieving data from PostgreSQL, but the recordsets (using DAO) are
flagged as not Updatable.  I have made sure the the ODBC driver read-only
flag is set to off/false/zero, but no luck.  I'm sure it's just my
ignorance, but where else should I look for the cause of getting
non-updatable recordsets?

--Brian Haney

> -----Original Message-----
> From: owner-pgsql-interfaces-digest@hub.org
> [mailto:owner-pgsql-interfaces-digest@hub.org]
> Sent: Saturday, November 27, 1999 8:03 PM
> To: pgsql-interfaces-digest@hub.org
> Subject: pgsql-interfaces-digest V1 #562
>
>
>
> pgsql-interfaces-digest  Saturday, November 27 1999  Volume 01 :
> Number 562
>
>
>
> Index:
>
> Re: [INTERFACES] Slow join query optimisation?
> Re: [INTERFACES] Spanish format on date and numbers
> datetime, JDBC, and MS Access
>
> ----------------------------------------------------------------------
>
> Date: Sat, 27 Nov 1999 00:36:41 -0500
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [INTERFACES] Slow join query optimisation?
>
> Douglas Thomson <dougt@mugc.cc.monash.edu.au> writes:
> > My question for the list: Is there something in the query optimiser
> > that will take a time that is something like exponential on the number
> > of tables being joined?
>
> Yes.  See the manual's discussion of genetic query optimization.  You
> might try setting the GEQO threshold to something less than 10.
>
> FWIW, the time you are looking at here is strictly planning time and
> will not increase when you actually put data in the tables.  Unless
> you foresee fairly small final tables, the planning time is not likely
> to be a significant factor in this range of query sizes.
>
> Still, if the purpose of the join is just to substitute descriptions
> for IDs, you'd be well advised to consider doing it via table lookup
> on the frontend side, assuming your frontend is coded in a language
> that makes that reasonable to do.  The Postgres optimizer cannot
> help but waste many cycles per query reverse-engineering your intent,
> even assuming that it ultimately comes up with the best possible
> query plan for the join...
>
>             regards, tom lane
>
> ------------------------------
>
> Date: Sat, 27 Nov 1999 13:34:02 +0000 (GMT)
> From: "Patrick Welche" <prlw1@newn.cam.ac.uk>
> Subject: Re: [INTERFACES] Spanish format on date and numbers
>
> Oscar Serrano wrote:
>
> > And it seems that I must first define and export the variables
> LANG, LC_ALL,
> > LC_CTYPE (and probably LC_COLLATE).
> > The LANG variable seems to must be: LANG="es_ES", for spanish
> configuration.
> > The LC_CTYPE must be something like this: LC_CTYPE="ISO8859-1".
> But, what is
> > the ISO for spain?
>
> Vague memory tells me it's ISO8859-2. I use LC_CTYPE=iso_8859_1, as in my
> /usr/share/locale directory (this is under NetBSD) I have
>
> % ls /usr/share/locale
> de         es         iso_8859_1 nl         pl         sl
> el         fr         ko         no         ru         sv
> % ls /usr/share/locale/iso_8859_1
> LC_CTYPE
>
> I don't know where I would find nor how to write an appropriate LC_CTYPE
> file - never needed to, and never tried --enable-locale - I just
> set LC_CTYPE
> for nice keyboard sequences.
>
> > So, if I define this variables and use the --enable-locale, the
> psql will
> > return the numbers and dates in the spanish format?
> > I can't believe it.
>
> There's only one way to find out!
>
> Good luck,
>
> Patrick
>
> ------------------------------
>
> Date: Sat, 27 Nov 1999 21:44:44 +0100
> From: "Oscar Serrano" <oserra@fondos.net>
> Subject: datetime, JDBC, and MS Access
>
> Hi:
> Probably someone of you can tellme what am I doing wrong:
>
> I create a table in psql, with an unique column of the type
> "date" wich name
> is "columdate".
> Then, via JDBC, I try to put the content of that column in a Java Date
> object. OK, no problem. I write this:
>
> Date mydate=rs.getDate("columndate");
>
> Now I can operate with mydate normaly.
>
> But the PROBLEM is when in psql, the column "columndate" is of the type
> "datetime". In that case, the java sentence puts null to mydate.
>
> Why as "date" works and as "datetime" it doesn't work?
>
> If also tried this:
> Timestamp mydate=rs.getTimestamp("columndate");
>
> but it doesn't work.
>
> I would define de column as "date" the problem is solved, but,
> you know, the
> tables are created exporting them from MS Access via ODBC, and
> when exported
> so, postgresql converts to "datetime" all columns that in MS
> Access were of
> the type "Date/Hour".
>
> I could get my problem solved if some of you knows how to tell Access to
> export "Date/Hour" types to postgresql "date" in side of  "datetime".
>
> Thank you very much.
>
> ------------------------------
>
> End of pgsql-interfaces-digest V1 #562
> **************************************
>
>
> ************
>



VBA and ODBC (was Re: [INTERFACES] RE: pgsql-interfaces-digest V1 #562)

From
Byron Nikolaidis
Date:

Brian Haney wrote:
> 
> I'm a newbie to PostgreSQL ODBC applications, so please forgive me if the is
> a FAQ.
> 
> I'm writing a VBA/Excel app (new to that, too) and am having great success
> with retrieving data from PostgreSQL, but the recordsets (using DAO) are
> flagged as not Updatable.  I have made sure the the ODBC driver read-only
> flag is set to off/false/zero, but no luck.  I'm sure it's just my
> ignorance, but where else should I look for the cause of getting
> non-updatable recordsets?
> 
> --Brian Haney
> 

Do you have the right kind of recordset?  The only updateable recordset
I think is a dynaset.  For the odbc driver to be able to use it, you
probably have to use the cursor library, since the driver does not deal
with keysets internally.  So there should be a setting in VBA somewhere
to tell it use the cursor library.

You should have the "Use Declare/Fetch" flag disabled.   

You probably need to have the "recognize unique indexes" set to enabled,
and of course you need a physical unique index on the table in question.

Byron

************




VBA and ODBC (was Re: [INTERFACES] RE: pgsql-interfaces-digest V1 #562)

From
Byron Nikolaidis
Date:

Brian Haney wrote:
> 
> I'm a newbie to PostgreSQL ODBC applications, so please forgive me if the is
> a FAQ.
> 
> I'm writing a VBA/Excel app (new to that, too) and am having great success
> with retrieving data from PostgreSQL, but the recordsets (using DAO) are
> flagged as not Updatable.  I have made sure the the ODBC driver read-only
> flag is set to off/false/zero, but no luck.  I'm sure it's just my
> ignorance, but where else should I look for the cause of getting
> non-updatable recordsets?
> 
> --Brian Haney
> 

Do you have the right kind of recordset?  The only updateable recordset
I think is a dynaset.  For the odbc driver to be able to use it, you
probably have to use the cursor library, since the driver does not deal
with keysets internally.  So there should be a setting in VBA somewhere
to tell it use the cursor library.

You should have the "Use Declare/Fetch" flag disabled.   

You probably need to have the "recognize unique indexes" set to enabled,
and of course you need a physical unique index on the table in question.

Byron

************