search

oracle / python-oracledb

Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle
https://oracle.github.io/python-oracledb
Other
308 stars 61 forks source link

Problem binding empty string values to proper columns #236

Closed pavelbrziak closed 1 month ago

pavelbrziak commented 9 months ago

1. What versions are you using?

platform.platform: Windows-10-10.0.19045-SP0 sys.maxsize > 2**32: True platform.python_version: 3.11.3 oracledb.version: 1.3.1

Oracle DB EE: 19.0.0.0.0

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

Error

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

I already reported this error in /issues/207, but I made a mistake when obsfucating the data, which resulted in wrong order of columns when creating the table. In this report, I'm providing (hopefully) corrected DDL script and also stdout of call with enabled PYO_DEBUG_PACKETS.

When running insert into table using executemany, it seems that the values are not properly bound to timestamp field. Result error is: ORA-01400: cannot insert NULL into ("DB_USER"."TABLE_1"."COL_16")

Eventhough the provided dictionary contains value for COL_16, it seems that it wasn't bound.

It seems that it is a new issue, because in version oracledb.version: 1.2.2 it was inserted properly. However, I'm not able to downgrade to 1.2.2, since it is not possible in our environment to rollback to previous versions.

We tried to avoid this issue by using positional binding and using TO_TIMESTAMP, but the behaviour remained the same.

4. Does your application call init_oracle_client()?

No

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

Table creation:

CREATE TABLE TABLE_1
(  
COL_1 NUMBER(38,0) NOT NULL ENABLE, 
COL_2 VARCHAR2(100 CHAR) NOT NULL ENABLE, 
COL_3 VARCHAR2(500 CHAR), 
COL_4 NUMBER(38,0) NOT NULL ENABLE,
COL_5 VARCHAR2(500 CHAR), 
COL_6 VARCHAR2(200 CHAR), 
COL_7 VARCHAR2(100 CHAR), 
COL_8 VARCHAR2(100 CHAR), 
COL_9 VARCHAR2(50 CHAR), 
COL_11 VARCHAR2(100 CHAR), 
COL_10 VARCHAR2(50 CHAR), 
COL_12 VARCHAR2(32 CHAR) NOT NULL ENABLE, 
COL_13 VARCHAR2(255 CHAR), 
COL_14 VARCHAR2(2000 CHAR), 
COL_15 VARCHAR2(500 CHAR),
COL_16 TIMESTAMP (6) NOT NULL ENABLE, 
COL_17 NUMBER NOT NULL ENABLE
);

Python code:

import oracledb

NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'

def insert_into(ora_conn: oracledb.Connection):
    with ora_conn.cursor() as cursor:
        cursor.execute(f"alter session set NLS_TIMESTAMP_FORMAT = '{NLS_TIMESTAMP_FORMAT}'")
        sql = "insert into TEST_1(COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_9,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15,COL_16,COL_17) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17)"
        data = []
        data.append({':1': '1002', ':2': '6546546sdf56454', ':3': 'TPS2_8sdf_212', ':4': '12', ':5': '12) Test TEXT', ':6': 'TEST Text 2', ':7': 'toto.domain.com', ':8': 'Test text - text', ':9': '12155FD4544UU', ':10': '77445522', ':11': "['OQK784AAfw']", ':12': 'tested', ':13': 'email.email@email.com', ':14': '', ':15': '', ':16': '2022-02-02 12:47:44', ':17': '1231564.784521'})
        print(f'Data process: {data}')
        cursor.executemany(sql, data, batcherrors=True)
        for error in cursor.getbatcherrors():
            print(error.message)
        ora_conn.commit()

dsn = """XXX"""

connection = oracledb.connect(user="XXX", password='XXX', dsn=dsn)

insert_into(connection)

PYO_DEBUG_PACKETS=1 (Note - from the packets, I replaced the target schema name with ****)


