Thread: iodbc interface on Unix

iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
Hello Byron, welcome back from vacation!

So, I'm working on trying to get the iodbc interface playing with
ApplixWare on my Linux box. I'm using a current snapshot of the Postgres
development tree, and your newest source code, which you announced
yesterday.

OK, so there are some minor problems in compiling for the Unix
environment, most of which I've fixed or worked-around. The biggest
minor problem, and easiest to fix, is that there is an

  #include <config.h>

in most of the files. I believe that this should be changed to

  #include "config.h"

to allow the compiler to pick up the file from the local directory.
Otherwise, it is interpreted as a "system-ish" file and (with my
Makefile, using many of the compiler options from the Postgres
distribution) finds the config.h in the Postgres distribution instead.

There is some funny-business with "HINSTANCE", but I've #define'd it to
be the same as a pointer to void.

As was the case a few weeks ago, I'm now able to start to initiate a
connection into a database. The only additional problem there is that
ApplixWare is sending a DSN keyword with a trailing blank, so I'm having
to chop it off in psqlodbc to find things in the registry.

At this point, I can try connecting to a database, but it doesn't seem
to get past the first handshaking with Postgres on the wire. I'm
guessing that the protocol may have changed for v6.4 and those changes
are not in the driver I'm using? The protocol has settled down for the
next release, so perhaps we could incorporate the changes into my test
code.

Is there a summary somewhere of what the protocol changes are? I've
forgotten if it was posted in detail already...

btw, I assume that you don't want me to send changes until I can show
that something works :)

                         - Tom

golem> cat psqlodbc.log
DSN info:
DSN='PostgreSQL',server='localhost',port='5432',dbase='test',user='tgl',passwd=''

readonly='',protocol='',showoid='',fakeoidindex='',showsystable=''
          conn_settings=''
          translation_dll='',translation_option=''
conn = 134611472, SQLConnect(DSN='PostgreSQL', UID='tgl', PWD='no$way')
Global Options: fetch=100, socket=4096, unknown_sizes=0,
max_varchar_size=254, max_longvarchar_size=4094
                disable_optimizer=1, unique_index=0, use_declarefetch=1
                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1
                extra_systable_prefixes='dd_;', conn_settings=''
conn=134611472, query=' '


golem$ /opt/postgres/current/bin/postmaster -B 1024 -i -d 3
FindExec: found "/opt/postgres/current/bin/postgres" using argv[0]
binding ShmemCreate(key=52e2c1, size=8779352)
/opt/postgres/current/bin/postmaster: ServerLoop:               handling
reading 5
/opt/postgres/current/bin/postmaster: ServerLoop:               handling
reading 5
/opt/postgres/current/bin/postmaster: ServerLoop:               handling
writing 5
/opt/postgres/current/bin/postmaster: BackendStartup: environ dump:
-----------------------------------------
        TERM=xterm-color
        HOME=/home/postgres


PATH=.:/opt/postgres/current/bin:/opt/bin:/usr/local/bin:/sbin:/usr/sbin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/usr/bin/mh
        SHELL=/bin/tcsh
        MAIL=/var/spool/mail/postgres
        LOGNAME=postgres
        HOSTTYPE=i386-linux
        VENDOR=intel
        OSTYPE=linux
        MACHTYPE=i386
        SHLVL=1
        PWD=/opt/postgres/current/src
        USER=postgres
        GROUP=postgres
        HOST=golem
        HOSTNAME=golem
        CVSROOT=/opt/postgres/cvs
        PGROOT=/opt/postgres/current
        PGDATA=/opt/postgres/current/data
        PGLIB=/opt/postgres/current/lib
        PGDATA2=/home/postgres/data
        RCSINIT=-x,v/
        POSTPORT=5432
        POSTID=2147483647
        PG_USER=tgl
        IPC_KEY=5432000
-----------------------------------------
/opt/postgres/current/bin/postmaster: BackendStartup: pid 30174 user tgl
db test socket 5
/opt/postgres/current/bin/postmaster child[30174]: starting with
(/opt/postgres/current/bin/postgres, -p, -d3, -P5, -B, 1024, -v65536,
test, )
FindExec: found "/opt/postgres/current/bin/postgres" using argv[0]
        ---debug info---
        Quiet =        f
        Noversion =    f
        timings   =    f
        dates     =    Normal
        bufsize   =    1024
        sortmem   =    512
        query echo =   f
        DatabaseName = [test]
        ----------------

        InitPostgres()..
/opt/postgres/current/bin/postmaster: reaping dead processes...
/opt/postgres/current/bin/postmaster: CleanupProc: pid 30174 exited with
status 0

Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
> At this point, I can try connecting to a database, but it doesn't seem
> to get past the first handshaking with Postgres on the wire. I'm
> guessing that the protocol may have changed for v6.4 and those changes
> are not in the driver I'm using? The protocol has settled down for the
> next release, so perhaps we could incorporate the changes into my test
> code.

I should mention that if I specify a non-existant database (OK, yeah, I
forgot to create one :) then the handshaking for that failure mode seems
to work well. I get a good error message back at ApplixWare...

                     - Tom

Re: [INTERFACES] iodbc interface on Unix

From
Tom Lane
Date:
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
>> At this point, I can try connecting to a database, but it doesn't seem
>> to get past the first handshaking with Postgres on the wire. I'm
>> guessing that the protocol may have changed for v6.4 and those changes
>> are not in the driver I'm using?

