firebird/examples/api/apifull.c

/*
 *    Program type:  API Interface
 *
 *    Description:
 *    This program prompts for and executes unknown SQL statements.
 * The contents of this file are subject to the Interbase Public
 * License Version 1.0 (the "License"); you may not use this file
 * except in compliance with the License. You may obtain a copy
 * of the License at http://www.Inprise.com/IPL.html
 *
 * Software distributed under the License is distributed on an
 * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express
 * or implied. See the License for the specific language governing
 * rights and limitations under the License.
 *
 * The Original Code was created by Inprise Corporation
 * and its predecessors. Portions created by Inprise Corporation are
 * Copyright (C) Inprise Corporation.
 *
 * All Rights Reserved.
 * Contributor(s): ______________________________________.
 */

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#if TIME_WITH_SYS_TIME
# include <sys/time.h>
# include <time.h>
#else
# if HAVE_SYS_TIME_H
#  include <sys/time.h>
# else
#  include <time.h>
# endif
#endif
#include <ctype.h>
#include <ibase.h>
#include "align.h"
#include "example.h"

#define    MAXLEN    1024

process_statement (XSQLDA ** sqlda, char *query);
void print_column (XSQLVAR * var);
int get_statement (char * buf);

/* ibase.h contains PARAMVARY, with the handicap that vary_string is unsigned char*
instead of plain char*.
Maybe we should fix ibase.h for users but without affecting the engine */
/* typedef struct vary2 {
    unsigned short vary_length;
    char           vary_string [1];
} VARY2;
*/
typedef PARAMVARY VARY2;

isc_db_handle      db = NULL;
isc_tr_handle      trans = NULL;
isc_stmt_handle    stmt = NULL;
ISC_STATUS_ARRAY   status;
int                ret;

#ifndef ISC_INT64_FORMAT

/* Define a format string for printf.  Printing of 64-bit integers
   is not standard between platforms */

#if (defined(_MSC_VER) && defined(WIN32))
#define	ISC_INT64_FORMAT	"I64"
#else
#define	ISC_INT64_FORMAT	"ll"
#endif
#endif


int main (int argc, char** argv)
{
    int                   query[MAXLEN];
    XSQLDA    *            sqlda;
    char                   db_name[128];

    if (argc < 2)
    {
        printf("Enter the database name:  ");
        gets(db_name);
    }
    else
    {
        strcpy(db_name, argv[1]);
    }

    if (isc_attach_database(status, 0, db_name, &db, 0, NULL))
    {
        printf("Could not open database %s\n", db_name);
        ERREXIT(status, 1);
    }                      

    /* 
    *    Allocate enough space for 20 fields.  
    *    If more fields get selected, re-allocate SQLDA later.
     */
    sqlda = (XSQLDA *) malloc(XSQLDA_LENGTH (20));
    sqlda->sqln = 20;
    sqlda->version = 1;

    /* Allocate a global statement */
    if (isc_dsql_allocate_statement(status, &db, &stmt))
    {
	free (sqlda);
        ERREXIT(status,1)
    }

    /*
     *    Process SQL statements.
     */
    ret = get_statement((char *) query);
    /* Use break on error or exit */
    while (ret != 1)
    {
        /* We must pass the address of sqlda, in case it
        ** gets re-allocated 
        */
        ret = process_statement(&sqlda,
                                (char *) query);
            if (ret == 1)
                break;
        ret = get_statement((char *) query);
    }

    free (sqlda);
    if (trans)
        if (isc_commit_transaction(status, &trans))
	{
	    ERREXIT(status,1);
	}

    if (isc_detach_database(status, &db))
    {
		ERREXIT(status,1);
    }

    return ret;
}

/* 
**  Function:  process_statement
**  Process submitted statement.  On any fundamental error, return status 1,
**  which will do an isc_print_status and exit the program.
**  On user errors, found in parsing or executing go to status 2,
**  which will print the error and continue.
*/

