Handling Date Data

Format an INGRESDATE for display.

Everything in Python is an object, but everything in an Actian SQL DBMS is a hardware datatype or a C structure. ctypes automatically takes care of marshalling the hardware datatypes and C strings to and from Python, but the other SQL datatypes need to be accommodated as ctypes structures, which have to be defined, or as byte arrays. (See Data Marshalling for more information.)

In general, all the Actian SQL abstract data types (dates/times, money, decimal/numeric, etc.) are represented in the database as C structures. The OpenAPI exchanges those structures with the application. In the case of dates they can be treated as byte arrays. It is not necessary to understand the internal structure in order to allocate a buffer.

Tip

The required buffer size can be determined in two ways. The OpenAPI returns query metadata in the form of a list of IIAPI_DESCRIPTOR. The ds_length attribute indicates the size of bufffer required. Alternatively, see Ingres Data Types in the OpenAPI User Guide.

Byte arrays (structures) are incomprehensible to a human user. All OpenAPI applications have to explicitly format abstract types such as dates in a human-readable form. The OpenAPI IIapi_convertData() and IIapi_formatData() functions are provided for the purpose.

This example illustrates how to format an INGRESDATE for display. The same general approach is used for ANSIDATE, TIME, TIMESTAMP, and INTERVAL.

INGRESDATE values are represented in a 12 byte structure. The example below allocates a 12 byte buffer using ctypes.c_buffer.

Note

This example formats the byte array representation of a date as a human-readable string. The same function can be used for the inverse operation, to load a human-readable source date into a destination buffer.

Download

 1import pyngres as py
 2import ctypes
 3
 4def readable(ingresdate):
 5    '''return a human-readable representation of an INGRESDATE'''
 6
 7    # an INGRESDATE requires up to 26 characters to display
 8    readable_date = ctypes.c_buffer(26)
 9
10    # allocate the conversion control block
11    cvp = py.IIAPI_CONVERTPARM()
12    # describe the source 
13    cvp.cv_srcDesc.ds_dataType = py.IIAPI_DTE_TYPE
14    cvp.cv_srcDesc.ds_nullable = False 
15    cvp.cv_srcDesc.ds_length = 12
16    cvp.cv_srcDesc.ds_columnType = py.IIAPI_COL_QPARM
17    cvp.cv_srcValue.dv_length = 12
18    cvp.cv_srcValue.dv_value = ctypes.addressof(ingresdate)
19    # describe the destination
20    cvp.cv_dstDesc.ds_dataType = py.IIAPI_CHA_TYPE
21    cvp.cv_dstDesc.ds_length = ctypes.sizeof(readable_date)
22    cvp.cv_dstDesc.ds_columnType = py.IIAPI_COL_QPARM
23    cvp.cv_dstValue.dv_length = ctypes.sizeof(readable_date)
24    cvp.cv_dstValue.dv_value = ctypes.addressof(readable_date)
25
26    # convert the INGRESDATE structure to a human-readable byte-array
27    py.IIapi_convertData(cvp)
28    # decode the byte array into a Python str()
29    return readable_date.value.decode()
30
31# the OpenAPI conversion functions won't work unless the OpenAPI is initialized
32inp = py.IIAPI_INITPARM()
33inp.in_timeout = -1
34inp.in_version = py.IIAPI_VERSION
35py.IIapi_initialize(inp)
36...
37# an INGRESDATE structure needs a 12 byte buffer
38dob = ctypes.c_buffer(12)
39# fetch date-of-birth (dob) from the DBMS
40...
41# convert the binary INGRESDATE and display it 
42readable_dob = readable(dob)
43print(readable_dob)

Download