2023-10-04 08:42:45.627 [socket: 1028] Sending packet:
0000 : 00 00 03 08 06 00 00 00 |........|
0008 : 00 00 03 5E 04 04 00 08 |...^....|
0016 : 80 29 00 01 01 DB 01 01 |.)......|
0024 : 0D 00 00 00 01 01 04 7F |........|
0032 : FF FF FF 01 01 11 00 00 |........|
0040 : 00 00 00 00 00 00 00 01 |........|
0048 : 00 00 00 00 00 00 00 00 |........|
0056 : 00 00 00 00 00 00 00 DB |........|
0064 : 69 6E 73 65 72 74 20 69 |insert.i|
0072 : 6E 74 6F 20 42 43 41 4D |nto.****|
0080 : 2E 54 41 42 4C 45 5F 31 |.TABLE_1|
0088 : 28 43 4F 4C 5F 31 2C 43 |(COL_1,C|
0096 : 4F 4C 5F 32 2C 43 4F 4C |OL_2,COL|
0104 : 5F 33 2C 43 4F 4C 5F 34 |_3,COL_4|
0112 : 2C 43 4F 4C 5F 35 2C 43 |,COL_5,C|
0120 : 4F 4C 5F 36 2C 43 4F 4C |OL_6,COL|
0128 : 5F 37 2C 43 4F 4C 5F 38 |_7,COL_8|
0136 : 2C 43 4F 4C 5F 39 2C 43 |,COL_9,C|
0144 : 4F 4C 5F 31 30 2C 43 4F |OL_10,CO|
0152 : 4C 5F 31 31 2C 43 4F 4C |L_11,COL|
0160 : 5F 31 32 2C 43 4F 4C 5F |_12,COL_|
0168 : 31 33 2C 43 4F 4C 5F 31 |13,COL_1|
0176 : 34 2C 43 4F 4C 5F 31 35 |4,COL_15|
0184 : 2C 43 4F 4C 5F 31 36 2C |,COL_16,|
0192 : 43 4F 4C 5F 31 37 29 20 |COL_17).|
0200 : 56 41 4C 55 45 53 20 28 |VALUES.(|
0208 : 3A 31 2C 20 3A 32 2C 20 |:1,.:2,.|
0216 : 3A 33 2C 20 3A 34 2C 20 |:3,.:4,.|
0224 : 3A 35 2C 20 3A 36 2C 20 |:5,.:6,.|
0232 : 3A 37 2C 20 3A 38 2C 20 |:7,.:8,.|
0240 : 3A 39 2C 20 3A 31 30 2C |:9,.:10,|
0248 : 20 3A 31 31 2C 20 3A 31 |.:11,.:1|
0256 : 32 2C 20 3A 31 33 2C 20 |2,.:13,.|
0264 : 3A 31 34 2C 20 3A 31 35 |:14,.:15|
0272 : 2C 20 3A 31 36 2C 20 3A |,.:16,.:|
0280 : 31 37 29 01 01 01 01 00 |17).....|
0288 : 00 00 00 00 00 00 02 80 |........|
0296 : 00 00 00 00 01 01 00 00 |........|
0304 : 01 10 00 00 00 00 02 03 |........|
0312 : 69 01 00 00 01 01 00 00 |i.......|
0320 : 01 3C 00 00 00 00 02 03 |.<......|
0328 : 69 01 00 00 01 01 00 00 |i.......|
0336 : 01 34 00 00 00 00 02 03 |.4......|
0344 : 69 01 00 00 01 01 00 00 |i.......|
0352 : 01 08 00 00 00 00 02 03 |........|
0360 : 69 01 00 00 01 01 00 00 |i.......|
0368 : 01 34 00 00 00 00 02 03 |.4......|
0376 : 69 01 00 00 01 01 00 00 |i.......|
0384 : 01 2C 00 00 00 00 02 03 |.,......|
0392 : 69 01 00 00 01 01 00 00 |i.......|
0400 : 01 3C 00 00 00 00 02 03 |.<......|
0408 : 69 01 00 00 01 01 00 00 |i.......|
0416 : 01 40 00 00 00 00 02 03 |.@......|
0424 : 69 01 00 00 01 01 00 00 |i.......|
0432 : 01 34 00 00 00 00 02 03 |.4......|
0440 : 69 01 00 00 01 01 00 00 |i.......|
0448 : 01 20 00 00 00 00 02 03 |........|
0456 : 69 01 00 00 01 01 00 00 |i.......|
0464 : 01 38 00 00 00 00 02 03 |.8......|
0472 : 69 01 00 00 01 01 00 00 |i.......|
0480 : 01 18 00 00 00 00 02 03 |........|
0488 : 69 01 00 00 01 01 00 00 |i.......|
0496 : 01 54 00 00 00 00 02 03 |.T......|
0504 : 69 01 00 00 01 01 00 00 |i.......|
0512 : 02 3E 80 00 00 00 00 02 |.>......|
0520 : 03 69 01 00 00 01 01 00 |.i......|
0528 : 00 02 3E 80 00 00 00 00 |..>.....|
0536 : 02 03 69 01 00 00 01 01 |..i.....|
0544 : 00 00 01 4C 00 00 00 00 |...L....|
0552 : 02 03 69 01 00 00 01 01 |..i.....|
0560 : 00 00 01 38 00 00 00 00 |...8....|
0568 : 02 03 69 01 00 00 07 04 |..i.....|
0576 : 31 30 30 32 0F 36 35 34 |1002.654|
0584 : 36 35 34 36 73 64 66 35 |6546sdf5|
0592 : 36 34 35 34 0D 54 50 53 |6454.TPS|
0600 : 32 5F 38 73 64 66 5F 32 |2_8sdf_2|
0608 : 31 32 02 31 32 0D 31 32 |12.12.12|
0616 : 29 20 54 65 73 74 20 54 |).Test.T|
0624 : 45 58 54 0B 54 45 53 54 |EXT.TEST|
0632 : 20 54 65 78 74 20 32 0F |.Text.2.|
0640 : 74 6F 74 6F 2E 64 6F 6D |toto.dom|
0648 : 61 69 6E 2E 63 6F 6D 10 |ain.com.|
0656 : 54 65 73 74 20 74 65 78 |Test.tex|
0664 : 74 20 2D 20 74 65 78 74 |t.-.text|
0672 : 0D 31 32 31 35 35 46 44 |.12155FD|
0680 : 34 35 34 34 55 55 08 37 |4544UU.7|
0688 : 37 34 34 35 35 32 32 0E |7445522.|
0696 : 5B 27 4F 51 4B 37 38 34 |['OQK784|
0704 : 41 41 66 77 27 5D 06 74 |AAfw'].t|
0712 : 65 73 74 65 64 15 65 6D |ested.em|
0720 : 61 69 6C 2E 65 6D 61 69 |ail.emai|
0728 : 6C 40 65 6D 61 69 6C 2E |l@email.|
0736 : 63 6F 6D 00 00 13 32 30 |com...20|
0744 : 32 32 2D 30 32 2D 30 32 |22-02-02|
0752 : 20 31 32 3A 34 37 3A 34 |.12:47:4|
0760 : 34 0E 31 32 33 31 35 36 |4.123156|
0768 : 34 2E 37 38 34 35 32 31 |4.784521|

2023-10-04 08:42:45.642 [socket: 1028] Receiving packet:
0000 : 00 00 00 0B 0C 20 00 00 |........|
0008 : 01 00 01                |...     |

2023-10-04 08:42:45.642 [socket: 1028] Sending packet:
0000 : 00 00 00 0B 0C 00 00 00 |........|
0008 : 01 00 02                |...     |

2023-10-04 08:42:45.643 [socket: 1028] Receiving packet:
0000 : 00 00 00 0B 0C 20 00 00 |........|
0008 : 01 00 02                |...     |

2023-10-04 08:42:45.655 [socket: 1028] Receiving packet:
0000 : 00 00 00 E7 06 00 00 00 |........|
0008 : 00 00 04 01 03 01 05 00 |........|
0016 : 02 5F 3D 01 0F 02 05 78 |._=....x|
0024 : 01 06 01 D3 02 00 00 00 |........|
0032 : 00 00 04 FF FF FF FF 00 |........|
0040 : 00 00 00 00 00 04 00 00 |........|
0048 : 00 01 01 03 02 05 78 01 |......x.|
0056 : 01 01 00 01 01 16 01 BD |........|
0064 : 3F 4F 52 41 2D 30 31 34 |?ORA-014|
0072 : 30 30 3A 20 63 61 6E 6E |00:.cann|
0080 : 6F 74 20 69 6E 73 65 72 |ot.inser|
0088 : 74 20 4E 55 4C 4C 20 69 |t.NULL.i|
0096 : 6E 74 6F 20 28 22 42 43 |nto.("BC|
0104 : 41 4D 22 2E 22 54 41 42 |AM"."TAB|
0112 : 4C 45 5F 31 22 2E 22 43 |LE_1"."C|
0120 : 4F 4C 5F 31 36 22 29 0A |OL_16").|
0128 : 00 00 02 5F 3D 00 60 4F |..._=.`O|
0136 : 52 41 2D 32 34 33 38 31 |RA-24381|
0144 : 3A 20 65 72 72 6F 72 28 |:.error(|
0152 : 73 29 20 69 6E 20 61 72 |s).in.ar|
0160 : 72 61 79 20 44 4D 4C 0A |ray.DML.|
0168 : 4F 52 41 2D 30 31 34 30 |ORA-0140|
0176 : 30 3A 20 63 61 6E 6E 6F |0:.canno|
0184 : 74 20 69 6E 73 65 72 74 |t.insert|
0192 : 20 4E 55 4C 4C 20 69 6E |.NULL.in|
0200 : 74 6F 20 28 22 42 43 41 |to.("***|
0208 : 4D 22 2E 22 54 41 42 4C |*"."TABL|
0216 : 45 5F 31 22 2E 22 43 4F |E_1"."CO|
0224 : 4C 5F 31 36 22 29 0A    |L_16"). |
pavelbrziak commented 4 months ago

I was able to retest it against oracledb version 2.0.1. The issue seems to be the same. I would really appreciate any help, since we're currently upgrading Airflow version and it means we have to manually downgrade oracledb version because of this issue. Thank you very much for your help.

anthony-tuininga commented 4 months ago

I just tried your scenario and it worked for me without any errors. I am using Linux but in thin mode that should make no difference. I did notice some things that aren't common -- but do work nonetheless!

Use of a list of dictionaries for executemany() is not common. This approach is more common:

        data.append(['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12',
                     '12) Test TEXT', 'TEST Text 2', 'toto.domain.com',
                     'Test text - text', '12155FD4544UU', '77445522',
                     "['OQK784AAfw']", 'tested', 'email.email@email.com',
                     '', '', '2022-02-02 12:47:44', '1231564.784521'])

In any case, you can try that to see if you notice anything different. I am not, however, able to replicate what you are seeing.

pavelbrziak commented 3 months ago

Thank you very much for your reply.

I’m aware that using dictionary isn’t standard and I used it only to show better how the columns match the data.

I tried to remove the constraints on the table to figure out how are data really bound. Thus, the new DDL is :

CREATE TABLE TABLE_1
(  
COL_1 NUMBER(38,0), 
COL_2 VARCHAR2(100 CHAR), 
COL_3 VARCHAR2(500 CHAR), 
COL_4 NUMBER(38,0),
COL_5 VARCHAR2(500 CHAR), 
COL_6 VARCHAR2(200 CHAR), 
COL_7 VARCHAR2(100 CHAR), 
COL_8 VARCHAR2(100 CHAR), 
COL_9 VARCHAR2(50 CHAR), 
COL_11 VARCHAR2(100 CHAR), 
COL_10 VARCHAR2(50 CHAR), 
COL_12 VARCHAR2(32 CHAR), 
COL_13 VARCHAR2(255 CHAR), 
COL_14 VARCHAR2(2000 CHAR), 
COL_15 VARCHAR2(500 CHAR),
COL_16 TIMESTAMP (6), 
COL_17 NUMBER
);

I also changed the data to contain the list instead of the dictionary and after some some tweeking, I came up with a clearer test which better shows, what seems to be happening. The new code:

import oracledb

NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'

EMPTY_STRING_DATA = ['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12',
                     '12) Test TEXT', 'TEST Text 2', 'toto.domain.com',
                     'Test text - text', '12155FD4544UU', '77445522',
                     "['OQK784AAfw']", 'tested', 'email.email@email.com',
                     '', '', '2022-02-02 12:47:44', '1231564.784521']

NONE_DATA = ['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12',
             '12) Test TEXT', 'TEST Text 2', 'toto.domain.com',
             'Test text - text', '12155FD4544UU', '77445522',
             "['OQK784AAfw']", 'tested', 'email.email@email.com',
             None, None, '2022-02-02 12:47:44', '1231564.784521']

def insert_select_and_truncate(ora_conn: oracledb.Connection, data):
    with (ora_conn.cursor() as cursor):
        cursor.execute(f"alter session set NLS_TIMESTAMP_FORMAT = '{NLS_TIMESTAMP_FORMAT}'")
        sql = "insert into TABLE_1(COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_9,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15,COL_16,COL_17) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17)"
        print(f'Data process: {data}')
        cursor.executemany(sql, data, batcherrors=True)
        for error in cursor.getbatcherrors():
            print(error.message)
        ora_conn.commit()

        print('Inserted data:')
        for r in cursor.execute('select COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_9,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15,COL_16,COL_17 from TABLE_1'):
            print(r)

        cursor.execute('TRUNCATE TABLE TABLE_1')

dsn = """"""

connection = oracledb.connect(user=, password=, dsn=dsn)

insert_select_and_truncate(connection, [EMPTY_STRING_DATA])
insert_select_and_truncate(connection, [NONE_DATA])
insert_select_and_truncate(connection, [EMPTY_STRING_DATA, NONE_DATA])

Because the constraint is not present anymore, I’m able to insert all the combinations above and also query back the inserted data.

Result:

platform.platform: Windows-10-10.0.19045-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.11.3
oracledb.__version__: 1.3.1

Data process: 
[['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12', '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', '', '', '2022-02-02 12:47:44', '1231564.784521']]

Inserted data:
(1002, '6546546sdf56454', 'TPS2_8sdf_212', 12, '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', '2022-02-02 12:47:44', '1231564.784521', None, None)

-------------------------------

Data process:
[['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12', '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', None, None, '2022-02-02 12:47:44', '1231564.784521']]

Inserted data:
(1002, '6546546sdf56454', 'TPS2_8sdf_212', 12, '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', None, None, datetime.datetime(2022, 2, 2, 12, 47, 44), 1231564.784521)

-------------------------------

Data process:
[['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12', '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', '', '', '2022-02-02 12:47:44', '1231564.784521'], 
['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12', '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', None, None, '2022-02-02 12:47:44', '1231564.784521']]

Inserted data:
(1002, '6546546sdf56454', 'TPS2_8sdf_212', 12, '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', '2022-02-02 12:47:44', '1231564.784521', None, None)
(1002, '6546546sdf56454', 'TPS2_8sdf_212', 12, '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', '2022-02-02 12:47:44', '1231564.784521', None, None)

To summarize the test - i used the same data, once with empty strings and once with None datatype instead. It can be clearly seen in the last two fields.

1) I'm inserting empty strings and the data are not bound properly - last two fields are bound to previous columns.

2) I'm inserting None instead of empty strings and the data seems to be bound properly.

3) I'm combining both and the result is even weirder - the data which were properly inserted in the second test are now bound incorrectly.

I was able to replicate this issue also on version oracledb 2.1.0 - the behaviour seems to be the same.

Please let me know if you're able to replicate the issue - I have workaround to change all the empty string to None, but from my point of view the behaviour seems quite inconsitent.

I'm also gonna change the name of this issue to better match the new findings.

cjbj commented 2 months ago

@pavelbrziak is this still a problem for you? It seems to have dropped off our radar.

pavelbrziak commented 2 months ago

@cjbj I would say so. I believe I was able to present above there seems to be an issue with inserting of empty strings. It seems to be quite easily replicable and if my assumptions are correct, this might affect any insert. Certainly, I might have made a mistake with my test case and it could be explained other way, but I would still really appreciate some investigation and comments. Thank you.

anthony-tuininga commented 2 months ago

I just tried your code with version 2.1.1 and I see the null values being inserted correctly. My output looks as follows:

2024-04-10 14:21:55.676 Sending packet [op 9] on socket 3
0000 : 00 00 03 0D 06 00 00 00 |........|
0008 : 00 00 11 69 04 00 01 01 |...i....|
0016 : 01 01 01 03 5E 05 00 04 |....^...|
0024 : 00 08 80 29 00 01 01 D6 |...)....|
0032 : 01 01 0D 00 00 00 01 01 |........|
0040 : 04 7F FF FF FF 01 01 11 |........|
0048 : 00 00 00 00 00 00 00 00 |........|
0056 : 00 01 00 00 00 00 00 00 |........|
0064 : 00 00 00 00 00 00 00 00 |........|
0072 : 00 D6 69 6E 73 65 72 74 |..insert|
0080 : 20 69 6E 74 6F 20 54 41 |.into.TA|
0088 : 42 4C 45 5F 31 28 43 4F |BLE_1(CO|
0096 : 4C 5F 31 2C 43 4F 4C 5F |L_1,COL_|
0104 : 32 2C 43 4F 4C 5F 33 2C |2,COL_3,|
0112 : 43 4F 4C 5F 34 2C 43 4F |COL_4,CO|
0120 : 4C 5F 35 2C 43 4F 4C 5F |L_5,COL_|
0128 : 36 2C 43 4F 4C 5F 37 2C |6,COL_7,|
0136 : 43 4F 4C 5F 38 2C 43 4F |COL_8,CO|
0144 : 4C 5F 39 2C 43 4F 4C 5F |L_9,COL_|
0152 : 31 30 2C 43 4F 4C 5F 31 |10,COL_1|
0160 : 31 2C 43 4F 4C 5F 31 32 |1,COL_12|
0168 : 2C 43 4F 4C 5F 31 33 2C |,COL_13,|
0176 : 43 4F 4C 5F 31 34 2C 43 |COL_14,C|
0184 : 4F 4C 5F 31 35 2C 43 4F |OL_15,CO|
0192 : 4C 5F 31 36 2C 43 4F 4C |L_16,COL|
0200 : 5F 31 37 29 20 56 41 4C |_17).VAL|
0208 : 55 45 53 20 28 3A 31 2C |UES.(:1,|
0216 : 20 3A 32 2C 20 3A 33 2C |.:2,.:3,|
0224 : 20 3A 34 2C 20 3A 35 2C |.:4,.:5,|
0232 : 20 3A 36 2C 20 3A 37 2C |.:6,.:7,|
0240 : 20 3A 38 2C 20 3A 39 2C |.:8,.:9,|
0248 : 20 3A 31 30 2C 20 3A 31 |.:10,.:1|
0256 : 31 2C 20 3A 31 32 2C 20 |1,.:12,.|
0264 : 3A 31 33 2C 20 3A 31 34 |:13,.:14|
0272 : 2C 20 3A 31 35 2C 20 3A |,.:15,.:|
0280 : 31 36 2C 20 3A 31 37 29 |16,.:17)|
0288 : 01 01 01 01 00 00 00 00 |........|
0296 : 00 00 00 02 80 00 00 00 |........|
0304 : 00 01 01 00 00 01 10 00 |........|
0312 : 00 00 00 02 03 69 01 00 |.....i..|
0320 : 00 01 01 00 00 01 3C 00 |......<.|
0328 : 00 00 00 02 03 69 01 00 |.....i..|
0336 : 00 01 01 00 00 01 34 00 |......4.|
0344 : 00 00 00 02 03 69 01 00 |.....i..|
0352 : 00 01 01 00 00 01 08 00 |........|
0360 : 00 00 00 02 03 69 01 00 |.....i..|
0368 : 00 01 01 00 00 01 34 00 |......4.|
0376 : 00 00 00 02 03 69 01 00 |.....i..|
0384 : 00 01 01 00 00 01 2C 00 |......,.|
0392 : 00 00 00 02 03 69 01 00 |.....i..|
0400 : 00 01 01 00 00 01 3C 00 |......<.|
0408 : 00 00 00 02 03 69 01 00 |.....i..|
0416 : 00 01 01 00 00 01 40 00 |......@.|
0424 : 00 00 00 02 03 69 01 00 |.....i..|
0432 : 00 01 01 00 00 01 34 00 |......4.|
0440 : 00 00 00 02 03 69 01 00 |.....i..|
0448 : 00 01 01 00 00 01 20 00 |........|
0456 : 00 00 00 02 03 69 01 00 |.....i..|
0464 : 00 01 01 00 00 01 38 00 |......8.|
0472 : 00 00 00 02 03 69 01 00 |.....i..|
0480 : 00 01 01 00 00 01 18 00 |........|
0488 : 00 00 00 02 03 69 01 00 |.....i..|
0496 : 00 01 01 00 00 01 54 00 |......T.|
0504 : 00 00 00 02 03 69 01 00 |.....i..|
0512 : 00 01 01 00 00 02 3E 80 |......>.|
0520 : 00 00 00 00 02 03 69 01 |......i.|
0528 : 00 00 01 01 00 00 02 3E |.......>|
0536 : 80 00 00 00 00 02 03 69 |.......i|
0544 : 01 00 00 01 01 00 00 01 |........|
0552 : 4C 00 00 00 00 02 03 69 |L......i|
0560 : 01 00 00 01 01 00 00 01 |........|
0568 : 38 00 00 00 00 02 03 69 |8......i|
0576 : 01 00 00 07 04 31 30 30 |.....100|
0584 : 32 0F 36 35 34 36 35 34 |2.654654|
0592 : 36 73 64 66 35 36 34 35 |6sdf5645|
0600 : 34 0D 54 50 53 32 5F 38 |4.TPS2_8|
0608 : 73 64 66 5F 32 31 32 02 |sdf_212.|
0616 : 31 32 0D 31 32 29 20 54 |12.12).T|
0624 : 65 73 74 20 54 45 58 54 |est.TEXT|
0632 : 0B 54 45 53 54 20 54 65 |.TEST.Te|
0640 : 78 74 20 32 0F 74 6F 74 |xt.2.tot|
0648 : 6F 2E 64 6F 6D 61 69 6E |o.domain|
0656 : 2E 63 6F 6D 10 54 65 73 |.com.Tes|
0664 : 74 20 74 65 78 74 20 2D |t.text.-|
0672 : 20 74 65 78 74 0D 31 32 |.text.12|
0680 : 31 35 35 46 44 34 35 34 |155FD454|
0688 : 34 55 55 08 37 37 34 34 |4UU.7744|
0696 : 35 35 32 32 0E 5B 27 4F |5522.['O|
0704 : 51 4B 37 38 34 41 41 66 |QK784AAf|
0712 : 77 27 5D 06 74 65 73 74 |w'].test|
0720 : 65 64 15 65 6D 61 69 6C |ed.email|
0728 : 2E 65 6D 61 69 6C 40 65 |.email@e|
0736 : 6D 61 69 6C 2E 63 6F 6D |mail.com|
0744 : 00 00 13 32 30 32 32 2D |...2022-|
0752 : 30 32 2D 30 32 20 31 32 |02-02.12|
0760 : 3A 34 37 3A 34 34 0E 31 |:47:44.1|
0768 : 32 33 31 35 36 34 2E 37 |231564.7|
0776 : 38 34 35 32 31          |84521   |

Note the zero length bytes at offsets 744 and 745, just before the final date and number. These are the null values. I see from your output that the null values seem to have been inserted at the very end instead of 3rd and 4th last as in your code. Can you supply your debug output as well? Can you also try with version 2.1.1?

My actual output is as follows, which I believe is expected:

Data process: [['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12', '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', '', '', '2022-02-02 12:47:44', '1231564.784521']]
Inserted data:
(1002, '6546546sdf56454', 'TPS2_8sdf_212', 12, '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', None, None, datetime.datetime(2022, 2, 2, 12, 47, 44), 1231564.784521)
Data process: [['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12', '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', None, None, '2022-02-02 12:47:44', '1231564.784521']]
Inserted data:
(1002, '6546546sdf56454', 'TPS2_8sdf_212', 12, '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', None, None, datetime.datetime(2022, 2, 2, 12, 47, 44), 1231564.784521)
Data process: [['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12', '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', '', '', '2022-02-02 12:47:44', '1231564.784521'], ['1002', '6546546sdf56454', 'TPS2_8sdf_212', '12', '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', None, None, '2022-02-02 12:47:44', '1231564.784521']]
Inserted data:
(1002, '6546546sdf56454', 'TPS2_8sdf_212', 12, '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', None, None, datetime.datetime(2022, 2, 2, 12, 47, 44), 1231564.784521)
(1002, '6546546sdf56454', 'TPS2_8sdf_212', 12, '12) Test TEXT', 'TEST Text 2', 'toto.domain.com', 'Test text - text', '12155FD4544UU', '77445522', "['OQK784AAfw']", 'tested', 'email.email@email.com', None, None, datetime.datetime(2022, 2, 2, 12, 47, 44), 1231564.784521)

It would be useful to also print out the actual database version:

print(connection.version)

Is the database running on Windows as well?

anthony-tuininga commented 1 month ago

Closing - no activity