PXPlus SQL ODBC Driver w/ Command line

[Michael Greer]

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]

Doesn't the company# need to be quoted?

[Michael Greer]

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]

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'"

[Michael Greer]

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]

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?"

[Devon Austen]

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.

[Michael Greer]

Devon,

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

Thanks!

[Devon Austen]

Michael,

I am glad you got it working 

[Michael Greer]

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]

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.