process_statement (XSQLDA **sqldap, char *query)
{
    int            buffer[MAXLEN];
    XSQLDA          *sqlda;
    XSQLVAR         *var;
    short           num_cols, i;
    short           length, alignment, type, offset;
    int            fetch_stat;
    static char     stmt_info[] = { isc_info_sql_stmt_type };
    char            info_buffer[20];
    short           l;
    int            statement_type;

    sqlda = *sqldap;

    /* Start a transaction if we are not in one */
    if (!trans)
        if (isc_start_transaction(status, &trans, 1, &db, 0, NULL))
        {
            ERREXIT(status, 1)
        }

    if (isc_dsql_prepare(status, &trans, &stmt, 0, query, SQL_DIALECT_V6, sqlda))
    {
        ERREXIT(status,2)
    }

    /* What is the statement type of this statement? 
    **
    ** stmt_info is a 1 byte info request.  info_buffer is a buffer
    ** large enough to hold the returned info packet
    ** The info_buffer returned contains a isc_info_sql_stmt_type in the first byte, 
    ** two bytes of length, and a statement_type token.
    */
    if (!isc_dsql_sql_info(status, &stmt, sizeof (stmt_info), stmt_info,
        sizeof (info_buffer), info_buffer))
    {
        l = (short) isc_vax_integer((char *) info_buffer + 1, 2);
        statement_type = isc_vax_integer((char *) info_buffer + 3, l);
    }


    /*
     *    Execute a non-select statement.
     */
    if (!sqlda->sqld)
    {
        if (isc_dsql_execute(status, &trans, &stmt, SQL_DIALECT_V6, NULL))
        {
            ERREXIT(status,2)
        }

        /* Commit DDL statements if that is what sql_info says */

        if (trans && (statement_type == isc_info_sql_stmt_ddl))
        {
            printf ("\tCommitting...\n");
            if (isc_commit_transaction(status, &trans))
            {
                ERREXIT(status, 2)
            }    
        }

        return 0;
    }

    /*
     *    Process select statements.
     */

    num_cols = sqlda->sqld;

    /* Need more room. */
    if (sqlda->sqln < num_cols)
    {
        *sqldap = sqlda = (XSQLDA *) realloc(sqlda,
                                                XSQLDA_LENGTH (num_cols));
        sqlda->sqln = num_cols;
        sqlda->version = 1;

        if (isc_dsql_describe(status, &stmt, SQL_DIALECT_V6, sqlda))
        {
            ERREXIT(status,2)
        }

        num_cols = sqlda->sqld;
    }

    /*
     *     Set up SQLDA.
     */
    for (var = sqlda->sqlvar, offset = 0, i = 0; i < num_cols; var++, i++)
    {
        length = alignment = var->sqllen;
        type = var->sqltype & ~1;

        if (type == SQL_TEXT)
            alignment = 1;
        else if (type == SQL_VARYING)
        {   
            length += sizeof (short) + 1;
            alignment = sizeof (short);
        }
        /*  RISC machines are finicky about word alignment
        **  So the output buffer values must be placed on
        **  word boundaries where appropriate
        */
        offset = FB_ALIGN(offset, alignment);
        var->sqldata = (char *) buffer + offset;
        offset += length;
        offset = FB_ALIGN(offset, sizeof (short));
        var->sqlind = (short*) ((char *) buffer + offset);
        offset += sizeof  (short);
    }

    if (isc_dsql_execute(status, &trans, &stmt, SQL_DIALECT_V6, NULL))
    {
        ERREXIT(status,2)
    }

    /*
     *    Print rows.
     */

    while ((fetch_stat = isc_dsql_fetch(status, &stmt, SQL_DIALECT_V6, sqlda)) == 0)
    {
        for (i = 0; i < num_cols; i++)
        {
            print_column((XSQLVAR *) &sqlda->sqlvar[i]);
        }
        printf("\n");
    }

    /* Close cursor */
    if (isc_dsql_free_statement(status, &stmt, DSQL_close))
    {
		ERREXIT (status,2);
    }

    if (fetch_stat != 100L)
    {
        ERREXIT(status,2)
    }

    return 0;
}

/*
 *    Print column's data.
 */