If the iodbc code is running the older ("version 1.0") protocol, it
should still work with the current server.  A possible gotcha here
is that if the client code is compiled to send "PG_PROTOCOL_LATEST"
as the version number that it speaks, then you lose when you compile
against the current backend's include files.  You've just built a
client that claims to speak 2.0 but doesn't really.  The client really
should have its own idea of the protocol version number it uses.
(I just this weekend sent in patches to make libpq do this correctly,
BTW; so iodbc is hardly the only one to make this mistake.  If it did.)

The other possibility is that you have an iodbc that was updated to
handle the 2.0 protocol as it stood a month or two ago, but doesn't
have all of the changes quite yet.

Or, maybe the backend's support for 1.0 protocol is broken.  We do
need to check that before 6.4 is released.  I thought someone had,
however.

            regards, tom lane

Re: [INTERFACES] iodbc interface on Unix

From
Byron Nikolaidis
Date:

Thomas G. Lockhart wrote:

> OK, so there are some minor problems in compiling for the Unix
> environment, most of which I've fixed or worked-around. The biggest
> minor problem, and easiest to fix, is that there is an
>
>   #include <config.h>
>
> in most of the files. I believe that this should be changed to
>
>   #include "config.h"
>
> to allow the compiler to pick up the file from the local directory.
> Otherwise, it is interpreted as a "system-ish" file and (with my
> Makefile, using many of the compiler options from the Postgres
> distribution) finds the config.h in the Postgres distribution instead.
>

Makes sense to me.

> There is some funny-business with "HINSTANCE", but I've #define'd it to
> be the same as a pointer to void.
>
> As was the case a few weeks ago, I'm now able to start to initiate a
> connection into a database. The only additional problem there is that
> ApplixWare is sending a DSN keyword with a trailing blank, so I'm having
> to chop it off in psqlodbc to find things in the registry.
>
> At this point, I can try connecting to a database, but it doesn't seem
> to get past the first handshaking with Postgres on the wire. I'm
> guessing that the protocol may have changed for v6.4 and those changes
> are not in the driver I'm using? The protocol has settled down for the
> next release, so perhaps we could incorporate the changes into my test
> code.
>

Yes, if you are using a snapshot of 6.4 then the driver won't work because it is using 6.3 protocol.  And it uses
PG_PROTOCOL_LATEST
in its connect for the protocol version.  Maybe it should use 6.3.

How do you set the protocol version to be 6.3?  Is it just major number = 6 and minor number = 3?  Seems too simple.

Also, as far as compiling goes, I understand the driver has trouble compiling under Linux since the Translation DLL
stuffwas 
added.  Did you have to ifdef that stuff out for Non-win32?  If so, I guess I'll just put that into the official
source.

Byron


Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
> Yes, if you are using a snapshot of 6.4 then the driver won't work
> because it is using 6.3 protocol.  And it uses PG_PROTOCOL_LATEST
> in its connect for the protocol version.  Maybe it should use 6.3.

Ah. That sounds like the problem.

> How do you set the protocol version to be 6.3?  Is it just major
> number = 6 and minor number = 3?  Seems too simple.

It's even simpler. The protocol started at 0.0, was 1.0 for v6.3, and
will be 2.0 for v6.4. But on the surface it looks like we are OK for
now, unless Postgres doesn't really like v6.3 packets:

/opt/postgres/current/src/interfaces/odbc.new/connection.h:
  #define PG_PROTOCOL_LATEST              PG_PROTOCOL(1, 0)
/opt/postgres/current/src/interfaces/odbc.new/connection.c:
  sp.protoVersion = (ProtocolVersion) htonl(PG_PROTOCOL_LATEST);

> Also, as far as compiling goes, I understand the driver has trouble
> compiling under Linux since the Translation DLL stuff was
> added.  Did you have to ifdef that stuff out for Non-win32?

Yeah, it didn't help :) I hope to get somewhere on this soon, and I can
send patches or full sources which include changes. No point in making
you guess on what actually worked...

So, any more ideas/suggestions on how to get this working?

                    - Tom

Re: [INTERFACES] iodbc interface on Unix

From
Aleksey Demakov
Date:
Byron Nikolaidis <byronn@insightdist.com> writes:

> Also, as far as compiling goes, I understand the driver has trouble compiling under Linux since the Translation DLL
stuffwas 
> added.  Did you have to ifdef that stuff out for Non-win32?  If so, I guess I'll just put that into the official
source.

I'm sorry, I haven't yet seen latest version of the driver. But I sent a
patch to fix this problem for previous version. This happened exactly before
Byron's vacation. I sent it to Byron, Thomas, and to mailing list. Doesn't it
help?

Regards,
Aleksey

--
Aleksey Demakov
avd@gcom.ru

Re: [INTERFACES] iodbc interface on Unix

From
Byron Nikolaidis
Date:

Aleksey Demakov wrote:

> Byron Nikolaidis <byronn@insightdist.com> writes:
>
> > Also, as far as compiling goes, I understand the driver has trouble compiling under Linux since the Translation DLL
stuffwas 
> > added.  Did you have to ifdef that stuff out for Non-win32?  If so, I guess I'll just put that into the official
source.
>
> I'm sorry, I haven't yet seen latest version of the driver. But I sent a
> patch to fix this problem for previous version. This happened exactly before
> Byron's vacation. I sent it to Byron, Thomas, and to mailing list. Doesn't it
> help?
>

