PxPlus User Forum

Twitter Twitter Twitter

Author Topic: PXPlus SQL ODBC Driver w/ Command line  (Read 637 times)

michaelgreer

  • Gold Member
  • ****
  • Posts: 51
    • View Profile
PXPlus SQL ODBC Driver w/ Command line
« on: April 17, 2020, 10:51:28 AM »
I have attempted installing both the 7.00 and 6.10 version of the above on a Redhat 7 system.
 cat os-release
NAME="Red Hat Enterprise Linux Server"
VERSION="7.8 (Maipo)"
ID="rhel"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.8"
PRETTY_NAME="Red Hat Enterprise Linux"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:7.8:GA:server"
HOME_URL="https://www.redhat.com/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"

REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 7"
REDHAT_BUGZILLA_PRODUCT_VERSION=7.8
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="7.8"

From the command line I can do a list command and a select command with no problem, but as soon as I add a where clause I see this:

The driver reported the following diagnostics whilst running ODBCExecDirect
S0000:1:1019:[PxPlus][SQL ODBC Driver]Internal error

I added -l odbc.log and it has this:

Driver version: 7.00.0000
ISAMOpen: UserID='root', length=4
------------------------------
select company,library from SM_UNFORM_LIBRARY_PATHS where company = 01

GetAccessibleTables:<table=SM_UNFORM_LIBRARY_PATHS> Open catalog(s) /infor/facts78 , available 710 table(s)
<SM_UNFORM_LIBRARY_PATHS> Open file /infor/facts78/data/SM/SMUNFP, maxksz=20
ISAMCloseTable: Closing table SM_UNFORM_LIBRARY_PATHS - 0 file reads, 0 re-reads, 0 writes, 0 removes
     CACHE STATS - 0 reads, size 2048 estimated, 0 used
ISAMClose

This is so close to working as I need it, but obviously this last needs to work.

Any input?
Michael

James Zukowski

  • Diamond Member
  • *****
  • Posts: 157
    • View Profile
Re: PXPlus SQL ODBC Driver w/ Command line
« Reply #1 on: April 17, 2020, 10:57:24 AM »
Doesn't the company# need to be quoted?
James Zukowski
Programmer / ERP Analyst
Beauty Enterprises, Inc.
150 Meadow St.
Hartford, CT 06114
860.296.9303  Ext 231

michaelgreer

  • Gold Member
  • ****
  • Posts: 51
    • View Profile
Re: PXPlus SQL ODBC Driver w/ Command line
« Reply #2 on: April 17, 2020, 01:04:26 PM »
James,  I have tried 01, '01' and "01".  The log always strips them off. In all cases I get the same result in the log.  Thanks!

Devon Austen

  • Staff
  • Diamond Member
  • *****
  • Posts: 266
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: PXPlus SQL ODBC Driver w/ Command line
« Reply #3 on: April 17, 2020, 01:27:58 PM »
What is the command you are using when you get the error?

i.e. pxpsql -d "/home/myuser/app/data" "select * from 'mytable' where field1='this'"
Software Developer for PVX Plus Technologies LTD.

michaelgreer

  • Gold Member
  • ****
  • Posts: 51
    • View Profile
Re: PXPlus SQL ODBC Driver w/ Command line
« Reply #4 on: April 17, 2020, 04:47:14 PM »
Devon,

here, with all variations on the "= 01" part:

 /usr/pxpsqlodbc/pxpsql -d /infor/facts78 select company,library from SM_UNFORM_LIBRARY_PATHS where company = "01"

This works just fine without the where clause.

James Zukowski

  • Diamond Member
  • *****
  • Posts: 157
    • View Profile
Re: PXPlus SQL ODBC Driver w/ Command line
« Reply #5 on: April 17, 2020, 04:53:20 PM »
Michael:
Silly question... Is the actual company data "01"? No spaces or other characters, exactly 2 digits, etc. I know FACTS is picky about it to begin with, and that should be the case. Considering your challenges... back to "is it plugged in?"
James Zukowski
Programmer / ERP Analyst
Beauty Enterprises, Inc.
150 Meadow St.
Hartford, CT 06114
860.296.9303  Ext 231

Devon Austen

  • Staff
  • Diamond Member
  • *****
  • Posts: 266
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: PXPlus SQL ODBC Driver w/ Command line
« Reply #6 on: April 17, 2020, 04:53:47 PM »
Try

/usr/pxpsqlodbc/pxpsql -d /infor/facts78 "select company,library from SM_UNFORM_LIBRARY_PATHS where company = 01"

The pxpsql program is expecting the SQL as one argument.
Software Developer for PVX Plus Technologies LTD.

michaelgreer

  • Gold Member
  • ****
  • Posts: 51
    • View Profile
Re: PXPlus SQL ODBC Driver w/ Command line
« Reply #7 on: April 20, 2020, 03:00:44 PM »
Devon,

You nailed it.  Only thing I had to do was put 01 as '01'.  Then, perfect.

Thanks!

Devon Austen

  • Staff
  • Diamond Member
  • *****
  • Posts: 266
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: PXPlus SQL ODBC Driver w/ Command line
« Reply #8 on: April 20, 2020, 03:23:31 PM »
Michael,

I am glad you got it working  :)
Software Developer for PVX Plus Technologies LTD.

michaelgreer

  • Gold Member
  • ****
  • Posts: 51
    • View Profile
Re: PXPlus SQL ODBC Driver w/ Command line
« Reply #9 on: April 22, 2020, 12:38:09 PM »
Devon,
One more question.  I am running the command line client on the same system as the database so I do not have the sql server running and it seems to work just fine *except* is appears not to see the non-normalized file.  Is that possible to enable, and even better, is there some documentation on the command line client that I somehow missed.

Thanks!
Michael

Devon Austen

  • Staff
  • Diamond Member
  • *****
  • Posts: 266
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: PXPlus SQL ODBC Driver w/ Command line
« Reply #10 on: April 22, 2020, 01:48:37 PM »
THe pxpsql uses the data dictionary to determine which tables can be accessed. See documentation here for details: https://manual.pvxplus.com/?odbc/table_definitions/pxplus_data_dictionary.htm

You can add a * to the beginning of a table name to hide it from the ODBC driver.

Info about how to use the pxpsql command can be found here: https://manual.pvxplus.com/?odbc/pxplus_sql_cmd_line_client.htm

you can also use pxpsql --help from the command line.
Software Developer for PVX Plus Technologies LTD.