Python UNO crashes when moving data from cx_Oracle to calc

English
, , ,
#1

Hello,
I came across this error when dumping data from Oracle using a python macro:

Here is the code:

def checkDB():
	context()
	connection = cx_Oracle.connect(user='????',password='?????',dsn='?????')

	cursor = connection.cursor()

	sql = wb.Sheets['sql'].getCellRangeByName("a1").String

	result= cursor.execute(sql).fetchall()
	active_sheet.getCellRangeByName('A1:BK1048576').clearContents(7)
	oRangeSource = active_sheet.getCellRangeByName('A1:BK'+str(cursor.rowcount)).setDataArray(result)

	return None

And there is the error:

    com.sun.star.uno.RuntimeException: [mscx_uno bridge error] UNO type of C++ exception unknown: "std.bad_alloc", RTTI-name=".?AVbad_alloc@std@@"! (Error during invoking function checkDB in module file:///D:/Program%20Files/LibreOffice/Data/settings/user/Scripts/python/Hello.py (<class 'uno.com.sun.star.uno.RuntimeException'>: [mscx_uno bridge error] UNO type of C++ exception unknown: "std.bad_alloc", RTTI-name=".?AVbad_alloc@std@@"!
      File "D:\Program Files\LibreOffice\App\libreoffice\program\pythonscript.py", line 915, in invoke
        ret = self.func( *args )
      File "D:\Program Files\LibreOffice\Data\settings\user\Scripts\python\Hello.py", line 111, in checkDB
        oRangeSource = active_sheet.getCellRangeByName('A1:BK'+str(cursor.rowcount)).setDataArray(result)
    ))

edit by @karolus : fenced Code and Error-msg with backtics``````

The sql was very complex. The first error I got was that it could not process the Oracle date timestamp properly. I converted that to varchar using to_char. Now i am stuck at the above error. This does not seem to handle large number of rows very well. Is there is a workaround? Thank you.

#2

Hallo

  1. is ’result’ not empty?
  2. does the size of result fit exactly into the size of output-range ?
#3

The answer to both questions is yes.

#4

which answer gives print(type(result))

#5

<class ‘list’>

#6

that looks ok…now:
print(len(result) == cursor.rowcount )
print(min(len(row) for row in result))
print(max(len(row) for row in result))

#7

Hello, I modified your suggestion to make it look like this:

wb.Sheets['sql'].getCellRangeByName('a4').String=len(result)
wb.Sheets['sql'].getCellRangeByName('a5').String=cursor.rowcount
wb.Sheets['sql'].getCellRangeByName('a6').String==(min(len(row) for row in result))
wb.Sheets['sql'].getCellRangeByName('a7').String=(max(len(row) for row in result))

The result was::

A
4 152188
5 152188
6
7 63
#8

I have made some more changes:

def checkDB():
	context()
	connection = cx_Oracle.connect(user='???',password='???',dsn='???')
	cursor = connection.cursor()
	sql = wb.Sheets['sql'].getCellRangeByName("a1").String
	result= cursor.execute(sql).fetchall()
	lresult= len(result)
	daRange = f"A1:BK{lresult}"
	active_sheet.getCellRangeByName('A1:BK1048576').clearContents(7)
	wb.Sheets['sql'].getCellRangeByName("a4").String=daRange
	wb.Sheets['sql'].getCellRangeByName("a5").String=cursor.rowcount
	oRangeSource = active_sheet.getCellRangeByName(daRange).setDataArray(result)
	return None

The resulting error is still the same:

com.sun.star.uno.RuntimeException: [mscx_uno bridge error] UNO type of C++ exception unknown: "std.bad_alloc", RTTI-name=".?AVbad_alloc@std@@"! (Error during invoking function checkDB in module file:///D:/Program%20Files/LibreOffice/Data/settings/user/Scripts/python/Hello.py (<class 'uno.com.sun.star.uno.RuntimeException'>: [mscx_uno bridge error] UNO type of C++ exception unknown: "std.bad_alloc", RTTI-name=".?AVbad_alloc@std@@"!
  File "D:\Program Files\LibreOffice\App\libreoffice\program\pythonscript.py", line 915, in invoke
    ret = self.func( *args )
  File "D:\Program Files\LibreOffice\Data\settings\user\Scripts\python\Hello.py", line 120, in checkDB
    oRangeSource = active_sheet.getCellRangeByName(daRange).setDataArray(result)
))