Sorry, I forgot about your patch.  I do have it though and will look at putting it in the source.

Tom,  if you got this patch, did it help with the compiling problems, or did you just fix it yourself?

Byron



Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
> > But I sent a
> > patch to fix this problem for previous version.
> Tom,  if you got this patch, did it help with the compiling problems,
> or did you just fix it yourself?

I don't remember if I applied it directly or typed it myself. Yes, it
did help, but since I'm making quite a few minor changes to try to track
down the current problems it would probably be best if you waited to
apply it. I'll give you an integrated set of patches (or a new set of
source code if you find that easier) when we figure out what is going
on.

At the moment, I'm stuck on a bizarre symptom (which of course will make
perfect sense the second after I send this e-mail :) I'm having trouble
seeing SQLExecute() entered, but can see the calling routine and can see
a return. So it looks like the routine is not actually getting called,
which of course is impossible. Don't know what is going on quite yet...

So the current status is that the initial startup packet is working, but
the configuration statements ("set DateStyle = 'ISO', etc) are
apparently not; the backend never sees a query, even the empty query
sent just after the startup negotiation.

                     - Tom

Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
> At the moment, I'm stuck on a bizarre symptom (which of course will
> make perfect sense the second after I send this e-mail :) I'm having
> trouble seeing SQLExecute() entered, but can see the calling routine
> and can see a return. So it looks like the routine is not actually
> getting called, which of course is impossible. Don't know what is
> going on quite yet...

OK, with Gerald's guidance I can confirm that the linker was calling the
iodbc routine SQLExecute rather than the internal psqlodbc routine of
the same name. This makes a bit of sense to me, since iodbc is getting
loaded first and is "steering the boat". I've stubbed-out SQLExecute()
as has been done for several other routines by defining _SQLExecute() as
the real routine and SQLExecute() as a pass-through stub.

So, I now get through several of the startup queries, including the
large object cursor loop, then crash. But clearly things are
progressing; just need to figure out what is getting called next and
(I'll bet) find out what else needs to be stubbed...

                        - Tom

Re: [INTERFACES] iodbc interface on Unix

From
Byron Nikolaidis
Date:

Thomas G. Lockhart wrote:

> > At the moment, I'm stuck on a bizarre symptom (which of course will
> > make perfect sense the second after I send this e-mail :) I'm having
> > trouble seeing SQLExecute() entered, but can see the calling routine
> > and can see a return. So it looks like the routine is not actually
> > getting called, which of course is impossible. Don't know what is
> > going on quite yet...
>
> OK, with Gerald's guidance I can confirm that the linker was calling the
> iodbc routine SQLExecute rather than the internal psqlodbc routine of
> the same name. This makes a bit of sense to me, since iodbc is getting
> loaded first and is "steering the boat". I've stubbed-out SQLExecute()
> as has been done for several other routines by defining _SQLExecute() as
> the real routine and SQLExecute() as a pass-through stub.
>

I did that for  SQLExecDirect but not SQLExecute, because SQLExecute wasn't
being called before the connection transitioned to a connected state,
whereas, SQLExecDirect was being called in the middle of transitioning to
the connected state, which was ultimately causing a runtime error.

I'm still confused though, why would this matter?  I can understand for the
case I mentioned because of not being in the correct state, but what does it
matter if SQLExecute is called in the driver manager for normal execution.
That's how its supposed to work anyway.  The application calls the driver
manager function, then the driver manager calls the function in one of its
loaded drivers.

Sounds like there is a lot more going on here than meets the eye.  Or maybe
the Windows driver manager or the windows linking process is covering up
some things.

Byron



Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
> > I can confirm that the linker was calling the
> > iodbc routine SQLExecute rather than the internal psqlodbc routine
> > of the same name.
> I did that for  SQLExecDirect but not SQLExecute, because SQLExecute
> wasn't being called before the connection transitioned to a connected
> state, whereas, SQLExecDirect was being called in the middle of
> transitioning to the connected state, which was ultimately causing a
> runtime error.
> I'm still confused though, why would this matter?
> ..., but what does it
> matter if SQLExecute is called in the driver manager for normal
> execution.
> That's how its supposed to work anyway.  The application calls the
> driver manager function, then the driver manager calls the function in
> one of its loaded drivers.

Yeah, but the problem is that the loaded driver is trying to call an
internal routine, and instead is accidentally calling *back* to the
driver manager. I would guess that for every call made to the loaded
driver, the driver manager is not expecting any more of its code to
execute until control returns from the loaded driver routine.

> Sounds like there is a lot more going on here than meets the eye.  Or
> maybe the Windows driver manager or the windows linking process is
> covering up some things.

Yup.

                        - Tom

Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
Woohoo!! Got my first data access through Applix via the psqlodbc
driver. It turns out that Gerald's suggestions about driver linking
problems and a damaged Makefile (with a misplaced "-L") were the keys to
jump these last steps, and I probably would not have gotten anywhere
without the suggestions. Thanks Gerald!

Anyway, if I can remember how to get a screenshot from Xwindows then
I'll send one along (any hints on how to do it?).

I've got changes to almost every file, none of the changes being huge
but cumulatively there is a lot which needed to be slightly different to
work on Unix. Some changes I've made to the odbc.ini file reading might
affect behavior under Windoze too, but I suspect that they are
desirable; in particular making the odbc.ini file reading insensitive to
whitespace and to case. At the moment the driver is case sensitive when
reading the odbc.ini file, and I can't think why it should be...

