Insert empty string into Database

lihofm commented 1 year ago

Hi,

1. What versions are you using?

Database version: Oracle Database 12c (12.1.0.2.0)

platform.platform: Windows-10-10.0.19044-SP0 sys.maxsize > 2**32: True platform.python_version: 3.10.9

oracledb.version: 1.2.1 cx_Oracle.version: 8.3.0

2. Is it an error or a hang or a crash?

error

3. What error(s) or behavior you are seeing?

When I am trying to insert an empty string into my database the input parameters after the empty string are getting shifted by one to the left. This leads to the following strange behavior:

Data will be inserted into the wrong database field
The types don't match (e.g. a string will be inserted into a number-field)
The value is too large for the field.

Here is an example of case 3: On Database:

CREATE TABLE tmp_table(
  short_field VARCHAR2(1),
  long_field VARCHAR2(50)
);

In Python:

data = [{
    'SHORT_FIELD': '', # empty string
    'LONG_FIELD': 'Loooong string'
}]
insert_statement = 'INSERT INTO tmp_table(SHORT_FIELD, LONG_FIELD) VALUES(:SHORT_FIELD, :LONG_FIELD)'

with get_database_connection() as connection:
    with connection.cursor() as cursor:
        cursor.executemany(insert_statement, data)
    connection.commit()

After executing the python code I will get the following error: oracledb.exceptions.DatabaseError: ORA-12899: value too large for column "TMP_TABLE"."SHORT_FIELD" (actual: 14, maximum: 1)

So the function executemany tried to insert the wrong value ('Loooong string') into the field short_field. Even though I defined a value for the field.
When executing the code with the function execute I get the same error.

I also tried to insert an empty string with the python package cx_oracle and it works.

4. Does your application call init_oracle_client()?

No

5. Include a runnable Python script that shows the problem.

see above

Thank you!

anthony-tuininga commented 1 year ago

I am unable to replicate your issue using the example you showed above. Can you send me the packet output for your case? I don't need everything. I just need the packet containing the SQL statement so I can see what is being sent to the database. Thank you!

lihofm commented 1 year ago

Hi Anthony,

thank you for your reply. I am not sure I fully understand what you mean with packet output. How do I get the packet output?

I tried to debug the call of executemany with the paramaters from above but I can not find the position where the manipulated sql statement can be found.

An alternative way of reproducing my issue is by using the function execute instead of executemany. Maybe this works for you:

insert_statement = 'INSERT INTO tmp_table(SHORT_FIELD, LONG_FIELD) VALUES(:SHORT_FIELD, :LONG_FIELD)'

with get_database_connection() as connection:
    with connection.cursor() as cursor:
        cursor.execute(insert_statement, ['', 'Loooooong string'])
    connection.commit()

To reproduce the error the order of the parameters is important. SHORT_FIELD must come before LONG_FIELD. If the empty string comes after the value 'Loooooong string' no error will be thrown.

Maybe the stack trace helps you:

Traceback (most recent call last):
  File "...\my_main.py", line 12, in <module>
    cursor.executemany(insert_statement, data)
  File "...\venv\lib\site-packages\oracledb\cursor.py", line 439, in executemany
    self._impl.executemany(self, num_execs, bool(batcherrors),
  File "src\oracledb\impl/thin/cursor.pyx", line 169, in oracledb.thin_impl.ThinCursorImpl.executemany
  File "src\oracledb\impl/thin/protocol.pyx", line 383, in oracledb.thin_impl.Protocol._process_single_message
  File "src\oracledb\impl/thin/protocol.pyx", line 384, in oracledb.thin_impl.Protocol._process_single_message
  File "src\oracledb\impl/thin/protocol.pyx", line 377, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-12899: value too large for column "TMP_TABLE"."SHORT_FIELD" (actual: 14, maximum: 1)

Kind regards, Lisa

sijms commented 1 year ago

Hi @lihofm i try you code and the code is executed without error please note that oracle parameter is positional by default so: this will work

cursor.execute('INSERT INTO tmp_table(SHORT_FIELD, LONG_FIELD) VALUES(:SHORT_FIELD, :LONG_FIELD)', ['', 'Loooooong string'])

and this will fail

cursor.execute('INSERT INTO tmp_table(SHORT_FIELD, LONG_FIELD) VALUES(:LONG_FIELD, :SHORT_FIELD)', ['Loooooong string', ''])

also this will fail

cursor.execute('INSERT INTO tmp_table(SHORT_FIELD, LONG_FIELD) VALUES(:SHORT_FIELD, :LONG_FIELD)', ['Loooooong string', ''])

so i think better to close this issue because it is not an issue of the driver

anthony-tuininga commented 1 year ago

@sijms, what you say is correct but I think misses the point! The code supplied by @lihofm is correct.

@lihofm, I tried your new code which is much simpler but doesn't fail for me, either! You can enable packet output by setting the environment variable PYO_DEBUG_PACKETS to any value before running your script. Mine looks like this:

0000 : 00 00 00 DC 06 00 00 00 |........|
0008 : 00 00 03 5E 03 02 80 29 |...^...)|
0016 : 00 01 01 59 01 01 0D 00 |...Y....|
0024 : 00 00 01 01 04 7F FF FF |........|
0032 : FF 01 01 02 00 00 00 00 |........|
0040 : 00 00 00 00 00 01 00 00 |........|
0048 : 00 00 00 00 00 00 00 00 |........|
0056 : 00 00 00 00 00 59 49 4E |.....YIN|
0064 : 53 45 52 54 20 49 4E 54 |SERT.INT|
0072 : 4F 20 69 73 73 75 65 5F |O.issue_|
0080 : 31 33 35 20 28 53 48 4F |135.(SHO|
0088 : 52 54 5F 46 49 45 4C 44 |RT_FIELD|
0096 : 2C 20 4C 4F 4E 47 5F 46 |,.LONG_F|
0104 : 49 45 4C 44 29 0A 20 20 |IELD)...|
0112 : 20 20 20 20 20 20 56 41 |......VA|
0120 : 4C 55 45 53 28 3A 53 48 |LUES(:SH|
0128 : 4F 52 54 5F 46 49 45 4C |ORT_FIEL|
0136 : 44 2C 20 3A 4C 4F 4E 47 |D,.:LONG|
0144 : 5F 46 49 45 4C 44 29 01 |_FIELD).|
0152 : 01 01 01 00 00 00 00 00 |........|
0160 : 00 00 02 80 00 00 00 00 |........|
0168 : 01 01 00 00 02 3E 80 00 |.....>..|
0176 : 00 00 00 02 03 69 01 00 |.....i..|
0184 : 00 01 01 00 00 01 40 00 |......@.|
0192 : 00 00 00 02 03 69 01 00 |.....i..|
0200 : 00 07 00 10 4C 6F 6F 6F |....Looo|
0208 : 6F 6F 6F 6E 67 20 73 74 |ooong.st|
0216 : 72 69 6E 67             |ring    |