This section of the error message looks somewhat worrisome:

 [mscx_uno bridge error] UNO type of C++ exception unknown: "std.bad_alloc", RTTI-name=".?AVbad_alloc@std@@"!

Does this indicate a problem with oracle python driver?

#9

yes|no: the message clearly points to the sourceline where it try to …setDataArray(result)

your result has a overall count of datasets 152188 which fits exactly into row 1 to 152188,
the output from sql.A7 indicates that it fits also with 63 into a width of Column A to Column BK.

BUT sql.A6 indicates that at least one of your Datasets from result has zero lenghts, and thats wrong for …setDataArray( … )

we should ping DataBase-Experts like @Villeroy , @Ratslinger

#12

Spreadsheet cells can be linked to record sets without a single line of code.
[Tutorial] Using registered datasources in Calc

1 Like
#10

give it a shot:

def checkDB():
    context()
    connection = cx_Oracle.connect(user='???',password='???',dsn='???')
    cursor = connection.cursor()
    sql = wb.Sheets['sql']["a1"].String
    result= cursor.execute(sql).fetchall()
    result = [ dataset for dataset in result if dataset ] ###←←←###
    len_result= len(result)
    daRange = f"A1:BK{len_result}"
    active_sheet["A1:BK1048576"].clearContents(7)
    wb.Sheets['sql']["a4"].String=daRange
    wb.Sheets['sql']["a5"].Value=cursor.rowcount
    wb.Sheets['sql']["a6"].Value=len_result
    oRangeSource = active_sheet[daRange].setDataArray(result)

[/quote]

1 Like
#11

Hello,
Here is the code:

def checkDB():
	context()
	connection = cx_Oracle.connect(user='????',password='????',dsn='????')
	cursor = connection.cursor()
	sql = wb.Sheets['sql'].getCellRangeByName("a1").String
	result= cursor.execute(sql).fetchall()
	result = [ dataset for dataset in result if dataset ]
	len_result= len(result)
	daRange = f"A1:BK{len_result}"
	active_sheet.getCellRangeByName('A1:BK1048576').clearContents(7)
	wb.Sheets['sql'].getCellRangeByName("a4").String=daRange
	wb.Sheets['sql'].getCellRangeByName("a5").String=cursor.rowcount
	wb.Sheets['sql'].getCellRangeByName("a6").String=len_result
	oRangeSource = active_sheet.getCellRangeByName(daRange).setDataArray(result)
	return None

Here is the error:

com.sun.star.uno.RuntimeException: [mscx_uno bridge error] UNO type of C++ exception unknown: "std.bad_alloc", RTTI-name=".?AVbad_alloc@std@@"! (Error during invoking function checkDB in module file:///D:/Program%20Files/LibreOffice/Data/settings/user/Scripts/python/Hello.py (<class 'uno.com.sun.star.uno.RuntimeException'>: [mscx_uno bridge error] UNO type of C++ exception unknown: "std.bad_alloc", RTTI-name=".?AVbad_alloc@std@@"!
  File "D:\Program Files\LibreOffice\App\libreoffice\program\pythonscript.py", line 915, in invoke
    ret = self.func( *args )
  File "D:\Program Files\LibreOffice\Data\settings\user\Scripts\python\Hello.py", line 116, in checkDB
    oRangeSource = active_sheet.getCellRangeByName(daRange).setDataArray(result)
))

Here is the result in the second sheet:

A
4| A1:BK152188
5| 152188
6|152188

#13

Hello,

Had an Oracle XE version at one time for testing but dumped it as it was slow (Base to oracle database connection slow)

Decided it might be useful to use PostgreSQL. Unfortunately had no Python driver installed and wanted to be as close as possible to your problem. Finally got one installed and had some further difficulty. Consistently getting similar errors to yours (slight difference because on Ubuntu 20.x Mate). Then looked at this → setDataArray().

Once I insured the column AND row numbers were exact, all worked. Too little or too many of either caused the error.

Code used:

import psycopg