I do get a "broken pipe" with at least one operation still; spent the
last while trying to get more of the internal calls stubbed out properly
and things work somewhat better but not finished yet.

Anyway, we have some progress!

                      - Tom

Re: [INTERFACES] iodbc interface on Unix

From
Gerald Gryschuk
Date:
Thomas G. Lockhart wrote:
>
> Woohoo!! Got my first data access through Applix via the psqlodbc
> driver. It turns out that Gerald's suggestions about driver linking
> problems and a damaged Makefile (with a misplaced "-L") were the keys to
> jump these last steps, and I probably would not have gotten anywhere
> without the suggestions. Thanks Gerald!
>

Great to hear this.

>
> I do get a "broken pipe" with at least one operation still; spent the
> last while trying to get more of the internal calls stubbed out properly
> and things work somewhat better but not finished yet.
>

Uhmm. Well now what do I do with my changes? I went through the complete
source and changed all the internal SQL calls as we had discussed, Doh!.
I didn't
make the changes for reading the odbc.ini file though. I can still send
this to you if you want it. In fact why don't I send my source to you,
you do a diff against your new code, add or fix it as you see fit than
send the whole shabang along to Byron. I would guess he probably won't
want 2 different copies of source fixing the same problem. I did add
a Makefile.standalone which is essentially the same one I sent out in
the original way back when. There's probably a better solution than this
though.

Anyway, I'll attach my source to a private message than you can go from
there.

--
Gerald Gryschuk
gerald.gryschuk@home.com
MidNightOil Consulting - "We burn the midnight oil so you don't have
to."

Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
> But I guess if it comes down to making all calls _SQL and using them
> internally, then I guess we have to do it.  It's ok with me I suppose.
> I guess the Windows linking process does some magic and figures that
> stuff out, and maybe even provides stubs itself for internally called
> functions?!?

Hi Byron. OK, I seem to have a full-featured ODBC interface running
under Unix. Been able to do table selects and updates, and I understand
how the .odbc.ini files should look. It turns out _none_ of the problems
I've had were with your fundamental code (e.g. all socket handshakes
worked without change!) but instead were due to obscure differences in
our compilation and linking environments.

There are a few things to figure out to reintegrate with your main tree:

1) I have a Makefile which integrates with the Postgres distribution. I
assume that you don't want or need it in your primary distribution,
right? So I'll commit it to the Postgres distribution but you might want
to ignore it...

2) The Unix environment uses dynamic linking from the ODBC driver
manager to the psqlodbc driver. On Unix, calls from within psqlodbc to
other routines which are local to psqlodbc actually get linked back to
the ODBC manager rather than staying local. This is not what is
intended, but the behavior makes sense. The way around this is to have
local names for the routines, and jump through the public names when
calling them externally, and call the internal names directly when
within postodbc. I've found a way to _not_ require this internal jump
when #ifdef UNIX is false. The mechanism I'm using requires a macro
defined:

#ifdef UNIX
#define INTERNAL(f) _##f
#else
#define INTERNAL(f) f
#endif

Can you check that this no-op macro works for you when subroutine names
are inside this INTERNAL() macro? For example, in bind.c, change one of
the internal calls to SQLAllocStmt() from something like
    result = SQLAllocStmt( self, &hstmt);
to
    result = INTERNAL(SQLAllocStmt)( self, &hstmt);

If your C compiler/preprocessor supports this, then we're OK. The
non-Unix code won't have to carry the overhead of these extra calls...

3) Many, but not all, of the source code files have DOS-specific EOL
characters. I'd like to strip those out of the code which is sitting on
Unix boxes, and have them correct when sitting on your DOS machines too.
Can we transfer files using FTP ascii transfers to fix the EOL problems
in the future? Or is there another way which will get this fixed up?? In
the meantime I will clean up all the files I have, and then can get them
to you via the Postgres web site.

4) I would like to add a couple of keyword parameters to the .odbc.ini
configuration file: CommLog and Debug were previously allowed only in
the /etc/odbcinit.ini file. Is it OK to add them? Are there
considerations which would make this a bad idea?

5) I've made the odbc.ini file reader case insensitive and more
resilient to embedded whitespace (e.g. keywords no longer have to start
in the first column). As in the previous item, are there considerations
which would make this a bad idea?

Talk to you soon...

                     - Tom

Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
Ah, I just realized that at least some of the EOL problems stem from my
picking up your latest .zip file from insightdist.com, and that the
files already on postgresql.org are probably clean already. So I'll
clean them up locally and give you the files via ftp...

                 - Tom

Re: [INTERFACES] iodbc interface on Unix

From
Byron Nikolaidis
Date:

Thomas G. Lockhart wrote:

> Hi Byron. OK, I seem to have a full-featured ODBC interface running
> under Unix. Been able to do table selects and updates, and I understand
> how the .odbc.ini files should look. It turns out _none_ of the problems
> I've had were with your fundamental code (e.g. all socket handshakes
> worked without change!) but instead were due to obscure differences in
> our compilation and linking environments.
>
> There are a few things to figure out to reintegrate with your main tree:
>
> 1) I have a Makefile which integrates with the Postgres distribution. I
> assume that you don't want or need it in your primary distribution,
> right? So I'll commit it to the Postgres distribution but you might want
> to ignore it...
>

