sword _fetchDataIntoDateArray(ORACLE_SQL_CURSOR *cursor, PA_Variable variable, unsigned int pos)
{
if(cursor->rowsFetched)
{
unsigned int itemCount = cursor->rowsFetched < cursor->itemCount ? cursor->rowsFetched : cursor->itemCount;
//to clear the content of element #0, if any
PA_ResizeArray(&variable, 0);
PA_ResizeArray(&variable, itemCount);
short d;
short m;
short y;
for(unsigned int i = 0; i < itemCount; ++i)
{
PA_YieldAbsolute();
if(cursor->indicatorLists.at(pos).at(i) != -1)
{
OCIDateGetDate(&cursor->arrayOfDates.at(pos).at(i), &y, &m, &d);
PA_SetDateInArray(variable, i + 1, d, m, y);
}else{
setDateArrayValueNull(variable, i + 1);
}
}
PA_SetPointerValue(&cursor->pointers.at(pos), variable);
}
return cursor->rowsFetched;
}
boolean OCI_API OCI_DateGetDate
(
OCI_Date *date,
int *year,
int *month,
int *day
)
{
sb2 yr = 0;
ub1 mt = 0;
ub1 dy = 0;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_DATE, date)
OCI_CHECK_PTR(OCI_IPC_INT, year)
OCI_CHECK_PTR(OCI_IPC_INT, month)
OCI_CHECK_PTR(OCI_IPC_INT, day)
OCIDateGetDate(date->handle, &yr, &mt, &dy);
*year = (int) yr;
*month = (int) mt;
*day = (int) dy;
call_retval = call_status = TRUE;
OCI_LIB_CALL_EXIT()
}
int main(int argc, char* argv[]) {
char err_msg [512];
struct oci_connection conn;
OCIStmt* sh;
int i_out;
double d_out;
char* c_out;
OCIDate dt_out;
int day, month, year, hour, minute, second;
c_out = (char*) malloc(101);
if (argc < 2) {
printf("usage %s username/password[@dbname]\n", argv[0]);
exit (-1);
}
text username[31];
text password[31];
text dbname [31];
parse_connect_string(argv[1],username, password, dbname);
if (! oci_connect(username, password, dbname,&conn, err_msg)) {
printf(err_msg);
goto clean_up;
}
if (! (sh=oci_parse("select number_, number_5_3, varchar2_100, date_ from oci_test_table where "
"number_ > :1 or number_5_3 = :2 or varchar2_100 < :3", err_msg, &conn))) {
printf(err_msg);
goto clean_up;
}
if (! oci_define(sh, err_msg, &conn, 4, SQLT_INT, &i_out, SQLT_FLT, &d_out, SQLT_CHR, c_out, 100, SQLT_ODT, &dt_out)) {
printf(err_msg);
goto clean_up;
}
if (! oci_execute(sh, err_msg, &conn, 3, SQLT_INT, 5, SQLT_FLT, 4.8, SQLT_CHR, "zzz")) {
printf(err_msg);
goto clean_up;
}
while (oci_fetch(sh, err_msg,&conn)) {
OCIDateGetDate(&dt_out, &year, &month , &day );
OCIDateGetTime(&dt_out, &hour, &minute, &second);
printf ("fetched %d %f %s %02d.%02d.%04d %02d:%02d:%02d\n",i_out, d_out, c_out, day, month, year, hour, minute, second);
}
clean_up:
free (c_out);
OCITerminate(OCI_DEFAULT);
printf("\n");
return 0;
}
/* convert an Oracle date to epoch */
double ocidate_to_epoch(OCIDate* ocidate) {
int year, month, day, hour, minute, second;
struct tm ut;
OCIDateGetDate(ocidate, &year, &month, &day);
OCIDateGetTime(ocidate, &hour, &minute, &second);
ut.tm_year = year - 1900; ut.tm_mon = month - 1; ut.tm_mday = day;
ut.tm_hour = hour; ut.tm_min = minute; ut.tm_sec = second; ut.tm_isdst = -1;
mktime(&ut); // should fix tm_isdst
double d = (double)mktime(&ut);
#ifdef DEBUG
char dbuf[256]; snprintf(dbuf, 255, "ocidate_to_epoch: epoch=%f year=%d month=%d day=%d is_dst=%d", d, ut.tm_year + 1900, ut.tm_mon + 1, ut.tm_mday, ut.tm_isdst); debug(dbuf);
#endif
return d;
}
sword _fetchDataIntoDateField(ORACLE_SQL_CURSOR *cursor, PA_Variable variable, unsigned int pos)
{
if(cursor->rowsFetched)
{
short d;
short m;
short y;
if(cursor->indicators.at(pos) != -1)
{
OCIDateGetDate(&cursor->dates.at(pos), &y, &m, &d);
PA_SetDateField(variable.uValue.fTableFieldDefinition.fTableNumber, variable.uValue.fTableFieldDefinition.fFieldNumber, d, m, y);
}else{
setDateFieldValueNull(variable);
}
}
return cursor->rowsFetched;
}
/* convert an Oracle date to epoch */
double ocidate_to_epoch(OCIDate* ocidate) {
int year, month, day, hour, minute, second;
time_t t;
struct tm* ut;
OCIDateGetDate(ocidate, &year, &month, &day);
OCIDateGetTime(ocidate, &hour, &minute, &second);
time (&t);
ut = localtime(&t);
ut->tm_year = year - 1900; ut->tm_mon = month - 1; ut->tm_mday = day;
ut->tm_hour = hour - 1; ut->tm_min = minute; ut->tm_sec = second - 1;
double d = (double)mktime(ut);
#ifdef DEBUG
char dbuf[256]; snprintf(dbuf, 255, "ocidate_to_epoch: epoch=%f year=%d month=%d day=%d", d, ut->tm_year + 1900, ut->tm_mon + 1, ut->tm_mday); debug(dbuf);
#endif
return d;
}
sword _fetchDataIntoDateVariable(ORACLE_SQL_CURSOR *cursor, PA_Variable variable, unsigned int pos)
{
if(cursor->rowsFetched)
{
short d;
short m;
short y;
if(cursor->indicators.at(pos) != -1)
{
OCIDateGetDate(&cursor->dates.at(pos), &y, &m, &d);
PA_SetDateVariable(&variable, d, m, y);
}else{
setDateVariableValueNull(variable);
}
PA_SetPointerValue(&cursor->pointers.at(pos), variable);
}
return cursor->rowsFetched;
}
/*
* Convert data fron db format to internal format
*/
static int convert_row(db_res_t* _res, db_row_t* _r, dmap_t* _d)
{
unsigned i, n = RES_COL_N(_res);
ROW_N(_r) = n;
for (i = 0; i < n; i++) {
static const str dummy_string = {"", 0};
db_val_t* v = &ROW_VALUES(_r)[i];
db_type_t t = RES_TYPES(_res)[i];
if (_d->ind[i] == -1) {
/* Initialize the string pointers to a dummy empty
* string so that we do not crash when the NULL flag
* is set but the module does not check it properly
*/
VAL_STRING(v) = dummy_string.s;
VAL_STR(v) = dummy_string;
VAL_BLOB(v) = dummy_string;
VAL_TYPE(v) = t;
VAL_NULL(v) = 1;
continue;
}
if (_d->ind[i])
LM_WARN("truncated value in DB\n");
VAL_TYPE(v) = t;
switch (t) {
case DB_INT:
VAL_INT(v) = *_d->pv[i].i;
break;
case DB_BIGINT:
VAL_BIGINT(v) = *_d->pv[i].i;
break;
case DB_BITMAP:
VAL_BITMAP(v) = *_d->pv[i].i;
break;
case DB_DOUBLE:
VAL_DOUBLE(v) = *_d->pv[i].f;
break;
case DB_DATETIME:
{
struct tm tm;
memset(&tm, 0, sizeof(tm));
OCIDateGetTime(_d->pv[i].o, &tm.tm_hour,
&tm.tm_min, &tm.tm_sec);
OCIDateGetDate(_d->pv[i].o, &tm.tm_year,
&tm.tm_mon, &tm.tm_mday);
if (tm.tm_mon)
--tm.tm_mon;
if (tm.tm_year >= 1900)
tm.tm_year -= 1900;
VAL_TIME(v) = mktime(&tm);
}
break;
case DB_STR:
case DB_BLOB:
case DB_STRING:
{
size_t len = _d->len[i];
char *pstr = pkg_malloc(len+1);
if (pstr == NULL)
return -1;
memcpy(pstr, _d->pv[i].c, len);
pstr[len] = '\0';
VAL_FREE(v) = 1;
if (t == DB_STR) {
VAL_STR(v).s = pstr;
VAL_STR(v).len = len;
} else if (t == DB_BLOB) {
VAL_BLOB(v).s = pstr;
VAL_BLOB(v).len = len;
} else {
VAL_STRING(v) = pstr;
}
}
break;
default:
LM_ERR("unknown type mapping (%u)\n", t);
return -2;
}
}
return 0;
}
void
_gda_oracle_set_value (GValue *value,
GdaOracleValue *ora_value,
GdaConnection *cnc)
{
GdaTime gtime;
GdaTimestamp timestamp;
sb2 year;
ub1 month;
ub1 day;
ub1 hour;
ub1 min;
ub1 sec;
if (ora_value->indicator == -1) {
gda_value_set_null (value);
return;
}
gda_value_reset_with_type (value, ora_value->g_type);
switch (ora_value->s_type) {
case GDA_STYPE_INT:
g_value_set_int (value, *((gint *) ora_value->value));
break;
case GDA_STYPE_STRING: {
gchar *string_buffer, *tmp;
string_buffer = (gchar *) ora_value->value;
string_buffer [ora_value->rlen] = '\0';
g_strchomp (string_buffer);
//tmp = g_locale_to_utf8 (string_buffer, -1, NULL, NULL, NULL);
//g_value_take_string (value, tmp);
g_value_set_string (value, string_buffer);
if (ora_value->use_callback) {
g_free (string_buffer);
ora_value->value = NULL;
}
break;
}
case GDA_STYPE_BOOLEAN:
g_value_set_boolean (value, (*((gint *) ora_value->value)) ? TRUE: FALSE);
break;
case GDA_STYPE_DATE: {
GDate *date;
OCIDateGetDate ((CONST OCIDate *) ora_value->value,
(sb2 *) &year,
(ub1 *) &month,
(ub1 *) &day);
date = g_date_new_dmy (day, month, year);
g_value_take_boxed (value, date);
break;
}
case GDA_STYPE_TIME: {
OCIDateGetTime ((CONST OCIDate *) ora_value->value,
(ub1 *) &hour,
(ub1 *) &min,
(ub1 *) &sec);
gtime.hour = hour;
gtime.minute = min;
gtime.second = sec;
gda_value_set_time (value, >ime);
break;
}
case GDA_STYPE_TIMESTAMP: {
OCIDateGetDate ((CONST OCIDate *) ora_value->value,
(sb2 *) &year,
(ub1 *) &month,
(ub1 *) &day);
OCIDateGetTime ((CONST OCIDate *) ora_value->value,
(ub1 *) &hour,
(ub1 *) &min,
(ub1 *) &sec);
timestamp.year = year;
timestamp.month = month;
timestamp.day = day;
timestamp.hour = hour;
timestamp.minute = min;
timestamp.second = sec;
timestamp.fraction = 0;
timestamp.timezone = 0;
gda_value_set_timestamp(value, ×tamp);
break;
}
case GDA_STYPE_INT64:
TO_IMPLEMENT; /* test that value fits in */
g_value_set_int64 (value, atoll (ora_value->value));
break;
case GDA_STYPE_UINT64:
TO_IMPLEMENT; /* test that value fits in */
g_value_set_uint64 (value, atoll (ora_value->value));
break;
case GDA_STYPE_UINT:
TO_IMPLEMENT; /* test that value fits in */
g_value_set_uint (value, *((guint*) ora_value->value));
break;
case GDA_STYPE_FLOAT:
g_value_set_float (value, *((gfloat*) ora_value->value));
break;
case GDA_STYPE_DOUBLE:
g_value_set_double (value, *((gdouble*) ora_value->value));
break;
case GDA_STYPE_LONG:
TO_IMPLEMENT;
break;
case GDA_STYPE_ULONG:
TO_IMPLEMENT;
break;
case GDA_STYPE_NUMERIC: {
GdaNumeric *numeric;
gchar *tmp;
g_assert (!ora_value->use_callback);
tmp = g_malloc0 (ora_value->defined_size);
memcpy (tmp, ora_value->value, ora_value->defined_size);
tmp [ora_value->rlen] = '\0';
g_strchomp (tmp);
numeric = gda_numeric_new ();
gda_numeric_set_from_string (numeric, tmp);
g_free (tmp);
gda_numeric_set_precision (numeric, ora_value->precision);
gda_numeric_set_width (numeric, ora_value->scale);
g_value_take_boxed (value, numeric);
break;
}
case GDA_STYPE_BINARY: {
GdaBinary *bin;
bin = g_new0 (GdaBinary, 1);
if (ora_value->use_callback) {
bin->data = ora_value->value;
ora_value->value = NULL;
}
else {
bin->data = g_new (guchar, ora_value->rlen);
memcpy (bin->data, ora_value->value, ora_value->rlen);
}
bin->binary_length = ora_value->rlen;
gda_value_take_binary (value, bin);
break;
}
case GDA_STYPE_BLOB: {
GdaBlob *blob;
GdaBlobOp *op;
OCILobLocator *lobloc;
OracleConnectionData *cdata;
gint result;
/* REM: we need to make a "copy" of the lob locator to give to the GdaOracleblobOp object */
cdata = (OracleConnectionData*) gda_connection_internal_get_provider_data_error (cnc, NULL);
if (!cdata) {
gda_connection_add_event_string (cnc, _("Invalid Oracle handle"));
gda_value_set_null (value);
return;
}
result = OCIDescriptorAlloc ((dvoid *) cdata->henv, (dvoid **) &lobloc,
(ub4) gda_oracle_blob_type (ora_value->sql_type), (size_t) 0, (dvoid **) 0);
if (gda_oracle_check_result (result, cnc, cdata, OCI_HTYPE_ERROR,
_("Could not allocate Lob locator"))) {
gda_value_set_null (value);
return;
}
result = OCILobAssign ((dvoid *) cdata->henv, (dvoid *) cdata->herr, ora_value->value, &lobloc);
if (gda_oracle_check_result (result, cnc, cdata, OCI_HTYPE_ERROR,
_("Could not copy Lob locator"))) {
gda_value_set_null (value);
return;
}
blob = g_new0 (GdaBlob, 1);
op = gda_oracle_blob_op_new (cnc, lobloc);
gda_blob_set_op (blob, op);
g_object_unref (op);
gda_value_take_blob (value, blob);
break;
}
case GDA_STYPE_CHAR: {
TO_IMPLEMENT; /* test that value fits in */
g_value_set_schar (value, *((gint8*) ora_value->value));
break;
}
case GDA_STYPE_SHORT: {
TO_IMPLEMENT; /* test that value fits in */
gda_value_set_short (value, *((gint*) ora_value->value));
break;
}
case GDA_STYPE_GTYPE:
TO_IMPLEMENT;
break;
case GDA_STYPE_GEOMETRIC_POINT:
TO_IMPLEMENT;
break;
case GDA_STYPE_NULL:
gda_value_set_null (value);
break;
default:
g_assert_not_reached ();
}
}