Note that I renamed my table to issue_135 but otherwise it is identical. Please let me know what yours looks like! I note that this is an older database (12.1.0.2). I'll see if someone internal can set up a database with that version to see if it replicates there.

If you call init_oracle_client() in your code that does that resolve your problem?

lihofm commented 1 year ago

Hi @anthony-tuininga @sijms ,

thank you for your reply!

This is my packet output using executemany():

0000 : 00 00 00 C9 06 00 00 00 |........|
0008 : 00 00 03 5E 03 02 80 29 |...^...)|
0016 : 00 01 01 50 01 01 0D 00 |...P....|
0024 : 00 00 01 01 04 7F FF FF |........|
0032 : FF 01 01 02 00 00 00 00 |........|
0040 : 00 00 00 00 00 01 00 00 |........|
0048 : 00 00 00 00 00 00 49 4E |......IN|
0056 : 53 45 52 54 20 49 4E 54 |SERT.INT|
0064 : 4F 20 74 6D 70 5F 74 61 |O.tmp_ta|
0072 : 62 6C 65 28 53 48 4F 52 |ble(SHOR|
0080 : 54 5F 46 49 45 4C 44 2C |T_FIELD,|
0088 : 20 4C 4F 4E 47 5F 46 49 |.LONG_FI|
0096 : 45 4C 44 29 20 56 41 4C |ELD).VAL|
0104 : 55 45 53 28 3A 53 48 4F |UES(:SHO|
0112 : 52 54 5F 46 49 45 4C 44 |RT_FIELD|
0120 : 2C 20 3A 4C 4F 4E 47 5F |,.:LONG_|
0128 : 46 49 45 4C 44 29 01 01 |FIELD)..|
0136 : 01 01 00 00 00 00 00 00 |........|
0144 : 00 02 80 00 00 00 00 01 |........|
0152 : 01 00 00 02 3E 80 00 00 |....>...|
0160 : 00 00 02 03 69 01 00 01 |....i...|
0168 : 01 00 00 01 40 00 00 00 |....@...|
0176 : 00 02 03 69 01 00 07 00 |...i....|
0184 : 10 4C 6F 6F 6F 6F 6F 6F |.Loooooo|
0192 : 6E 67 20 73 74 72 69 6E |ng.strin|
0200 : 67                      |g       |

I tested my code using init_oracle_client(). When I call the function it seems to work. If I do not call it I get the error shown above (ORA-12899). Have you tried executing my code without calling init_oracle_client()?

In my not working program I am connecting to the database using the following code:

def get_database_connection(host_name, port, sid, user_name, user_password):
    try:
        dsn = makedsn(host_name, port, sid)
        connection = connect(user=user_name, password=user_password, dsn=dsn)
        return connection
    except Exception:
        print("do something")

Is using init_oracle_client() mandatory for this operation?

anthony-tuininga commented 1 year ago

Thanks for supplying the packet output. That looks exactly like mine, except for the differences due to the fact that you are using 12.1.0.2.0, so I'll have to see if I can replicate with that older database.

The call to init_oracle_client() causes the driver to use "thick mode", which is identical to cx_Oracle in how it executes SQL in the database. Getting the packet output can only occur in thin mode (without using Oracle tracing techniques). Running in thick mode (by calling init_oracle_client()) should not be necessary. I would like to get to the bottom of this if possible before making a new release!

anthony-tuininga commented 1 year ago

Ok. I can replicate the problem with a 12.1.0.2.0 database. Hopefully I can get to the bottom of the issue now!

lihofm commented 1 year ago

Hi @anthony-tuininga , Thank you very much for testing it on an older database version! I hope that you can find the issue.

anthony-tuininga commented 1 year ago

I was able to determine the source of the issue! If you are able to build from source you can verify that it corrects your issue as well.

Now that I know the source of the issue I can supply a few workarounds:

use the value None instead of the value ''
reorder your binds to put the "long" value(s) last (it is considered long because the default size is 4000 characters which, due to potential character expansion means 4000x4 = 16000 bytes)
create a variable and specify the size
upgrade your database :-)

lihofm commented 1 year ago

Hi @anthony-tuininga ,

I tested my code (with execute and executemany) using the oracleDB-version built from source. It worked for me. So the issue is resolved and can therefore be closed. Thank you very much for your help!

anthony-tuininga commented 1 year ago

This has been included in version 1.3.0 which was just released.

oracle / python-oracledb