There was already a "Makefile.unx" file in the distribution.  Is that the
file (or some part thereof) you are using?   To answer your question, yes it
should be in the distribution.  I do include it even in the postsrc.zip
archive, along with the windows makefile psqlodbc.mak.  But the psqlodbc.mak
file is not in the official source whereas the "Makefile.unx" file is.

> 2) The Unix environment uses dynamic linking from the ODBC driver
> manager to the psqlodbc driver. On Unix, calls from within psqlodbc to
> other routines which are local to psqlodbc actually get linked back to
> the ODBC manager rather than staying local. This is not what is
> intended, but the behavior makes sense. The way around this is to have
> local names for the routines, and jump through the public names when
> calling them externally, and call the internal names directly when
> within postodbc. I've found a way to _not_ require this internal jump
> when #ifdef UNIX is false. The mechanism I'm using requires a macro
> defined:
>
> #ifdef UNIX
> #define INTERNAL(f) _##f
> #else
> #define INTERNAL(f) f
> #endif
>
> Can you check that this no-op macro works for you when subroutine names
> are inside this INTERNAL() macro? For example, in bind.c, change one of
> the internal calls to SQLAllocStmt() from something like
>     result = SQLAllocStmt( self, &hstmt);
> to
>     result = INTERNAL(SQLAllocStmt)( self, &hstmt);
>
> If your C compiler/preprocessor supports this, then we're OK. The
> non-Unix code won't have to carry the overhead of these extra calls...
>

Yes, this works.  BUT, I don't see the real benefit.  OK, so the internal
calls will now have the original behavior for Windows, which for all I know,
might not be as fast as just calling the internal function.  Internal calls
aren't that intensive anyway so I don't see any real savings here.

BUT,  external calls from the driver manager will now have to go through
this thin wrapper layer (SQLGetData --->_SQLGetData), which is a small
performance loss, especially on a call to say, SQLGetData, which could
possibly be called a million times in a session for big data access.

I don't have a problem with these wrappers but the real benefit for Windows,
would be if the external calls didn't have to go through the wrapper layer.
Therefore, I would rather not have this INTERNAL macro and simply call the
_SQLxxx functions  internally.


> 3) Many, but not all, of the source code files have DOS-specific EOL
> characters. I'd like to strip those out of the code which is sitting on
> Unix boxes, and have them correct when sitting on your DOS machines too.
> Can we transfer files using FTP ascii transfers to fix the EOL problems
> in the future? Or is there another way which will get this fixed up?? In
> the meantime I will clean up all the files I have, and then can get them
> to you via the Postgres web site.
>

OK.  I do transfer files to Postgresql.org using ftp ascii.  Just the
postsrc.zip distribution would have dos crap in it.

> 4) I would like to add a couple of keyword parameters to the .odbc.ini
> configuration file: CommLog and Debug were previously allowed only in
> the /etc/odbcinit.ini file. Is it OK to add them? Are there
> considerations which would make this a bad idea?
>

I think these log type parameters belong on the driver level as they are
now, not per datasource.  OR did I misunderstand your question?

> 5) I've made the odbc.ini file reader case insensitive and more
> resilient to embedded whitespace (e.g. keywords no longer have to start
> in the first column). As in the previous item, are there considerations
> which would make this a bad idea?
>

This sounds fine.

Byron


Re: [INTERFACES] iodbc interface on Unix

From
Gerald Gryschuk
Date:
Byron Nikolaidis wrote:
>
> Thomas G. Lockhart wrote:
>
> > 3) Many, but not all, of the source code files have DOS-specific EOL
> > characters. I'd like to strip those out of the code which is sitting on
> > Unix boxes, and have them correct when sitting on your DOS machines too.
> > Can we transfer files using FTP ascii transfers to fix the EOL problems
> > in the future? Or is there another way which will get this fixed up?? In
> > the meantime I will clean up all the files I have, and then can get them
> > to you via the Postgres web site.
> >
>
> OK.  I do transfer files to Postgresql.org using ftp ascii.  Just the
> postsrc.zip distribution would have dos crap in it.

I noticed this too the first few times I unzipped postsrc.zip until I
remebered that
unzip(I'm assuming Thomas your using the same one I am) has a "-a"
option to convert
all text files to the proper line endings regardless of where the file
originated.
i.e. if you do "unzip -a postsrc.zip" the problems with DOS EOL
characters goes
away.

--
Gerald Gryschuk(ggryschuk@scf.sk.ca)
Programmer Analyst
Saskatoon Cancer Centre
((306)655-2746)

Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
> > 1) I have a Makefile which integrates with the Postgres
> > distribution.
> ... yes it should be in the distribution. I do include it even
> in the postsrc.zip archive, along with the windows makefile
> psqlodbc.mak.  But the psqlodbc.mak
> file is not in the official source whereas the "Makefile.unx" file is.

I have another Makefile (called "Makefile" :) which can be integrated
into the Postgres distribution environment, including being generated
from "Makefile.in". Makefile.unx is not Postgres-tree-specific, and is
probably a better example for someone trying the build the distribution
without being in the Postgres tree. I'm not proposing to get rid of
Makefile.unx, just to put "Makefile" into the Postgres distribution.

> > 2) The Unix environment uses dynamic linking from the ODBC driver
> > manager to the psqlodbc driver.
> Yes, this works.  BUT, I don't see the real benefit.
> I don't have a problem with these wrappers but the real benefit for
> Windows, would be if the external calls didn't have to go through the
> wrapper layer.
> Therefore, I would rather not have this INTERNAL macro and simply call
> the _SQLxxx functions internally.

