Insert multi cannot insert decimal into text field about pyexasol HOT 8 CLOSED

Hello.

Ok, the real error was:

pyexasol.exceptions.ExaRequestError: 
(
    message  =>  getString: JSON value is not a string. (Session: 1665483672788760589)
    dsn      =>  exasol-dev.mlan:8563
    user     =>  SYS
    schema   =>  PYEXASOL_TEST
    code     =>  00000
)

I'll push an update to display this error properly.

The problem is related to data types. If you have column NNUMBER with type VARCHAR, Exasol prepared statement expects you to send string as data for such column. And if you have type DECIMAL or DOUBLE PRECISION, Exasol expects you to send number. Basically, this is how Exasol WebSocket protocol works at this moment.

We cannot handle it on client side, since it will require checking table structure in advance and full iteration over all rows, which is too expensive.

In my opinion, it's much better to use .import_from_pandas() in most of cases and avoid INSERT with prepared statements altogether.

from pyexasol.

Error message will be displayed properly starting from 0.12.1. This version is available now.

from pyexasol.

Thanks. Upgraded my version

Also, a second issue when inserting null value into decimal numbers:

CREATE TABLE SAMPLE_SCHEMA.TABLE1(
NNUMBER NUMERIC(20,10),
WWORD VARCHAR(20000)
)

Code:

import pyexasol
import pandas as pd
print(pyexasol.__version__)
print(pd.__version__)

def get_connection():
    conn = pyexasol.connect(
        dsn="DSN",
        user="USERNAME",
        password="PASSWORD")
    return conn

tuple_list = [
    (3.1415926, "AAA"),("", "BBB")
]
# Empty string to represent missing value
df = pd.DataFrame(tuple_list, columns=["NNUMBER", "WWORD"])
conn = get_connection()

conn.import_from_pandas(df, ("SAMPLE_SCHEMA", "TABLE1"))
conn.commit()
print("Step 1 OK")

conn.ext.insert_multi(("SAMPLE_SCHEMA", "TABLE1"),tuple_list,columns=list(df.columns))
conn.commit()
print("Step 4 OK")  # <-- Will never be reached.

Output:

0.12.1
1.0.3
Step 1 OK
Error.......

The error goes away if I use None instead of empty string "" to represent the error

I get an error message thatlooks like this:

pyexasol.exceptions.ExaCommunicationError: 
(
    message  =>  Connection is already closed.
    dsn      =>  ................
    user     =>  .............
    schema   =>  

from pyexasol.

Huh, interesting! I'll take a look today or tomorrow.

It might be possible that NULLs (as None values) are not properly supported by Exasol prepared statements.

Switching to .import_from_pandas() should definitely help with this issue.

from pyexasol.

@CHBEXD , ok, I've tested the input value for DECIMAL column with empty string.

It actually causes crash in SQLProcess.

The error message is:

11.05 19:07:24.932 �[31;1mERROR �[0mCC: Thread will be terminated because an std::exception has been caught: [22018] invalid character value for cast; input is empty or spaces only
11.05 19:07:24.932 �[33mEXAOP_WARNING Internal error in SqlProcess of session: 1666422190107996613.�[0m

###########################################
11.05 19:07:24
Program received signal 15 (SIGTERM) because of tkill() or tgkill() (-).
Signal was raised by this process.

Since the problem is on server side, I'll raise a ticket for Exasol support. I guess, it has to be a proper error message instead of crash.

Thank you.

from pyexasol.

I'll post a link to the EXA ticket when it will be available for the public access.

from pyexasol.

@CHBEXD , ticket is now available: https://www.exasol.com/support/browse/EXASOL-2702

Thank you.

from pyexasol.

Exasol ticket was resolved. I'll close this ticket. Fix should be applied on Exasol server side, nothing to do on client.

from pyexasol.