convert.c

关系型数据库 Postgresql 6.5.2

/* Module:         convert.c
 *
 * Description:	   This module contains routines related to 
 *                 converting parameters and columns into requested data types.
 *                 Parameters are converted from their SQL_C data types into
 *                 the appropriate postgres type.  Columns are converted from
 *                 their postgres type (SQL type) into the appropriate SQL_C 
 *                 data type.
 *
 * Classes:        n/a
 *
 * API functions:  none
 *
 * Comments:       See "notice.txt" for copyright and license information.
 *
 */

#ifdef HAVE_CONFIG_H
#include "config.h"
#endif

#include <stdio.h>
#include <string.h>
#include <ctype.h>

#include "psqlodbc.h"

#ifndef WIN32
#include "iodbc.h"
#include "isql.h"
#include "isqlext.h"
#else
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#endif

#include <time.h>
#include <math.h>
#include "convert.h"
#include "statement.h"
#include "bind.h"
#include "pgtypes.h"
#include "lobj.h"
#include "connection.h"

#ifndef WIN32
#ifndef HAVE_STRICMP
#define stricmp(s1,s2) strcasecmp(s1,s2)
#define strnicmp(s1,s2,n) strncasecmp(s1,s2,n)
#endif
#ifndef SCHAR
typedef signed char SCHAR;
#endif
#endif

extern GLOBAL_VALUES globals;

/*	How to map ODBC scalar functions {fn func(args)} to Postgres */
/*	This is just a simple substitution */
char *mapFuncs[][2] = {   
	{ "CONCAT",      "textcat" },
	{ "LCASE",       "lower"   },
	{ "LOCATE",      "strpos"  },
	{ "LENGTH",      "textlen" },
	{ "LTRIM",       "ltrim"   },
	{ "RTRIM",       "rtrim"   },
	{ "SUBSTRING",   "substr"  },
	{ "UCASE",       "upper"   },
	{ "NOW",         "now"     },
	{    0,             0      }
};

char *mapFunction(char *func);
unsigned int conv_from_octal(unsigned char *s);
unsigned int conv_from_hex(unsigned char *s);
char *conv_to_octal(unsigned char val);

/********		A Guide for date/time/timestamp conversions    **************

			field_type		fCType				Output
			----------		------				----------
			PG_TYPE_DATE	SQL_C_DEFAULT		SQL_C_DATE
			PG_TYPE_DATE	SQL_C_DATE			SQL_C_DATE
			PG_TYPE_DATE	SQL_C_TIMESTAMP		SQL_C_TIMESTAMP		(time = 0 (midnight))
			PG_TYPE_TIME	SQL_C_DEFAULT		SQL_C_TIME
			PG_TYPE_TIME	SQL_C_TIME			SQL_C_TIME
			PG_TYPE_TIME	SQL_C_TIMESTAMP		SQL_C_TIMESTAMP		(date = current date)
			PG_TYPE_ABSTIME	SQL_C_DEFAULT		SQL_C_TIMESTAMP
			PG_TYPE_ABSTIME	SQL_C_DATE			SQL_C_DATE			(time is truncated)
			PG_TYPE_ABSTIME	SQL_C_TIME			SQL_C_TIME			(date is truncated)
			PG_TYPE_ABSTIME	SQL_C_TIMESTAMP		SQL_C_TIMESTAMP		
******************************************************************************/


/*	This is called by SQLFetch() */
int
copy_and_convert_field_bindinfo(StatementClass *stmt, Int4 field_type, void *value, int col)
{
BindInfoClass *bic = &(stmt->bindings[col]);

	return copy_and_convert_field(stmt, field_type, value, (Int2)bic->returntype, (PTR)bic->buffer,
                                (SDWORD)bic->buflen, (SDWORD *)bic->used);
}