void print_column (XSQLVAR *var)
{
    short       dtype;
    char        data[MAXLEN], *p;
    char        blob_s[20], date_s[25];
    VARY2        *vary2;
    short       len; 
    struct tm   times;
    ISC_QUAD    bid;

    dtype = var->sqltype & ~1;
    p = data;

    /* Null handling.  If the column is nullable and null */
    if ((var->sqltype & 1) && (*var->sqlind < 0))
    {
        switch (dtype)
        {
            case SQL_TEXT:
            case SQL_VARYING:
                len = var->sqllen;
                break;
            case SQL_SHORT:
                len = 6;
		if (var->sqlscale > 0) len += var->sqlscale;
                break;
            case SQL_LONG:
                len = 11;
		if (var->sqlscale > 0) len += var->sqlscale;
                break;
	    case SQL_INT64:
		len = 21;
		if (var->sqlscale > 0) len += var->sqlscale;
		break;
            case SQL_FLOAT:
                len = 15;
                break;
            case SQL_DOUBLE:
                len = 24;
                break;
	    case SQL_TIMESTAMP:
		len = 24;
		break;
	    case SQL_TYPE_DATE:
		len = 10;
		break;
	    case SQL_TYPE_TIME:
		len = 13;
		break;
            case SQL_BLOB:
            case SQL_ARRAY:
            default:
                len = 17;
                break;
        }
        if ((dtype == SQL_TEXT) || (dtype == SQL_VARYING))
            sprintf(p, "%-*s ", len, "NULL");
        else
            sprintf(p, "%*s ", len, "NULL");
    }
    else
    {
        switch (dtype)
        {
            case SQL_TEXT:
                sprintf(p, "%.*s ", var->sqllen, var->sqldata);
                break;

            case SQL_VARYING:
                vary2 = (VARY2*) var->sqldata;
                vary2->vary_string[vary2->vary_length] = '\0';
                sprintf(p, "%-*s ", var->sqllen, vary2->vary_string);
                break;

            case SQL_SHORT:
            case SQL_LONG:
	    case SQL_INT64:
		{
		ISC_INT64	value;
		short		field_width;
		short		dscale;
		switch (dtype)
		    {
		    case SQL_SHORT:
			value = (ISC_INT64) *(short *) var->sqldata;
			field_width = 6;
			break;
		    case SQL_LONG:
			value = (ISC_INT64) *(int *) var->sqldata;
			field_width = 11;
			break;
		    case SQL_INT64:
			value = (ISC_INT64) *(ISC_INT64 *) var->sqldata;
			field_width = 21;
			break;
		    }
		dscale = var->sqlscale;
		if (dscale < 0)
		    {
		    ISC_INT64	tens;
		    short	i;

		    tens = 1;
		    for (i = 0; i > dscale; i--)
			tens *= 10;

		    if (value >= 0)
			sprintf (p, "%*" ISC_INT64_FORMAT "d.%0*" ISC_INT64_FORMAT "d",
				field_width - 1 + dscale, 
				(ISC_INT64) value / tens,
				-dscale, 
				(ISC_INT64) value % tens);
		    else if ((value / tens) != 0)
			sprintf (p, "%*" ISC_INT64_FORMAT "d.%0*" ISC_INT64_FORMAT "d",
				field_width - 1 + dscale, 
				(ISC_INT64) (value / tens),
				-dscale, 
				(ISC_INT64) -(value % tens));
		    else
			sprintf (p, "%*s.%0*" ISC_INT64_FORMAT "d",
				field_width - 1 + dscale, 
				"-0",
				-dscale, 
				(ISC_INT64) -(value % tens));
		    }
		else if (dscale)
		    sprintf (p, "%*" ISC_INT64_FORMAT "d%0*d", 
			    field_width, 
			    (ISC_INT64) value,
			    dscale, 0);
		else
		    sprintf (p, "%*" ISC_INT64_FORMAT "d%",
			    field_width, 
			    (ISC_INT64) value);
		}
                break;

            case SQL_FLOAT:
                sprintf(p, "%15g ", *(float *) (var->sqldata));
                break;

            case SQL_DOUBLE:
		sprintf(p, "%24f ", *(double *) (var->sqldata));
                break;

	    case SQL_TIMESTAMP:
		isc_decode_timestamp((ISC_TIMESTAMP *)var->sqldata, &times);
		sprintf(date_s, "%04d-%02d-%02d %02d:%02d:%02d.%04d",
				times.tm_year + 1900,
				times.tm_mon+1,
				times.tm_mday,
				times.tm_hour,
				times.tm_min,
				times.tm_sec,
				((ISC_TIMESTAMP *)var->sqldata)->timestamp_time % 10000);
		sprintf(p, "%*s ", 24, date_s);
		break;

	    case SQL_TYPE_DATE:
		isc_decode_sql_date((ISC_DATE *)var->sqldata, &times);
		sprintf(date_s, "%04d-%02d-%02d",
				times.tm_year + 1900,
				times.tm_mon+1,
				times.tm_mday);
		sprintf(p, "%*s ", 10, date_s);
		break;

	    case SQL_TYPE_TIME:
		isc_decode_sql_time((ISC_TIME *)var->sqldata, &times);
		sprintf(date_s, "%02d:%02d:%02d.%04d",
				times.tm_hour,
				times.tm_min,
				times.tm_sec,
				(*((ISC_TIME *)var->sqldata)) % 10000);
		sprintf(p, "%*s ", 13, date_s);
		break;

            case SQL_BLOB:
            case SQL_ARRAY:
                /* Print the blob id on blobs or arrays */
                bid = *(ISC_QUAD *) var->sqldata;
                sprintf(blob_s, "%08x:%08x", bid.gds_quad_high, bid.gds_quad_low);
                sprintf(p, "%17s ", blob_s);
                break;

            default:
                break;
        }
    }

    while (*p)
    {
        putchar(*p++);
    }
    
}


/*
 *    Prompt for and get input.
 *    Statements are terminated by a semicolon.
 */
int get_statement (char *buf)
{
    short   c;
    char    *p;
    int     cnt;

    p = buf;
    cnt = 0;
    printf("SQL> ");

    for (;;)
    {
        if ((c = getchar()) == EOF)
            return 1;

        if (c == '\n')
        {
            /* accept "quit" or "exit" to terminate application */

            if (!strncmp(buf, "exit", 4))
                return 1;
            if (!strncmp(buf, "quit", 4))
                return 1;

            /* Search back through white space looking for ';'.*/
            while (cnt && isspace(*(p - 1)))
            {
                p--;
                cnt--;
            }
            if (*(p - 1) == ';')
            {
                *p++ = '\0';

                return 0;
            }
            *p++ = ' ';
            printf("CON> ");
        }
        else
        {
            *p++ = (char)c;
        }
        cnt++;
    }
}