Converting python date to Firebird date

English
,
#1

Hello,

I am copying data from oracle to firebird embedded using python. The problem that i have is that the date values extracted from Oracle are of type python datetime. Once I get them from python, how do i convert those values to a type which is usable for the embedded database? As a work around I have my date as an integer YYYYMMDD. I would like to keep the native format of DATE if possible. Thank you.

#4 
    sDateNow = datetime.datetime.now()
    sDateTable = sDateNow.strftime( "%Y-%m-%d")
    sText = [sDateTable, j, sMailTo, SPATH + STOPDF, sBijlage, sFactuurOmschrijving, sTemp, "False"]
    sText = "'" + "','".join(sText) + "'"
    sSQL = 'insert into "tblF-mail-log" ("datum","mailgb","E-mail","pad","bijlage","subject","body","done") ' + \
                '	values (' + sText + ') '
#2

An ISO date string should work. yyyy-mm-dd

#3

I am sorry that did not seem to work. I checked the type returned from the Oracle database. The print out returned <class ‘ooo_script_framework.com.sun.star.util.Date’>. This did not accept the string type YYYY-MM-DD format. Still investigating

#5

Of course “it works”: 3.4  Data Types for Dates and Times

#6

Hello,

Here is the structure of the embedded FireBird table:

CREATE TABLE TBL_TSTDATE
(
ID SMALLINT generated by default as identity primary key,
DA_DATE DATE
)

Here is the code i have used to test:

from __future__ import unicode_literals
import cx_Oracle
import uno
from datetime import datetime
from scriptforge import CreateScriptService

def getData():
	__openDB()
	bas = CreateScriptService("Basic")
	stmt = fConnection.createStatement() 
	stmt.execute('delete from TBL_TSTDATE')	
	fConnection.commit()
	stmt.close()
	stmt=None
	selectSysDateEpat ="select trunc(sysdate) DA_DATE from dual"
	epatcursor.execute(selectSysDateEpat)
	result= epatcursor.fetchone()
	insSQL = "INSERT INTO TBL_TSTDATE (DA_DATE) VALUES (?)"
	stmt = fConnection.prepareStatement(insSQL)
	for DA_DATE in result:
		print(DA_DATE)
		print(type(DA_DATE))
		stmt.setString(1, DA_DATE)
		stmt.executeUpdate()				
		stmt.clearParameters()

def __openDB():
	global fConnection
	global epat
	global epatcursor
	XSCRIPTCONTEXT.getDocument().CurrentController.connect()
	oDoc = XSCRIPTCONTEXT.getDocument()
	db = oDoc.DataSource
	fConnection = db.getConnection("","") 
	epat = cx_Oracle.connect(user='????',password='????',dsn='????')
	epatcursor = epat.cursor()

def __closeDb():
	epatcursor.close()
	epat.close()
	fConnection.close()
	epatcursor= None
	epat=None
	fConnection=None

The result of this code is the following printout:

>>> 2022-04-30 00:00:00
<class 'datetime.datetime'>

With the following popup error messge:

