/*
* Program type: API Interface
*
* Description:
* This program combines the three programming styles:
* static SQL, dynamic SQL, and the API interface.
*
* Employee information is retrieved and printed for some set
* of employees. A predefined set of columns is always printed.
* However, the 'where' clause, defining which employees the report
* is to be printed for, is unknown.
*
* Dynamic SQL is utilized to construct the select statement.
* The 'where' clause is assumed to be passed as a parameter.
*
* Static SQL is used for known SQL statements.
*
* The API interface is used to access the two databases, and
* to control most transactions. The database and transaction
* handles are shared between the three interfaces.
* 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>
#include "example.h"
#define BUFLEN 1024
char *sel_str =
"SELECT full_name, dept_no, salary, job_code, job_grade, job_country \
FROM employee";
char *where_str =
"WHERE job_code = 'SRep' AND dept_no IN (110, 140, 115, 125, 123, 121)";
/* This macro is used to declare structures representing SQL VARCHAR types */
#define SQL_VARCHAR(len) struct {short vary_length; char vary_string[(len)+1];}
char Db_name[128];
EXEC SQL
SET DATABASE db1 = "employee.fdb" RUNTIME :Db_name;
int main(int argc, char** argv)
{
BASED_ON employee.salary salary;
SQL_VARCHAR(5) job_code;
BASED_ON employee.job_grade job_grade;
SQL_VARCHAR(15) job_country;
SQL_VARCHAR(37) full_name;
BASED_ON country.currency currency;
BASED_ON department.dept_no dept_no;
BASED_ON department.department department;
char buf[BUFLEN + 1];
float rate;
void *trans1 = NULL; /* transaction handle */
void *trans2 = NULL; /* transaction handle */
ISC_STATUS_ARRAY status;
XSQLDA *sqlda;
char empdb2[128];
if (argc > 1)
strcpy(Db_name, argv[1]);
else
strcpy(Db_name, "employee.fdb");
if (argc > 2)
strcpy(empdb2, argv[2]);
else
strcpy(empdb2, "employe2.fdb");
EXEC SQL
WHENEVER SQLERROR GO TO Error;
/*
* Set-up the select query. The select portion of the query is
* static, while the 'where' clause is determined elsewhere and
* passed as a parameter.
*/
sprintf(buf, "%s %s", sel_str, where_str);
/*
* Open the employee database.
*/
if (isc_attach_database(status, 0, Db_name, &db1, 0, NULL))
isc_print_status(status);
/*
* Prepare the select query.
*/
sqlda = (XSQLDA *) malloc(XSQLDA_LENGTH(6));
sqlda->sqln = 6;
sqlda->sqld = 6;
sqlda->version = 1;
EXEC SQL
SET TRANSACTION USING db1;
EXEC SQL
PREPARE q INTO SQL DESCRIPTOR sqlda FROM :buf;
EXEC SQL
COMMIT ;
sqlda->sqlvar[0].sqldata = (char *)&full_name;
sqlda->sqlvar[0].sqltype = SQL_VARYING;
sqlda->sqlvar[1].sqldata = dept_no;
sqlda->sqlvar[1].sqltype = SQL_TEXT;
sqlda->sqlvar[2].sqldata = (char *) &salary;
sqlda->sqlvar[2].sqltype = SQL_DOUBLE;
sqlda->sqlvar[3].sqldata = (char *)&job_code;
sqlda->sqlvar[3].sqltype = SQL_VARYING;
sqlda->sqlvar[4].sqldata = (char *) &job_grade;
sqlda->sqlvar[4].sqltype = SQL_SHORT;
sqlda->sqlvar[5].sqldata = (char *)&job_country;
sqlda->sqlvar[5].sqltype = SQL_VARYING;
/*
* Open the second database.
*/
EXEC SQL
SET DATABASE db2 = "employe2.fdb";
if (isc_attach_database(status, 0, empdb2, &db2, 0, NULL))
isc_print_status(status);
/*
* Select the employees, using the dynamically allocated SQLDA.
*/
EXEC SQL
DECLARE emp CURSOR FOR q;
if (isc_start_transaction(status, &trans1, 1, &db1, 0, NULL))
isc_print_status(status);
EXEC SQL
OPEN TRANSACTION trans1 emp;
while (SQLCODE == 0)
{
EXEC SQL
FETCH emp USING SQL DESCRIPTOR sqlda;
if (SQLCODE == 100)
break;
/*
* Get the department name, using a static SQL statement.
*/
EXEC SQL
SELECT TRANSACTION trans1 department
INTO :department
FROM department
WHERE dept_no = :dept_no;
/*
* If the job country is not USA, access the second database
* in order to get the conversion rate between different money
* types. Even though the conversion rate may fluctuate, all
* salaries will be presented in US dollars for relative comparison.
*/
job_country.vary_string[job_country.vary_length] = '\0';
if (strcmp(job_country.vary_string, "USA"))
{
EXEC SQL
SELECT TRANSACTION trans1 currency
INTO :currency
FROM country
WHERE country = :job_country.vary_string :job_country.vary_length;
if (isc_start_transaction(status, &trans2, 1, &db2, 0, NULL))
isc_print_status(status);
EXEC SQL
SELECT TRANSACTION trans2 conv_rate
INTO :rate
FROM cross_rate
WHERE from_currency = 'Dollar'
AND to_currency = :currency;
if (!SQLCODE)
salary = salary / rate;
if (isc_commit_transaction (status, &trans2))
isc_print_status(status);
}
/*
* Print the results.
*/
printf("%-20.*s ", full_name.vary_length, full_name.vary_string);
fflush (stdout);
printf("%8.2f ", salary);
fflush (stdout);
printf(" %-5.*s %d", job_code.vary_length, job_code.vary_string, job_grade);
fflush (stdout);
printf(" %-20s\n", department);
fflush (stdout);
}
EXEC SQL
CLOSE emp;
if (isc_commit_transaction (status, &trans1))
isc_print_status(status);
isc_detach_database(status, &db1);
isc_detach_database(status, &db2);
return(0);
Error:
printf("\n");
isc_print_sqlerror(SQLCODE, isc_status);
return(1);
}
