0

I am trying to query a table from a Oracle 9i db, using Python with jaydebeapi. It connects successfully, the cursor and fecth seems to work well, but when I display the results, only the numeric fields of the table are available.

I tried some Oracle functions like TO_CHAR, SUBSTR and TRIM to see if changing its value could help showing it, but none of them worked.

import jaydebeapi
import jpype
import os

sql = "select 0 as order, ST_CODE_PDV, ST_NAME_PDV, ST_CHANNEL,  \
                 ST_ACCESS, 'Safra', sum(ACTIVATION), sum(FRAUD) \
          from MY_TABLE \
          WHERE ST_CODE_PDV <> '-1' \
          HAVING sum(FRAUD) > 0   \
          group by ST_CODE_PDV, ST_NAME_PDV, ST_CHANNEL, ST_ACCESS, 'Safra'"

try:
    jar = os.getcwd()+'\ojdbc8.jar'
    args = '-Djava.class.path=%s' % jar
    jpype.startJVM("C:\\Program Files\\Java\\jre1.8.0_121\\bin\\server\\jvm.dll", args)

    cn = jaydebeapi.connect('oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@dbserverinfo', ["user","pass"])

    print("Connection Successful")

    curs = cn.cursor()
    curs.execute(sql)

    result = curs.fetchall()

    for r in result:
        print(r)

    curs.close()
    cn.close()

except Exception as e:
    print("connection error:", e)

The expected result is:

(0.0, 'NE99_TSR02X_TS9876_XY001', 'STARK TELECOM', 'AGENT SALES', '', 'Safra', 6.0, 1.0)

But my code displays:

(0.0, '', '', '', '', 'Safra', 6.0, 1.0)

The fields that do not display any results are VARCHAR2 type. The string 'Safra' is displayed, but it's hardcoded in my query.

I tried other tables, but the problem is the same (only numeric fields are returned).

What may be happening?

Nikaido
  • 4,443
  • 5
  • 30
  • 47
Ricardo Junior
  • 1
  • 1
    Does the query produce the expected result when run in SQL developer or other DB client? – Simon Notley Oct 02 '19 at 14:42
  • Yes, it works fine in SQL Developer. I am trying to use Python to query and export the results in a "single command." – Ricardo Junior Oct 02 '19 at 15:39
  • Is there a reason you're using jaydebeapi? It doesn't look like it's been updated for some time, maybe try swapping out for cx_oracle? – Simon Notley Oct 02 '19 at 16:12
  • cx_Oracle does not work with Oracle9i. In my researches, jaydebeapi was the only one that managed to establish a connection with this version of Oracle. – Ricardo Junior Oct 02 '19 at 16:16
  • Ah OK, looks like you may have found a bug, but in a library that's rarely updated using an oracle version that went out of support ten years ago, I doubt there's much chance of a fix unless you're able to diagnose it yourself. If jaydebe is written in python you might have some luck with a debugger. You could also try wireshark on the connection to see whether the data ever leaves the database if it's in a readable form. – Simon Notley Oct 02 '19 at 16:41
  • The fixed string 'Safra' would be a CHAR rather than VARCHAR2 so you could try SELECT CAST('X' AS VARCHAR2(10)) x, CAST('Y' AS CHAR2(10)) y FROM DUAL to see if it is a datatype issue. Failing that, try a java program using the ojbc8 and see if the issue is in that component. – Gary Myers Oct 03 '19 at 03:35
  • You are right, the problem is the VARCHAR2-type field. The SELECT above returned the same problem ('X' returned as '' and 'Y' returned as 'Y '). Thanks a lot! – Ricardo Junior Oct 03 '19 at 16:44

0 Answers0