/* * 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++; } }