Michael Paquier | 24 Jul 07:13 2014
Picon

New release of pgodbc 09.03.XXXX before 9.4 series?

Hi all,

Should we consider soon a last release of odbc 03.03.XXXX before the 9.4 series?
This would be a good timing, considering as well that the latest
driver release was done two months ago and that a couple of bugs have
been fixed since. This would give us a well some time to integrate
correctly the protocol simplification patches that are waiting to be
integrated.
Regards,
-- 
Michael

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Personnel | 23 Jul 19:14 2014

Can't update views with pgsql-odbc

Hello,

 

I use PostgreSQL 9.2.8 and 9.34 64-bit on Windows or Linux, and psqlodbc v. 09.03.0300 64 bit on Windows 7/ 8.1 Pro.

In my Access 2013 application I can’t delete / insert /modify the views linked on the PostgreSQL server.

These views can be updated by SQL queries in PSQL or pgAdmin III.

So I create à light DB containing only 2 tables and 2 updatable views (one using rules like in PG 8, the other using a trigger) and I try to find a solution …

On a Windows 7 Pro SP1 32-bit box with MS Access 2007 I can modify only if :

-          Using a System Data Source (located in registry HKLM\Software\ODBC) with :

o   The postgres user

o   Connecting to a PostgreSQL 9.2.8 on CentOS 6.5 64-bits

o   Using the defauts values

-          The view based on rules works, not the other using trigger (new in PG 9.1)

On the 64-bit boxes, Windows 7 Pro SP1 and Windows 8.1 Update 1, no result.

 

Is the PG ODBC driver permitting to update a view or not ?

 

I can send you my test database (8 KB) for your tests …

 

Thanks for any solution.

 

Luc ROLLAND

luc <at> rolland-fr.com

Lizeth Solís | 15 Jul 17:58 2014
Picon

odbc for unix

Good morning.  Please If you could answer this question.  I would be so gratefull.

 

I want to use heterogeneous service  from Oracle to integrate Oracle – Postgres.

And to use this, I need and ODBC driver installed in my unix server (AIX 7.1).

 

I downloaded this one psqlodbc-09.03.0300.tar.gz  from  your web page,  from the route SRC.

 

And i’m applying this document:

 

Compiling psqlODBC on Unix

Following psqlODBC's move away from the main PostgreSQL source tree, Peter Eisentraut was kind enough provide a build environment for us which is now included in psqlODBC tarballs.

Installation

To install, just try something like:

% tar -zxvf psqlodbc-xx.xx.xxxx.tar.gz

% cd psqlodbc-xx.xx.xxxx

% ./configure

% make

% make install

 

 

 

But I get this error post apply ./configure

 

ZEUS:/opt/odbc/psqlodbc-09.03.0300>#./configure

checking for a BSD-compatible install... config/install-sh -c

checking whether build environment is sane... yes

checking for a thread-safe mkdir -p... config/install-sh -c -d

checking for gawk... no

checking for mawk... no

checking for nawk... nawk

checking whether make sets $(MAKE)... yes

checking whether to enable maintainer-specific portions of Makefiles... no

checking for gcc... no

checking for cc... no

checking for cl.exe... no

configure: error: in `/opt/odbc/psqlodbc-09.03.0300':

configure: error: no acceptable C compiler found in $PATH

See `config.log' for more details

 

 

My server is UNIX – AIX 7.1  and Im executing this one like ROOT.

 

Please could you help me with this?  Thanks a lot!!!

 


 

Matevž Mrak | 11 Jul 15:38 2014
Picon

WARNING: there is already a transaction in progress

Dear support,

I am using these software with your psqlodbc driver:

unixODBC: 2.3.2.
ODBC psqlodbc driver: http://www.postgresql.org/ftp/odbc/versions/src/ (latest) - psqlodbc-09.03.0300.tar.gz
OSX version: 10.9.3
UCS: UCS2
Eclipse: Eclipse Standard/SDK - Kepler Service Release - Build id: 20140224-0627
Python: 2.7.6
mxBase version: egenix-mx-base-3.2.6-py2.7_ucs2-macosx-10.4-fat-prebuilt
mxODBC version: egenix-mxodbc-3.1.2.macosx-10.4-fat-py2.7_ucs2.prebuilt