/*	This is called by SQLGetData() */
int
copy_and_convert_field(StatementClass *stmt, Int4 field_type, void *value, Int2 fCType, 
					   PTR rgbValue, SDWORD cbValueMax, SDWORD *pcbValue)
{
Int4 len = 0, copy_len = 0;
SIMPLE_TIME st;
time_t t = time(NULL);
struct tm *tim;
int pcbValueOffset, rgbValueOffset;
char *rgbValueBindRow, *ptr;
int bind_row = stmt->bind_row;
int bind_size = stmt->options.bind_size;
int result = COPY_OK;
char tempBuf[TEXT_FIELD_SIZE+5];

/*	rgbValueOffset is *ONLY* for character and binary data */
/*	pcbValueOffset is for computing any pcbValue location */

	if (bind_size > 0) {

		pcbValueOffset = rgbValueOffset = (bind_size * bind_row);
	}
	else {

		pcbValueOffset = bind_row * sizeof(SDWORD);
		rgbValueOffset = bind_row * cbValueMax;

	}

	memset(&st, 0, sizeof(SIMPLE_TIME));

	/*	Initialize current date */
	tim = localtime(&t);
	st.m = tim->tm_mon + 1;
	st.d = tim->tm_mday;
	st.y = tim->tm_year + 1900;

	mylog("copy_and_convert: field_type = %d, fctype = %d, value = '%s', cbValueMax=%d\n", field_type, fCType,  (value==NULL)?"<NULL>":value, cbValueMax);

	if ( ! value) {
        /* handle a null just by returning SQL_NULL_DATA in pcbValue, */
        /* and doing nothing to the buffer.                           */
        if(pcbValue) {
			*(SDWORD *) ((char *) pcbValue + pcbValueOffset) = SQL_NULL_DATA;
        }
		return COPY_OK;
	}


	if (stmt->hdbc->DataSourceToDriver != NULL) {
		int length = strlen (value);
		stmt->hdbc->DataSourceToDriver (stmt->hdbc->translation_option,
										SQL_CHAR,
										value, length,
										value, length, NULL,
										NULL, 0, NULL);
	}


	/********************************************************************
		First convert any specific postgres types into more
		useable data.

		NOTE: Conversions from PG char/varchar of a date/time/timestamp 
		value to SQL_C_DATE,SQL_C_TIME, SQL_C_TIMESTAMP not supported 
	*********************************************************************/
	switch(field_type) {
	/*  $$$ need to add parsing for date/time/timestamp strings in PG_TYPE_CHAR,VARCHAR $$$ */
	case PG_TYPE_DATE:
		sscanf(value, "%4d-%2d-%2d", &st.y, &st.m, &st.d);
		break;

	case PG_TYPE_TIME:
		sscanf(value, "%2d:%2d:%2d", &st.hh, &st.mm, &st.ss);
		break;

	case PG_TYPE_ABSTIME:
	case PG_TYPE_DATETIME:
	case PG_TYPE_TIMESTAMP:
		if (strnicmp(value, "invalid", 7) != 0) {
			sscanf(value, "%4d-%2d-%2d %2d:%2d:%2d", &st.y, &st.m, &st.d, &st.hh, &st.mm, &st.ss);

		} else {	/* The timestamp is invalid so set something conspicuous, like the epoch */
			t = 0;
			tim = localtime(&t);
			st.m = tim->tm_mon + 1;
			st.d = tim->tm_mday;
			st.y = tim->tm_year + 1900;
			st.hh = tim->tm_hour;
			st.mm = tim->tm_min;
			st.ss = tim->tm_sec;
		}
		break;

	case PG_TYPE_BOOL: {		/* change T/F to 1/0 */
		char *s = (char *) value;
		if (s[0] == 'T' || s[0] == 't') 
			s[0] = '1';
		else 
			s[0] = '0';
		}
		break;

	/* This is for internal use by SQLStatistics() */
	case PG_TYPE_INT28: {
		// this is an array of eight integers
		short *short_array = (short *) ( (char *) rgbValue + rgbValueOffset);

		len = 16;

		sscanf(value, "%hd %hd %hd %hd %hd %hd %hd %hd",
			&short_array[0],
			&short_array[1],
			&short_array[2],
			&short_array[3],
			&short_array[4],
			&short_array[5],
			&short_array[6],
			&short_array[7]);

		/*  There is no corresponding fCType for this. */
		if(pcbValue)
			*(SDWORD *) ((char *) pcbValue + pcbValueOffset) = len;

		return COPY_OK;		/* dont go any further or the data will be trashed */
						}

	/* This is a large object OID, which is used to store LONGVARBINARY objects. */
	case PG_TYPE_LO:

		return convert_lo( stmt, value, fCType, ((char *) rgbValue + rgbValueOffset), cbValueMax, (SDWORD *) ((char *) pcbValue + pcbValueOffset));

	default:

		if (field_type == stmt->hdbc->lobj_type)	/* hack until permanent type available */
			return convert_lo( stmt, value, fCType, ((char *) rgbValue + rgbValueOffset), cbValueMax, (SDWORD *) ((char *) pcbValue + pcbValueOffset));
	}

	/*  Change default into something useable */
	if (fCType == SQL_C_DEFAULT) {
		fCType = pgtype_to_ctype(stmt, field_type);

		mylog("copy_and_convert, SQL_C_DEFAULT: fCType = %d\n", fCType);
	}


	rgbValueBindRow = (char *) rgbValue + rgbValueOffset;

    if(fCType == SQL_C_CHAR) {


		/*	Special character formatting as required */
		/*	These really should return error if cbValueMax is not big enough. */
		switch(field_type) {
		case PG_TYPE_DATE:
		    len = 10;
			if (cbValueMax > len)
				sprintf(rgbValueBindRow, "%.4d-%.2d-%.2d", st.y, st.m, st.d);
			break;

		case PG_TYPE_TIME:
			len = 8;
			if (cbValueMax > len)
				sprintf(rgbValueBindRow, "%.2d:%.2d:%.2d", st.hh, st.mm, st.ss);
			break;

		case PG_TYPE_ABSTIME:
		case PG_TYPE_DATETIME:
		case PG_TYPE_TIMESTAMP:
			len = 19;
			if (cbValueMax > len)
				sprintf(rgbValueBindRow, "%.4d-%.2d-%.2d %.2d:%.2d:%.2d", 
					st.y, st.m, st.d, st.hh, st.mm, st.ss);
			break;

		case PG_TYPE_BOOL:
			len = 1;
			if (cbValueMax > len) {
				strcpy(rgbValueBindRow, value);
				mylog("PG_TYPE_BOOL: rgbValueBindRow = '%s'\n", rgbValueBindRow);
			}
			break;

		/*	Currently, data is SILENTLY TRUNCATED for BYTEA and character data
			types if there is not enough room in cbValueMax because the driver 
			can't handle multiple calls to SQLGetData for these, yet.  Most likely,
			the buffer passed in will be big enough to handle the maximum limit of 
			postgres, anyway.

			LongVarBinary types are handled correctly above, observing truncation
			and all that stuff since there is essentially no limit on the large
			object used to store those.
		*/
		case PG_TYPE_BYTEA:		// convert binary data to hex strings (i.e, 255 = "FF")
			len = convert_pgbinary_to_char(value, rgbValueBindRow, cbValueMax);

			/***** THIS IS NOT PROPERLY IMPLEMENTED *****/
			break;

		default:
			/*	convert linefeeds to carriage-return/linefeed */
			len = convert_linefeeds(value, tempBuf, sizeof(tempBuf));
			ptr = tempBuf;

			mylog("DEFAULT: len = %d, ptr = '%s'\n", len, ptr);

			if (stmt->current_col >= 0) {
				if (stmt->bindings[stmt->current_col].data_left == 0)
					return COPY_NO_DATA_FOUND;
				else if (stmt->bindings[stmt->current_col].data_left > 0) {
					ptr += len - stmt->bindings[stmt->current_col].data_left;
					len = stmt->bindings[stmt->current_col].data_left;
				}
				else
					stmt->bindings[stmt->current_col].data_left = strlen(ptr);
			}

			if (cbValueMax > 0) {
				
				copy_len = (len >= cbValueMax) ? cbValueMax -1 : len;

				/*	Copy the data */
				strncpy_null(rgbValueBindRow, ptr, copy_len + 1);

				/*	Adjust data_left for next time */
				if (stmt->current_col >= 0) {
					stmt->bindings[stmt->current_col].data_left -= copy_len;
				}
			}

			/*	Finally, check for truncation so that proper status can be returned */
			if ( len >= cbValueMax)
				result = COPY_RESULT_TRUNCATED;


			mylog("    SQL_C_CHAR, default: len = %d, cbValueMax = %d, rgbValueBindRow = '%s'\n", len, cbValueMax, rgbValueBindRow);
			break;
		}


    } else {

		/*	for SQL_C_CHAR, its probably ok to leave currency symbols in.  But
			to convert to numeric types, it is necessary to get rid of those.
		*/
		if (field_type == PG_TYPE_MONEY)
			convert_money(value);

		switch(fCType) {
		case SQL_C_DATE:
			len = 6;
			{
			DATE_STRUCT *ds;
			
			if (bind_size > 0) {
				ds = (DATE_STRUCT *) ((char *) rgbValue + (bind_row * bind_size));
			} else {
				ds = (DATE_STRUCT *) rgbValue + bind_row;
			}
			ds->year = st.y;
			ds->month = st.m;
			ds->day = st.d;
			}
			break;

		case SQL_C_TIME:
			len = 6;
			{
			TIME_STRUCT *ts;
			
			if (bind_size > 0) {
				ts = (TIME_STRUCT *) ((char *) rgbValue + (bind_row * bind_size));
			} else {
				ts = (TIME_STRUCT *) rgbValue + bind_row;
			}
			ts->hour = st.hh;
			ts->minute = st.mm;
			ts->second = st.ss;
			}
			break;

		case SQL_C_TIMESTAMP:					
			len = 16;
			{
			TIMESTAMP_STRUCT *ts;
			if (bind_size > 0) {
				ts = (TIMESTAMP_STRUCT *) ((char *) rgbValue + (bind_row * bind_size));
			} else {
				ts = (TIMESTAMP_STRUCT *) rgbValue + bind_row;
			}
			ts->year = st.y;
			ts->month = st.m;
			ts->day = st.d;
			ts->hour = st.hh;
			ts->minute = st.mm;
			ts->second = st.ss;
			ts->fraction = 0;
			}
			break;

		case SQL_C_BIT:
			len = 1;
			if (bind_size > 0) {
				*(UCHAR *) ((char *) rgbValue + (bind_row * bind_size)) = atoi(value);
			} else {
				*((UCHAR *)rgbValue + bind_row) = atoi(value);
			}
			// mylog("SQL_C_BIT: val = %d, cb = %d, rgb=%d\n", atoi(value), cbValueMax, *((UCHAR *)rgbValue));
			break;

		case SQL_C_STINYINT:
		case SQL_C_TINYINT:
			len = 1;
			if (bind_size > 0) {
				*(SCHAR *) ((char *) rgbValue + (bind_row * bind_size)) = atoi(value);
			} else {
				*((SCHAR *) rgbValue + bind_row) = atoi(value);
			}
			break;

		case SQL_C_UTINYINT:
			len = 1;
			if (bind_size > 0) {
				*(UCHAR *) ((char *) rgbValue + (bind_row * bind_size)) = atoi(value);
			} else {
				*((UCHAR *) rgbValue + bind_row) = atoi(value);
			}
			break;

		case SQL_C_FLOAT:
			len = 4;
			if (bind_size > 0) {
				*(SFLOAT *) ((char *) rgbValue + (bind_row * bind_size)) = (float) atof(value);
			} else {
				*((SFLOAT *)rgbValue + bind_row) = (float) atof(value);
			}
			break;

		case SQL_C_DOUBLE:
			len = 8;
			if (bind_size > 0) {
				*(SDOUBLE *) ((char *) rgbValue + (bind_row * bind_size)) = atof(value);
			} else {
				*((SDOUBLE *)rgbValue + bind_row) = atof(value);
			}
			break;

		case SQL_C_SSHORT:
		case SQL_C_SHORT:
			len = 2;
			if (bind_size > 0) {
				*(SWORD *) ((char *) rgbValue + (bind_row * bind_size)) = atoi(value);
			} else {
				*((SWORD *)rgbValue + bind_row) = atoi(value);
			}
			break;

		case SQL_C_USHORT:
			len = 2;
			if (bind_size > 0) {
				*(UWORD *) ((char *) rgbValue + (bind_row * bind_size)) = atoi(value);
			} else {