Doh! I am pretty sure that there is a linker option on my Linux box
which will make this entire discussion moot. I recalled with clarity
that there _must_ be that option (probably "-Bsymbolic" on my machine)
very soon after carefully converting all of the code to this
internal/external dual interface :(

Will run some tests, but it probably is a non-issue. btw, I'd be willing
to bet that the Windows code never calls back through your wrapper layer
anyway; if it did things would crash just like on my machine :)

> > 3) Many, but not all, of the source code files have DOS-specific EOL
> > characters.
> OK.  I do transfer files to Postgresql.org using ftp ascii.  Just the
> postsrc.zip distribution would have dos crap in it.

Gerald gave me a suggestion for running unzip. It's a non-issue. Sorry
for the false alarm.

> > 4) I would like to add a couple of keyword parameters to the
> > .odbc.ini configuration file: CommLog and Debug were previously
> > allowed only in the /etc/odbcinit.ini file. Is it OK to add them?
> > Are there considerations which would make this a bad idea?
> I think these log type parameters belong on the driver level as they
> are now, not per datasource.  OR did I misunderstand your question?

Hmm. No, you have a good point. Unfortunately, it takes root privilege
for anyone to write into /etc, whereas it takes no system privilege to
do anything else with Postgres. Anyway, shouldn't we have some
driver-level parameters somewhere in ~/.odbc.ini? Otherwise, the user
can't do any debugging or diagnostics on new database definitions.

> > 5) I've made the odbc.ini file reader case insensitive and more
> > resilient to embedded whitespace
> This sounds fine.

OK.

Oh, one other issue:
6) gcc doesn't like the "mylog" routine being disabled by defining an
empty macro for it. That leaves stuff which looks like code in bare
parenthesis in the code body. Any suggestions from a C weenie on how to
completely disable the code? The problem is that macros seem to prefer a
fixed number of parameters, and mylog() has a variable number of
parameters allowed...

I'm leaving town for the weekend, but might be able to do some testing
beforehand. Otherwise, expect some news early next week...

Cheers.

                    - Tom

Re: [INTERFACES] iodbc interface on Unix

From
Byron Nikolaidis
Date:

Thomas G. Lockhart wrote:

> I have another Makefile (called "Makefile" :) which can be integrated
> into the Postgres distribution environment, including being generated
> from "Makefile.in". Makefile.unx is not Postgres-tree-specific, and is
> probably a better example for someone trying the build the distribution
> without being in the Postgres tree. I'm not proposing to get rid of
> Makefile.unx, just to put "Makefile" into the Postgres distribution.
>

I assume this Makefile is not intended to be used to compile for the Windows
environment, yes?  I just wanted to be clear on it.

> Hmm. No, you have a good point. Unfortunately, it takes root privilege
> for anyone to write into /etc, whereas it takes no system privilege to
> do anything else with Postgres. Anyway, shouldn't we have some
> driver-level parameters somewhere in ~/.odbc.ini? Otherwise, the user
> can't do any debugging or diagnostics on new database definitions.
>

I think I have a good solution to this problem.  Would it be possible to
have a local "odbcinst.ini" file in the user's home directory which would
get read first, and if that didn't exist, go to the /etc/odbcinst.ini file?
That way the user could override certain parameters, such as debugging.

> Oh, one other issue:
> 6) gcc doesn't like the "mylog" routine being disabled by defining an
> empty macro for it. That leaves stuff which looks like code in bare
> parenthesis in the code body. Any suggestions from a C weenie on how to
> completely disable the code? The problem is that macros seem to prefer a
> fixed number of parameters, and mylog() has a variable number of
> parameters allowed...
>

Yeah, this old thing.  Windows allowed me to be lazy and define a macro
which can comment out the rest of the line using the // C++ style comment!
The reason I didn't want the mylog statements to be compiled in was it took
up alot of space plus sometimes the mylog statements are a little risky in
what they print out (that may not be a real issue anymore).

One solution would be to put #ifdef MY_LOG around each of the mylog
statements.  Another, which I think is easier and probably more beneficial,
is to just forget about trying to not compile them in and simply use the
Debug parameter to control the printing (which you have to do anyway).

So, all you have to do is just always #define MY_LOG and let the "Debug"
parameter control it.  On windows, I can still use my comment trick.

Byron


Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
> > I'm not proposing to get rid of
> > Makefile.unx, just to put "Makefile" into the Postgres distribution.
> I assume this Makefile is not intended to be used to compile for the
> Windows environment, yes?  I just wanted to be clear on it.

Yes, the makefile called "Makefile" is very Postgres-tree-specific. Not
intended to replace Makefile.unx or other ways of building a driver
library.

> > Unfortunately, it takes root privilege for
> > anyone to write into /etc, whereas it takes no system privilege to
> > do anything else with Postgres. Anyway, shouldn't we have some
> > driver-level parameters somewhere in ~/.odbc.ini?
> Would it be possible to
> have a local "odbcinst.ini" file in the user's home directory which
> would get read first, and if that didn't exist, go to the
> /etc/odbcinst.ini file? That way the user could override certain
> parameters, such as debugging.

OK, I'm not clear on the usage of odbc.ini. My odbc.ini looks like (note
that this includes my local mods for "Debug" and "CommLog"):

[ODBC Data Sources]
PostgreSQL = PostgreSQL Driver
Postgres = Postgres Simple Spec
PostgresODBC = PostgresODBC
Default = Postgres Stripped