Couldn't convert 2022-04-30 00:00:00 to a UNO type; caught exception: <class 'AttributeError'>: 'datetime.datetime' object has no attribute 'getTypes', traceback follows
no traceback available
 (Error during invoking function getData in module vnd.sun.star.tdoc:/725167500/Scripts/python/LibraryOpwpVEppr/LibraryTblTstDate/ModuleTblTstDate.py (<class 'uno.com.sun.star.uno.RuntimeException'>: Couldn't convert 2022-04-30 00:00:00 to a UNO type; caught exception: <class 'AttributeError'>: 'datetime.datetime' object has no attribute 'getTypes', traceback follows
no traceback available

I then made modification to the code. The code in full with modifications is:

from __future__ import unicode_literals
import cx_Oracle
import uno
from datetime import datetime
from scriptforge import CreateScriptService

def getData():
	__openDB()
	bas = CreateScriptService("Basic")
	stmt = fConnection.createStatement() 
	stmt.execute('delete from TBL_TSTDATE')	
	fConnection.commit()
	stmt.close()
	stmt=None
	selectSysDateEpat ="select trunc(sysdate) DA_DATE from dual"
	epatcursor.execute(selectSysDateEpat)
	result= epatcursor.fetchone()
	insSQL = "INSERT INTO TBL_TSTDATE (DA_DATE) VALUES (?)"
	stmt = fConnection.prepareStatement(insSQL)
	for DA_DATE in result:
		sDateTable =DA_DATE.strftime("%Y-%m-%d")
		print(sDateTable)
		print(type(sDateTable))
		stmt.setDate(1, sDateTable)
		stmt.executeUpdate()				
		stmt.clearParameters()

def __openDB():
	global fConnection
	global epat
	global epatcursor
	XSCRIPTCONTEXT.getDocument().CurrentController.connect()
	oDoc = XSCRIPTCONTEXT.getDocument()
	db = oDoc.DataSource
	fConnection = db.getConnection("","") 
	epat = cx_Oracle.connect(user='????',password='????',dsn='????')
	epatcursor = epat.cursor()

def __closeDb():
	epatcursor.close()
	epat.close()
	fConnection.close()
	epatcursor= None
	epat=None
	fConnection=None

The result of this code is the following printout:

>>> 2022-04-30
<class 'str'>

With the following popup error message:

value is not of same or derived type! (Error during invoking function getData in module vnd.sun.star.tdoc:/725167500/Scripts/python/LibraryOpwpVEppr/LibraryTblTstDate/ModuleTblTstDate.py (<class 'ooo_script_framework.com.sun.star.script.CannotConvertException'>: value is not of same or derived type!
  File "D:\Program Files\LibreOffice\App\libreoffice\program\pythonscript.py", line 915, in invoke
    ret = self.func( *args )
  File "vnd.sun.star.tdoc:/725167500/Scripts/python/LibraryOpwpVEppr/LibraryTblTstDate/ModuleTblTstDate.py", line 25, in getData
))

I modified the insert SQL to be insSQL = "INSERT INTO TBL_TSTDATE (DA_DATE) VALUES (cast (? as date))"
with the set value to be both stmt.setDate(1, sDateTable) as well as stmt.setString(1, sDateTable)

and nothing worked.

Thank you for your suggestions so far.

#7

Why all that overcomplicated stuff? Is it really worth it?
UNO method setDate takes an UNO struct com.sun.star.util.Date
If you want to set a string, then use method .setString
Still not using MRI? Why not?

#8

Thank you for the suggestion. Here is what I did for setString:

from __future__ import unicode_literals
import cx_Oracle
import uno
from datetime import datetime
from scriptforge import CreateScriptService

def getData():
	__openDB()
	bas = CreateScriptService("Basic")
	stmt = fConnection.createStatement() 
	stmt.execute('delete from TBL_TSTDATE')	
	fConnection.commit()
	stmt.close()
	stmt=None
	selectSysDateEpat ="select trunc(sysdate) DA_DATE from dual"
	epatcursor.execute(selectSysDateEpat)
	result= epatcursor.fetchone()
	insSQL = "INSERT INTO TBL_TSTDATE (DA_DATE) VALUES (?)"
	stmt = fConnection.prepareStatement(insSQL)
	for DA_DATE in result:
		sDateTable =DA_DATE.strftime("%Y-%m-%d")
		print(sDateTable)
		print(type(sDateTable))
		stmt.setString(1, sDateTable)
		stmt.executeUpdate()				
		stmt.clearParameters()

def __openDB():
	global fConnection
	global epat
	global epatcursor
	XSCRIPTCONTEXT.getDocument().CurrentController.connect()
	oDoc = XSCRIPTCONTEXT.getDocument()
	db = oDoc.DataSource
	fConnection = db.getConnection("","") 
	epat = cx_Oracle.connect(user='????',password='????',dsn='????')
	epatcursor = epat.cursor()

def __closeDb():
	epatcursor.close()
	epat.close()
	fConnection.close()
	epatcursor= None
	epat=None
	fConnection=None

The resulting printout is:

>>> 2022-04-30
<class 'str'>

The resulting popup error message is:

Incorrect type for setString (Error during invoking function getData in module vnd.sun.star.tdoc:/725167500/Scripts/python/LibraryOpwpVEppr/LibraryTblTstDate/ModuleTblTstDate.py (<class 'ooo_script_framework.com.sun.star.sdbc.SQLException'>: Incorrect type for setString
  File "D:\Program Files\LibreOffice\App\libreoffice\program\pythonscript.py", line 915, in invoke
    ret = self.func( *args )
  File "vnd.sun.star.tdoc:/725167500/Scripts/python/LibraryOpwpVEppr/LibraryTblTstDate/ModuleTblTstDate.py", line 25, in getData
))
#9

Thank you for the suggestion to use MRI. I will install it whenever I can get temporary permission to go beyond the corporate firewall.

#10

I’m not sure if parameter substitution ever worked like this with oStatement.setString(index, sTxt) but I avoid macros like pestilence. Can you make it work with some VARCHAR field? I won’t waste any more time with testing weird UNO interfaces.
Generating an UNO date:

>>> import uno
>>> import time
>>> help(uno)
>>> d = time.localtime()
>>> dUNO = uno.createUnoStruct('com.sun.star.util.Date')
>>> dUNO.Year = d[0]
>>> dUNO.Month = d[1]
>>> dUNO.Day = d[2]
>>> dUNO
(com.sun.star.util.Date){ Day = (unsigned short)0x1e, Month = (unsigned short)0x4, Year = (short)0x7e6 }
1 Like
#11

Hello,

I would really like to thank you for your hint that you gave. I am learning a lot from all of the good people here. With your code sample, I was able to convert the python date format to UNO com.sun.star.util.Date format. This worked completely and I was able to save the data in the native firebird date format!

Here is the resulting code, thanks to you:

from __future__ import unicode_literals
import cx_Oracle
import uno
from datetime import datetime

def getData():
	__openDB()
	stmt = fConnection.createStatement() 
	stmt.execute('delete from TBL_TSTDATE')	
	fConnection.commit()
	stmt.close()
	stmt=None
	selectSysDateEpat ="select trunc(sysdate) DA_DATE from dual"
	epatcursor.execute(selectSysDateEpat)
	result= epatcursor.fetchone()
	insSQL = "INSERT INTO TBL_TSTDATE (DA_DATE) VALUES (cast (? as date))"
	stmt = fConnection.prepareStatement(insSQL)
	for DA_DATE in result:
		dUNO = uno.createUnoStruct('com.sun.star.util.Date')
		dUNO.Year = int(DA_DATE.strftime("%Y"))
		dUNO.Month= int(DA_DATE.strftime("%m"))
		dUNO.Day= int(DA_DATE.strftime("%d"))
		stmt.setDate(1, dUNO)
		stmt.executeUpdate()				
		stmt.clearParameters()
def __openDB():
	global fConnection
	global epat
	global epatcursor
	XSCRIPTCONTEXT.getDocument().CurrentController.connect()
	oDoc = XSCRIPTCONTEXT.getDocument()
	db = oDoc.DataSource
	fConnection = db.getConnection("","") 
	epat = cx_Oracle.connect(user='????',password='????',dsn='????')
	epatcursor = epat.cursor()

def __closeDb():
	epatcursor.close()
	epat.close()
	fConnection.close()
	epatcursor= None
	epat=None
	fConnection=None

Thank you once again.

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