-----------------

I am getting error "WARNING: there is already a transaction in progress" when trying to execute command "BEGIN TRANSACTION; LOCK TABLE "%s" IN EXCLUSIVE MODE;'"

We have written mutex which is working on other systems to unlock and lock database table using pgsql-odbc driver. Is that possible that transaction is ran automatically by driver or when "cur = self.conn.cursor()" is executed ? Has anyone else experienced that issue?

-----------------

Python code:

                def lock(self):

            """

            Engages the lock

            <at> return: None

            """

            log.info("mutex lock %d" % self.locked)

            if not self.locked:

                cur = self.conn.cursor()

                log.info("mutex start tran")

                self.locked = True

                try:

                    cur.execute('BEGIN TRANSACTION; LOCK TABLE "%s" IN EXCLUSIVE MODE;' % self.table)

                except:

                    log.info("mutex tran failed")

                    log.exception()

                    raise

                else:

                    log.info("mutex tran started")

                cur.close()

                del cur


        def unlock(self):

            """

            Releases the lock

            <at> return: None

            """

            log.info("mutex unlock %d" % self.locked)

            if self.locked:

                try:

                    cur = self.conn.cursor()

                except:

                    log.exception()

                    raise

                log.info("mutex end trans")

                self.locked = False

                cur.execute("END TRANSACTION")

                log.info("mutex trans ended")

                cur.close()

                del cur


 
Python error: 

InterfaceError: ('25001', -1, 'WARNING: there is already a transaction in progress', 8489)

 - File "/Users/user/ana/ana/srv/orm/driver.py", line 100, in execute

