PxPlus User Forum

Twitter Twitter Twitter

Author Topic: ODBC and views not working  (Read 765 times)

Danny_S

  • Member
  • **
  • Posts: 6
    • View Profile
ODBC and views not working
« on: August 02, 2022, 08:37:18 AM »
Hi all,

I'm having trouble getting data from Views using the ODBC driver.

I'm using the client/server ODBC, version 7, where the server is RedHat 6, 64-bit, and the client ODBC version 7, and Windows 10.
(also tried version 6.1). Pvx version is 14.10, but also tried 18.20

Using the ODBC to get data direct from data-files works, but by using the Views, there comes a message that there are no visible coloms.

Pxp is in /opt/pxp, and the ODBC is installed in /pvxapps/odbc.

The data is in /pvxapps/livedata, and the views-files are in /pvxapps/livedata/views

In de pxpsqlsvr.conf I have defined 2 catalogs :

   Ref_Prod_Data=[/pvxapps/liveprog][][/pvxapps/livedata,/pvxapps/livehist,/pvxapps/livemenu,/pvxapps/syspvx]
   and
   Ref_Prod_Views=*[/pvxapps/livedata/views][][/pvxapps/livedata,/pvxapps/livehist,/pvxapps/livemenu,/pvxapps/syspvx]

and the View Library Path is set to 'ViewLib=/opt/pxp'.

The example view I created has a data-source with a physical file, actcodf0, which is in /pvxapps/livedata. I tried it with just the filename, and the full path in de datasource, but no luck. Also no embedded io with the datafile.

The Ref_Prod_Data returns data, and the Ref_Prod_Views returns the View-names, but no data.

I have tried mixed combination of 32-bits and 64-bits, but all results are the same

The libpxpio.so is copied to /usr/lib64

I got a feeling I'm missing something...anybody any idea?

Thanks, Danny

Devon Austen

  • Administrator
  • Diamond Member
  • *****
  • Posts: 382
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: ODBC and views not working
« Reply #1 on: August 02, 2022, 10:58:28 AM »
As long as the PxPlus pointed to via ViewLib= in the pxpsqlsvr.conf is the same architecture (i.e. 64-bit or 32-bit) as the installed PxPlus SQL Server then you should be able to access view.

For example I would setup a PxPlus SQL Server and the PxPlus that are both 64-bit.

Another thing to rule out is Excel not liking the views. You can use the PxPlus SQL command line client to test a query against the view to see if you get back the data. If you do then the issue is Excel does not like something about the view. If that is the case there is a different way from excel to access the ODBC and therefore view that may work.

C:\PVX Plus Technologies\SQL ODBC Driver (64-bit)\pxpsql.exe --catalog Ref_Prod_Views -s 123.123.123.123 -p 4093 "SELECT * FROM 'View: actcodf0'"
Principal Software Engineer for PVX Plus Technologies LTD.

Danny_S

  • Member
  • **
  • Posts: 6
    • View Profile
Re: ODBC and views not working
« Reply #2 on: August 03, 2022, 09:28:41 AM »
Devon,

Thanks for your suggestions. After checking all versions and re-checking....it still didn't work.. >:( >:( >:(

Until it got to me..I installed RedHat 6 Pxplus and ODBC on a RedHat 7 machine.. :-[ :-[ :-[

After fixing that it works like a charm. :)

Mike King

  • Diamond Member
  • *****
  • Posts: 3799
  • Mike King
    • View Profile
    • BBSysco Consulting
Re: ODBC and views not working
« Reply #3 on: August 03, 2022, 09:34:43 AM »
Yes --- That would do it.   OpenSSL versions between Redhat 6 and 7 are quite different and the compiles we use take that info account.

Mike King
President - BBSysco Consulting
eMail: mike.king@bbsysco.com