def checkDB(*arg):
    connection = psycopg.connect("dbname='???' user='???' password='???'")
    cursor = connection.cursor()
    sql = 'Select "ID", forename, street From customer'
    result= cursor.execute(sql).fetchall()
    active_sheet = XSCRIPTCONTEXT.getDocument().CurrentController.ActiveSheet
    result = [ dataset for dataset in result if dataset ]
    len_result= len(result)
    daRange = f"A1:C{len_result}"
    active_sheet.getCellRangeByName('A1:BK1048576').clearContents(7)
    oRangeSource =     active_sheet.getCellRangeByName(daRange).setDataArray(result)
    active_sheet.getCellRangeByName("a4").String=cursor.rowcount

producing (lower portion is DB data):

Screenshot at 2021-12-14 17-48-01

1 Like
Address labels creation/printing
#14

Hello,

I ran the SQL code for this work in SQL-Developer, and i made a VBA version of it in Excel. The Excel version ran with out any problems. However I noticed that the number of rows returned in SQL-Developer (which uses ojdbcXX.jar) and VBA (which is using OraOledb )are about 142 thousand. As you noticed in the previous postings cx_Oracle is returning about ten thousand more. So this is definitely a driver problem. I tried to modify my SQL to absolutely prevent the return of any empty rows, but to no avail. So this is a driver problem through and through. Thank you for your assistance.

#15

Not certain I agree with that. The link shows:

Each element of the array must contain a double or a string.

Since you have blank rows, this may be the issue. Adjust the SQL.

Edit:

Did test with Empty fields and all worked OK (PostgreSQL driver). The selected fields were strings. When selecting only an Integer field the error returned.
’
Edit 2:
.
For the sake of trying more data, had larger table - 20 columns with 81831 records mostly text fields (one was integer) and resulting Calc file was 4.5 MB. Completed in under a minute.

1 Like
#16

I have made many adjustments to my SQL (all part of work) and I was finally able to get some data into the sheet. Here is the python code:

def checkDB():
	context()
	connection = cx_Oracle.connect(user='???',password='???',dsn='???')
	cursor = connection.cursor()
	sql = wb.Sheets['sql'].getCellRangeByName("a1").String
	result= cursor.execute(sql).fetchall()
	result = [ dataset for dataset in result if dataset ]
	len_result= len(result)
	daRange = f"A1:CF{len_result}"
	active_sheet.getCellRangeByName('A1:CF1048576').clearContents(7)
	wb.Sheets['sql'].getCellRangeByName("a4").String=daRange
	wb.Sheets['sql'].getCellRangeByName("a5").String=cursor.rowcount
	wb.Sheets['sql'].getCellRangeByName("a6").String=len_result
	#r=7
	#for i in range(0, len(cursor.description)):
	#	wb.Sheets['sql'].getCellRangeByName(f"a{r}").String=cursor.description[i][0]
	#	r+=1
	oRangeSource = active_sheet.getCellRangeByName(daRange).setDataArray(result)
	return None

This also ran in under one minute. So I am not sure what to make of this. I will run more scenarios, but I will accept the answer as the problem is the SQL.

#17

Hello,

I just thought I would give you all an update. As stated before, the problem was with the SQL. The specific issue with the SQL is that there are a lot of null data in the table I was using. So I modified the sql to use NVL. The second issue was that the SQL returns over 95 thousand rows with 94 columns. So using fetchall() was never a good idea. I modified my code to use fetchmany(10000) within a loop. Here is the new code:

    def checkDB(tblName):
     	wb = XSCRIPTCONTEXT.getDocument()
     	sht =  wb.Sheets[tblName]
     	connection = cx_Oracle.connect(user='????',password='????',dsn='????')
     	cursor = connection.cursor()
     	sql = wb.Sheets['sql'].getCellRangeByName("a1").String
     	sht.getCellRangeByName('A1:AMJ1048576').clearContents(7)
     	startCol = 0
     	startRow = 0
     	listSize=10000
     	cursor.execute(sql)
     	desc = cursor.description
     	desc=list(zip(*desc))
     	desc=desc[0]
     	lastCol =len(desc)-1
     	desc=[desc]
     	sht.getCellRangeByPosition(startCol,startRow,lastCol,startRow ).setDataArray(desc)
     	startRow=1
     	while True:
     		result= cursor.fetchmany(listSize)
     		if result:
     			lastRow=len(result)+startRow-1
     			sht.getCellRangeByPosition(startCol,startRow,lastCol,lastRow).setDataArray(result)
     			startRow=lastRow+1
     		else:
     			break
     	cursor.close()
     	connection.close()
     	return None

This thankfully succeeds without any kind of error

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.