[0m[31m14:58:53.862 0.0.0.0 {unknown} - Query to execute: BEGIN TRANSACTION; LOCK TABLE "Sys_sequencer" IN EXCLUSIVE MODE; - File "/Users/user/ana/ana/srv/orm/driver.py", line 101, in execute




SQL ODBC warning in log:

Statement = 0x100a0fa00
SQL = [BEGIN TRANSACTION; LOCK TABLE "Sys_sequencer" IN EXCLUSIVE MODE;][length = 64]
[ODBC][20715][1405083533.616254][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS_WITH_INFO]
DIAG [25001] WARNING: there is already a transaction in progress



(BEGIN TRANSACTION; LOCK TABLE "Sys_sequencer" IN EXCLUSIVE MODE; is called three times one afteranother without unlock)


--
Lep pozdrav,
Matevž Mrak
cobainpluto | 9 Jul 15:59 2014

Some problem about malloc

Dear all,

According to Fortify analysis,I found that some other malloc results could not be judged. It is also likely to produce a Null Dereference.

Details are as follows :
---------------------------------------------------------------
psqlodbc.h:433: in STRN_TO_NAME()
432 (the_name).name = malloc((n) + 1); \
433 memcpy((the_name).name, str, (n)); \
---------------------------------------------------------------
Here,if malloc failed,the returned name should be NULL.The subsequent memcpy operation had the potential to produce Null Dereference. 
There are two similar situations:
---------------------------------------------------------------
dlg_specific.c:1577: in decode()
1572 outs = (char *) malloc(ilen + 1);
1577 outs[o++] = ' ';
---------------------------------------------------------------
---------------------------------------------------------------
multibyte.c:186: in check_client_encoding()
185 rptr = malloc(len + 1);
186 memcpy(rptr, sptr, len);
---------------------------------------------------------------

I think it might be a bug. 
Could you please check and revive it?
The attachments are related codes.
Thank you very much.

Best wishes~
Sincerely yours, 
pluto.cobain
Attachment (psqlodbc.zip): application/zip, 26 KiB

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Craig Ringer | 9 Jul 05:13 2014

Windows build instructions on website v.outdated

Hey

It looks like the win32-compilation.html on PgFoundry is rather outdated
compared to the one in git.

http://psqlodbc.projects.pgfoundry.org/docs/win32-compilation.html

vs

http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob_plain;f=docs/win32-compilation.html;h=2372502f2f3482aeec011cb3fc34f1cf124c946a;hb=HEAD

I have no idea how the site gets refreshed, but it might be a good idea
to push an update.

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Craig Ringer | 9 Jul 04:53 2014

Impossible to use MSDTC/XA with SSPI

(Sent from the wrong account earlier, please disregard if you see
another copy)

Hi all

It doesn't seem to be possible to use MSDTC XA transactions alongside
SSPI authentication.

psqlODBC's MSDTC support assumes that the DSN supplied by the user is
also valid for a connection from MSDTC.exe via pgxalib.dll for XA
recovery. We've already seen one case where that's not a valid
assumption - where the 32-bit and 64-bit driver names differ when using
32-bit psqlODBC under SysWow64 on 64-bit Windows.

This is another case.

SSPI for TCP is like "peer" for unix socket connections on UNIX, except
that it uses an in-band handshake protocol rather than out-of-band
syscalls. It verifies that the connecting user is running as same
operating system user as the PostgreSQL username they've requested (or
is allowed to connect as that Pg user by the user mappings).

So what happens is:

- psqlODBC connects as the user
- User starts distributed tx manager, binds connection(s) to MSDTC
- User starts distributed tx; psqlODBC starts tx's on each session and
saves the user's DSN in the DTC transaction context.
- User does work on each session
- User asks DTC to commit tx; psqlODBC does a PREPARE TRANSACTION on
each session
- DTC tells user tx has committed successfully
- DTC asks psqlODBC to COMMIT PREPARED on each tx

If the app exits after MSDTC tells it the tx committed (which it might -
MSDTC doesn't offer synchronous 2-phase commit, it can only wait for the
first phase to complete) or the server crashes, etc, there are left-over
prepared transactions.

pgxalib.dll's job is to recover those. To do that it has to connect to
PostgreSQL. It's loaded within MSDTC.exe, where it establishes a
psqlODBC connection using the DSN it saved in the distributed
transaction context earlier.

This works only if the DSN is valid. The 32-bit to 64-bit mismatch was
one case of this being a problem earlier. SSPI is another - when
MSDTC.exe tries to connect to PostgreSQL it does so under Windows user
NETWORKSERVICE, which won't match the username the original user
connected to PostgreSQL as. So PostgreSQL will reject the connection.

I'm not sure how to solve this. "Use libpq" isn't an answer here, it
still wouldn't give us any way to acquire creds that'd permit connecting
safely.

Stashing creds in the Registry or a file would work, but would require a
.pgpass-like setup with matching for hostname/port/etc, since MSDTC
might be co-ordinating transactions for multiple Pg servers on multiple
machines. It'd also mean that MSDTC XA support for Pg would require
configuration, which it never has before. Anything else running as
NETWORKSERVICE could then steal these creds. Because you can only COMMIT
PREPARED a tx that you created unless you're superuser, they'd have to
be superuser creds too.

Trusting NETWORKSERVICE to SSPI-authenticate as anybody seems pretty
horrible - if Pg even has wildcard user mapping for SSPI that'll work
for this, I haven't checked yet. Anything could be running as
NETWORKSERVICE.

The only thing I can think of that seems secure in the face of other
things running as NETWORKSERVICE is to have a special cookie stored in
the DTC transaction context that PostgreSQL accepts as a one-time
password for a connection that can *only* commit or rollback that
particular prepared transaction. That'd require some pretty significant
changes to Pg core, so it's hardly a lightweight option.

Ideas?

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

sunpeng | 4 Jul 02:58 2014
Picon

Using VC2008 to store bytea, I got AppendChunk error 800a0c93

My server is pg 9.1.13 in win8 64, my client is win xp 32 bit
installed psqlodbc_09_01_0200.zip(ide is VS2008, vc++).
When I store pic binary data to bytea (field is CapturePic) using:
try
 	{
 		m_pRecordset->AddNew();
		iTimes++;

 		VARIANT bitdata[3];

 		SAFEARRAY *psafe[3] = {NULL, NULL, NULL};
 		SAFEARRAYBOUND  band[3];

		if (pFreImg != NULL && FreImgSize > 0)
		{
			bitdata[0].vt = VT_ARRAY | VT_UI1;
			band[0].cElements = FreImgSize;
			band[0].lLbound = 0;
			psafe[0] = SafeArrayCreate(VT_UI1, 1, &band[0]);

			for (long iSize = 0; iSize < FreImgSize; iSize++)
			{
				SafeArrayPutElement(psafe[0], &iSize, &pFreImg[iSize]);
			}

			bitdata[0].parray = psafe[0];
			if (bitdata[0].parray != NULL)
			{
				m_pRecordset->GetFields()->GetItem("CapturePic")->AppendChunk(&bitdata[0]);
			}
...
                 m_pRecordset->Update();
...
}catch()

I got com_error (at line ...>AppendChunk() ),and see in AppendChunk():
HRESULT _hr = raw_AppendChunk(data);
I got: hresult 800a0c93

see http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0c93-errors.html

how to resolve it?

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

matevzm | 3 Jul 16:03 2014
Picon

Connction string lacks some options

I am having trouble making DSN for psqlodbcw.so driver. 

When I try to test it with iodbctest I get error (I can connect openlink
drivers but I cannot use them because they do not support mxODBC):

Driver Manager: 03.52.0709.0909

Enter ODBC connect string (? shows list): ?

DSN                              | Driver                                  
------------------------------------------------------------------------------
DSNana                           | psqlDriver                              
DSNopen                          | OpenLink PostgreSQL Lite Driver v6.0    
DSNopen2                         | OpenLink PostgreSQL Lite Driver (Unicode)
v6.0

Enter ODBC connect string (? shows list): DSNana
1: SQLDriverConnect = connction string lacks some options (202)
SQLSTATE=08001

Have a nice day.

---------

*ODBC.ini*

[ODBC Data Sources]
DSNana  = psqlDriver
DSNopen = OpenLink PostgreSQL Lite Driver v6.0
DSNopen2 = OpenLink PostgreSQL Lite Driver (Unicode) v6.0

[DSNana]
Driver     = /Library/PostgreSQL/psqlODBC/lib/psqlodbcw.so
ServerName = localhost
Database   = ANA
UserName   = matevzmrak
Port       = 5432

[DSNopen]
Driver     = /Library/ODBC/OpenLink PostgreSQL Lite ODBC Driver
v6.0.bundle/Contents/MacOS/pgr7_mt_lt.so
ServerName = localhost
Port       = 5432
Database   = ANA
Username   = matevzmrak
Debug      = 1

[DSNopen2]
Driver     = /Library/ODBC/OpenLink PostgreSQL Lite ODBC Driver (Unicode)
v6.0.bundle/Contents/MacOS/pgr7_mt_lu.so
ServerName = localhost
Port       = 5432
Database   = ANA
Username   = matevzmrak
Debug      = 1

*ODBCINST.ini*

[ODBC Drivers]
psqlDriver                                     = Installed
OpenLink PostgreSQL Lite Driver v6.0           = Installed
OpenLink PostgreSQL Lite Driver (Unicode) v6.0 = Installed

[psqlDriver]
Driver = /Library/PostgreSQL/psqlODBC/lib/psqlodbcw.so

[OpenLink PostgreSQL Lite Driver v6.0]
Driver = /Library/ODBC/OpenLink PostgreSQL Lite ODBC Driver
v6.0.bundle/Contents/MacOS/pgr7_mt_lt.so
Setup  = /Library/ODBC/OpenLink PostgreSQL Lite ODBC Driver
v6.0.bundle/Contents/MacOS/OpenLink PostgreSQL Lite ODBC Driver v6.0

[OpenLink PostgreSQL Lite Driver (Unicode) v6.0]
Driver = /Library/ODBC/OpenLink PostgreSQL Lite ODBC Driver (Unicode)
v6.0.bundle/Contents/MacOS/pgr7_mt_lu.so
Setup  = /Library/ODBC/OpenLink PostgreSQL Lite ODBC Driver (Unicode)
v6.0.bundle/Contents/MacOS/OpenLink PostgreSQL Lite ODBC Driver (Unicode)
v6.0

*Specs:*

Postgres server: Postgres app 9.3.4.2 (http://postgresapp.com/)
ODBC manager: iODBC Data Source Manager (OpenLink)
ODBC driver: http://www.postgresql.org/ftp/odbc/versions/src/ (latest) -
psqlodbc-09.03.0300.tar.gz
OSX version: 10.9.3
UCS: UCS2
Eclipse: Eclipse Standard/SDK - Kepler Service Release - Build id:
20140224-0627
Python: 2.7.6 - 64bit
mxBase version: egenix-mx-base-3.2.7-py2.7_ucs2-macosx-10.5-x86_64-prebuilt
mxODBC version: egenix-mxodbc-3.1.2.macosx-10.5-x86_64-py2.7_ucs2.prebuilt

Thank you for your answers.

Best regards, Matevz

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Connction-string-lacks-some-options-tp5810410.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Michael Paquier | 3 Jul 08:39 2014
Picon

Facilitating page type detection

Hi all,

As raised by Horiguchi-san when discussing the WAL replay patch here,
it is now a little bit difficult to identify what is the type of a
given page:
http://www.postgresql.org/message-id/20140702.173242.27816044.horiguchi.kyotaro <at> lab.ntt.co.jp

It is of course doable, but it is not that straight-forward:
- Relation page: special area size of 0
- gist page: special area size of MAXALIGN(sizeof(GISTPageOpaqueData))
and check if last 2 bytes match GIST_PAGE_ID
- btree page: special area size of MAXALIGN(sizeof(BTPageOpaqueData))
and check if last 2 bytes match MAX_BT_CYCLE_ID
- spgist page: special area size of
MAXALIGN(sizeof(SpGistPageOpaqueData)) and check if last 2 bytes match
SPGIST_PAGE_ID
- sequence page: special area size of MAXALIGN(sizeof(sequence_magic))
and then check if SEQ_MAGIC is set (4 first bytes of the special area)
- gin page: Special area size of MAXALIGN(sizeof(GinPageOpaqueData)),
and no magic identifier of the other types found.

The identification is uniform for most of the pages: check the last
two bytes and be done luckily thanks to their default sizes (gist,
btree have a special area size of 16 bytes after alignment. Sequence,
spgist and gin have a special area of 8 bytes). By looking at the
comments of gin_private.h, it is written that we can use the size of
the special area to identify that it is a gin page. But that's not
really cool, especially if we add in the future new page types that
have similar characteristics like same special area size, without a
magic identifier.

For relations, I guess that using the special area size is enough, but
I think that we could do better for sequences and gin indexes, by for
example adding an identifier at the end of the page...
In order to do that, the cleanest solution would be to add a dedicated
identifier area at the end of the page for non-relation pages, but
this induces a on-disk format change. Something usually rejected
AFAIK.

Regards,
-- 
Michael

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Craig Ringer | 3 Jul 08:07 2014

[PATCHSET] Docs, old build files changes

Hi all

I have a set of patches that should be able to be applied without any
real concern, to:

- Better document arch settings in windows compilation;

- Document MSDTC / PGXALIB.DLL tracing

- Delete the broken and old Make.bat (and delete MakeX64.bat too, which
  works but is a THIRD way to build installers - I think powershell and
  installer.mak is enough). Remove docs that refer to them.

- Eliminate the copy and paste coding in the powershell installer
  creation scripts. Keep the same entry points, but share common code.

- Get rid of duplicate readme_winbuild.txt

- Remove stray "execute" bits

You can pull
windows-docs-build-patches for the changes or apply the attached with
"git am" (except the execute bits one).

I found getting started with psqlODBC rather confusing, because of all
the duplicated build options, some of which were broken. This should
help a little, though it still leaves NMake and Powershell. Sounds like
that can be dealt with after the 0904 release, but this should be safe
to patch in before 0904 to make the final 0904 release a bit tidier.

Thanks!

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Gmane