[PostgreSQL]
Debug = 1
CommLog = 1
ReadOnly = 0
Driver = /opt/postgres/current/lib/libpsqlodbc.so
Servername = localhost
Username = tgl
Password = "no$way"
Port = 5432
Database = test

<snip>

So, each database entry specifies the full information for each
database, including which driver to use, host info and account info.
What is the distinction between all of those entries and the "Debug" and
"CommLog" fields? What _should_ odbcinit.ini be providing which should
never be in odbc.ini?

> > 6) gcc doesn't like the "mylog" routine being disabled by defining
> > an empty macro for it.
> One solution would be to put #ifdef MY_LOG around each of the mylog
> statements.  Another, which I think is easier and probably more
> beneficial, is to just forget about trying to not compile them in and
> simply use the Debug parameter to control the printing (which you have
> to do anyway).
> So, all you have to do is just always #define MY_LOG and let the
> "Debug" parameter control it.  On windows, I can still use my comment
> trick.

OK.

So, I just finished removing the stubs and internal calls which I had
added in (having now found the linker options which make sure things
resolve within the library), when I found some additional stubs which
are in results.c. They have been there since before I touched the code.
Are they required for Windows? If so, why aren't the other routines
required to be stubbed as they were on my Unix box without the right
linker options??

                           - Tom

Re: [INTERFACES] iodbc interface on Unix

From
David Hartwig
Date:
Byron will be unavailable for a day or so.

Thomas G. Lockhart wrote:

> > > I'm not proposing to get rid of
> > > Makefile.unx, just to put "Makefile" into the Postgres distribution.
> > I assume this Makefile is not intended to be used to compile for the
> > Windows environment, yes?  I just wanted to be clear on it.
>
> Yes, the makefile called "Makefile" is very Postgres-tree-specific. Not
> intended to replace Makefile.unx or other ways of building a driver
> library.
>
> > > Unfortunately, it takes root privilege for
> > > anyone to write into /etc, whereas it takes no system privilege to
> > > do anything else with Postgres. Anyway, shouldn't we have some
> > > driver-level parameters somewhere in ~/.odbc.ini?
> > Would it be possible to
> > have a local "odbcinst.ini" file in the user's home directory which
> > would get read first, and if that didn't exist, go to the
> > /etc/odbcinst.ini file? That way the user could override certain
> > parameters, such as debugging.
>
> OK, I'm not clear on the usage of odbc.ini. My odbc.ini looks like (note
> that this includes my local mods for "Debug" and "CommLog"):
>
> [ODBC Data Sources]
> PostgreSQL = PostgreSQL Driver
> Postgres = Postgres Simple Spec
> PostgresODBC = PostgresODBC
> Default = Postgres Stripped
>
> [PostgreSQL]
> Debug = 1
> CommLog = 1
> ReadOnly = 0
> Driver = /opt/postgres/current/lib/libpsqlodbc.so
> Servername = localhost
> Username = tgl
> Password = "no$way"
> Port = 5432
> Database = test
>
> <snip>
>
> So, each database entry specifies the full information for each
> database, including which driver to use, host info and account info.
> What is the distinction between all of those entries and the "Debug" and
> "CommLog" fields? What _should_ odbcinit.ini be providing which should
> never be in odbc.ini?
>
> > > 6) gcc doesn't like the "mylog" routine being disabled by defining
> > > an empty macro for it.
> > One solution would be to put #ifdef MY_LOG around each of the mylog
> > statements.  Another, which I think is easier and probably more
> > beneficial, is to just forget about trying to not compile them in and
> > simply use the Debug parameter to control the printing (which you have
> > to do anyway).
> > So, all you have to do is just always #define MY_LOG and let the
> > "Debug" parameter control it.  On windows, I can still use my comment
> > trick.
>
> OK.
>
> So, I just finished removing the stubs and internal calls which I had
> added in (having now found the linker options which make sure things
> resolve within the library), when I found some additional stubs which
> are in results.c. They have been there since before I touched the code.
> Are they required for Windows? If so, why aren't the other routines
> required to be stubbed as they were on my Unix box without the right
> linker options??
>
>                            - Tom




Re: [INTERFACES] iodbc interface on Unix

From
Gerald Gryschuk
Date:
Thomas G. Lockhart wrote:
> So, I just finished removing the stubs and internal calls which I had
> added in (having now found the linker options which make sure things
> resolve within the library), when I found some additional stubs which
> are in results.c. They have been there since before I touched the code.
> Are they required for Windows? If so, why aren't the other routines
> required to be stubbed as they were on my Unix box without the right
> linker options??

With the "-Bsymbolic" flag(which I found only after you mentioned it)
than none of
these _SQL stubs are needed. Byron only added the _SQL stubs when I told
him that
there was a problem that I thought was related to an ODBC spec issue
which was really
just this problem all along. In other words my ignorance led me to a
mistaken conclusion, which we have attempted to fix in the wrong way
ever since. I really must apologize.

So I'm positive its o.k. if you dust all references to _SQL functions.

--
Gerald Gryschuk(ggryschuk@scf.sk.ca)
Programmer Analyst
Saskatoon Cancer Centre
((306)655-2746)

Re: [INTERFACES] iodbc interface on Unix

From
Byron Nikolaidis
Date:

Gerald Gryschuk wrote:

> Thomas G. Lockhart wrote:
> > So, I just finished removing the stubs and internal calls which I had
> > added in (having now found the linker options which make sure things
> > resolve within the library), when I found some additional stubs which
> > are in results.c. They have been there since before I touched the code.
> > Are they required for Windows? If so, why aren't the other routines
> > required to be stubbed as they were on my Unix box without the right
> > linker options??
>
> With the "-Bsymbolic" flag(which I found only after you mentioned it)
> than none of
> these _SQL stubs are needed. Byron only added the _SQL stubs when I told
> him that
> there was a problem that I thought was related to an ODBC spec issue
> which was really
> just this problem all along. In other words my ignorance led me to a
> mistaken conclusion, which we have attempted to fix in the wrong way
> ever since. I really must apologize.
>
> So I'm positive its o.k. if you dust all references to _SQL functions.
>

This -Bsymbolic option is great news!  Yes, indeed, go ahead and dust the
_SQL stubs in results.c!

Byron (minus one pair of tonsils)


Re: [INTERFACES] iodbc interface on Unix

From
"Thomas G. Lockhart"
Date:
> This -Bsymbolic option is great news!  Yes, indeed, go ahead and dust
> the _SQL stubs in results.c!

OK, I've got some cleaned-up code now. The only outstanding issue I can
recall is whether CommLog and Debug should absolutely not be allowed in
the user-accessible .odbc.ini file. btw, I've now realized that the
driver information is not in the database-specific area as I might have
claimed earlier, but in another area ([Default]) of the .odbc.ini file.

I suspect that differences in behavior between the Unix iODBC driver and
the drivers you work with on Windows boxes have lead to some of our
confusion over where things should go. In fact, it appears that the
iODBC driver manager does not know about an odbcinst.ini file of any
sort, so the .odbc.ini file is the only one which is very useful under
Unix.

So, the CommLog/Debug question seems to have only one answer for Unix
installations (they must be allowed somewhere in odbc.ini) but we could
disable that for non-Unix builds. Or, replace references to odbcinst.ini
with odbc.ini for Unix installations.

The current status is that the code compiles and runs, allowing full
access to databases. I haven't gotten the time type to display properly
yet (it shows today's date also) but that may be a problem with
ApplixWare.

> Byron (minus one pair of tonsils)

Ouch :(

                    - Tom

Re: [INTERFACES] iodbc interface on Unix

From
Gerald Gryschuk
Date:
I'm sure Byron will correct me if I'm wrong but here's my understanding
of things.

Thomas G. Lockhart wrote:
> OK, I've got some cleaned-up code now. The only outstanding issue I can
> recall is whether CommLog and Debug should absolutely not be allowed in
> the user-accessible .odbc.ini file. btw, I've now realized that the
> driver information is not in the database-specific area as I might have
> claimed earlier, but in another area ([Default]) of the .odbc.ini file.

I believe this an ODBC thing, if you don't give a specific DSN to use
than
iODBC and I think even Windows ODBC manager will use the [Default]
section
to determine which driver library to load.

>
> I suspect that differences in behavior between the Unix iODBC driver and
> the drivers you work with on Windows boxes have lead to some of our
> confusion over where things should go. In fact, it appears that the
> iODBC driver manager does not know about an odbcinst.ini file of any
> sort, so the .odbc.ini file is the only one which is very useful under
> Unix.

Windows ODBC manager shouldn't know about odbcinst.ini either.
odbcinst.ini
is loaded only by psqlodbc as far as I know. None of the settings in
odbcinst.ini
apply to iODBC anyway. Specifically the settings in odbcinst.ini get
read once only
when the driver's dynamic library is first loaded. See file psqlodbc.c
for the dynamic
loading routines that get run. On Linux when a dll is loaded if the
dynamic library
exports a routine _init than it will be run before the call to dlopen(by
iODBC) returns.
_init calls getGlobalDefaults which is the routine that loads everything
from odbcinst.ini.
As far as I know this gets done once only regardless of how many
references to the driver library are currently being held. Obviously
this has consequences on a "real" multiuser system since only the first
person actually causing the driver to be loaded will make the
odbcinst.ini file get read.

>
> So, the CommLog/Debug question seems to have only one answer for Unix
> installations (they must be allowed somewhere in odbc.ini) but we could
> disable that for non-Unix builds. Or, replace references to odbcinst.ini
> with odbc.ini for Unix installations.

If you want true user level control over these settings then the only
solution is to have them read in by data source section. Otherwise, the
settings from the first user's odbcinst.ini file will get used for every
subsequent concurrent loading of the driver. In other words, your second
solution above won't work.

Your other question about what truely belongs in odbcinst.ini is
probably more illuminating here. I would think that Debug should stay in
odbcinst.ini as this is really a driver thing meant only for
"developers". The CommLog setting should probably be moved to the
odbc.ini file and used per datasource. This raises another problem
though in that all CommLog messages are sent to the same psqlodbc.log
file. This isn't good in a multi-user system as the file is opened
read-write only for people in the same user/group which, on RedHat at
least, defaults to a single person. After scanning the qlog source(see
misc.c), I remember now why I didn't bother doing anything about this
before. This will require a non-trivial "fix" since qlog stores the
logging file pointer in a static variable. Since logging was just gravy
to me at the time I didn't worry about it.

Hope this helps.
--
Gerald Gryschuk(ggryschuk@scf.sk.ca)
Programmer Analyst
Saskatoon Cancer Centre
((306)655-2746)