예제 #1
0
파일: select.cpp 프로젝트: fyatao/firebird
int main()
{
	int rc = 0;

	// set default password if none specified in environment
	setenv("ISC_USER", "sysdba", 0);
	setenv("ISC_PASSWORD", "masterkey", 0);

	// declare pointers to required interfaces
	IStatus* st = NULL;
	IProvider* prov = NULL;
	IAttachment* att = NULL;
	ITransaction* tra = NULL;
	IStatement* stmt = NULL;
	IResultSet* curs = NULL;
	IMessageMetadata* meta = NULL;
	IMetadataBuilder* builder = NULL;

	try
	{
		// status vector and main dispatcher
		st = master->getStatus();
		prov = master->getDispatcher();

		// attach employee db
		att = prov->attachDatabase(st, "localhost:employee", 0, NULL);
		check(st, "attachDatabase");

		// start default transaction
		tra = att->startTransaction(st, 0, NULL);
		check(st, "startTransaction");

		// prepare statement
		stmt = att->prepare(st, tra, 0, "select * from country", 3,
			IStatement::PREPARE_PREFETCH_METADATA);
		check(st, "prepare");

		// get list of columns
		meta = stmt->getOutputMetadata(st);
		check(st, "getOutputMetadata");
		builder = meta->getBuilder(st);
		check(st, "getBuilder");
		unsigned cols = meta->getCount(st);
		check(st, "getCount");

		// struct to cache received metadata
		struct MyField
		{
			const char* name;
			unsigned length, offset;
		};
		MyField* fields = new MyField[cols];
		memset(fields, 0, sizeof(MyField) * cols);

		// parse columns list & coerce datatype(s)
		for (unsigned j = 0; j < cols; ++j)
		{
			unsigned t = meta->getType(st, j);
			check(st, "getType");

			if (t == SQL_VARYING || t == SQL_TEXT)
			{
				builder->setType(st, j, SQL_TEXT);
				check(st, "setType");
				fields[j].name = meta->getField(st, j);
				check(st, "getField");
			}
		}

		meta->release();
		meta = builder->getMetadata(st);
		check(st, "getMetadata");

		// now we may also get offsets info
		for (unsigned j = 0; j < cols; ++j)
		{
			if (fields[j].name)
			{
				fields[j].length = meta->getLength(st, j);
				check(st, "getLength");
				fields[j].offset = meta->getOffset(st, j);
				check(st, "getOffset");
			}
		}

		builder->release();
		builder = NULL;

		// open cursor
		curs = stmt->openCursor(st, tra, NULL, NULL, meta);
		check(st, "openCursor");

		// allocate output buffer
		unsigned l = meta->getMessageLength(st);
		check(st, "getMessageLength");
		unsigned char* buffer = new unsigned char[l];

		// fetch records from cursor and print them
		while (curs->fetch(st, buffer))
		{
			for (unsigned j = 0; j < cols; ++j)
			{
				if (fields[j].name)
				{
					printf("%s: %*.*s\n", fields[j].name, fields[j].length, fields[j].length,
						buffer + fields[j].offset);
				}
			}
			printf("\n");
		}
		check(st, "fetch");

		// close interfaces
		curs->close(st);
		check(st, "close");
		curs = NULL;

		stmt->free(st);
		check(st, "free");
		stmt = NULL;

		meta->release();
		meta = NULL;

		tra->commit(st);
		check(st, "commit");
		tra = NULL;

		att->detach(st);
		check(st, "detach");
		att = NULL;
	}
	catch(const char* text)
	{
		// handle error
		rc = 1;
		fprintf(stderr, "%s:\n", text);
		if (st)
			isc_print_status(st->get());
	}

	// release interfaces after error caught
	if (meta)
		meta->release();
	if (builder)
		builder->release();
	if (curs)
		curs->release();
	if (stmt)
		stmt->release();
	if (tra)
		tra->release();
	if (att)
		att->release();
	if (prov)
		prov->release();
	if (st)
		st->dispose();

	return rc;
}
예제 #2
0
int main()
{
	int rc = 0;

	// set default password if none specified in environment
	setenv("ISC_USER", "sysdba", 0);
	setenv("ISC_PASSWORD", "masterkey", 0);

	// status vector and main dispatcher
	ThrowStatusWrapper status(master->getStatus());
	IProvider* prov = master->getDispatcher();

	// declare pointers to required interfaces
	IAttachment* att = NULL;
	ITransaction* tra = NULL;

	// Interface executes prepared SQL statement
	IStatement* stmt = NULL;

	// Interfaces provides access to format of data in messages
	IMessageMetadata* meta = NULL;

	// Interface makes it possible to change format of data or define it yourself
	IMetadataBuilder* builder = NULL;

	const char* updstr =
	    "UPDATE department SET budget = ? * budget + budget WHERE dept_no = ?";

	try
	{
		// attach employee db
		att = prov->attachDatabase(&status, "employee", 0, NULL);

		// start transaction
		tra = att->startTransaction(&status, 0, NULL);

		// prepare statement
		stmt = att->prepare(&status, tra, 0, updstr, SAMPLES_DIALECT, 0);

		// build metadata
		// IMaster creates empty new metadata in builder
		builder = master->getMetadataBuilder(&status, 2);
		// set required info on fields
		builder->setType(&status, 0, SQL_DOUBLE + 1);
		builder->setType(&status, 1, SQL_TEXT + 1);
		builder->setLength(&status, 1, 3);
		// IMetadata should be ready
		meta = builder->getMetadata(&status);
		// no need in builder any more
		builder->release();
		builder = NULL;

		// allocate buffer on stack
		char buffer[256];
		unsigned len = meta->getMessageLength(&status);
		if (len > sizeof(buffer))
		{
			throw "Input message length too big - can't continue";
		}

		// locations of parameters in input message
		char* dept_no = &buffer[meta->getOffset(&status, 1)];
 		double* percent_inc = (double*) &buffer[meta->getOffset(&status, 0)];

		// null IDs (set to NOT NULL)
 		short* flag = (short*)&buffer[meta->getNullOffset(&status, 0)];
 		*flag = 0;
 		flag = (short*) &buffer[meta->getNullOffset(&status, 1)];
 		*flag = 0;

		// Get the next department-percent increase input pair.
	    while (get_input(dept_no, percent_inc))
    	{
	        printf("\nIncreasing budget for department:  %s  by %5.2lf percent.\n",
    	           dept_no, *percent_inc);

			// Update the budget.
			try
			{
			    stmt->execute(&status, tra, meta, buffer, NULL, NULL);
			}
			catch (const FbException& error)
			{
				// Handle exception raised during statement execution
				if (error.getStatus()->getErrors()[1] == isc_not_valid)
				{
					// Don't save the update, if the new budget exceeds the limit.
					printf("\tExceeded budget limit -- not updated.\n");

					tra->rollbackRetaining(&status);
					continue;
				}

				// Another error - use default handler
				throw;
			}

			// Save each department's update independently.
			// *** Change to commitRetaining() to see changes
			// *** tra->commitRetaining(&status);
			tra->rollbackRetaining(&status);
        }

		// close interfaces
		stmt->free(&status);
		stmt = NULL;

		meta->release();
		meta = NULL;

		tra->commit(&status);
		tra = NULL;

		att->detach(&status);
		att = NULL;
	}
	catch (const FbException& error)
	{
		// handle error
		rc = 1;

		char buf[256];
		master->getUtilInterface()->formatStatus(buf, sizeof(buf), error.getStatus());
		fprintf(stderr, "%s\n", buf);
	}

	// release interfaces after error caught
	if (builder)
		builder->release();
	if (meta)
		meta->release();
	if (stmt)
		stmt->release();
	if (tra)
		tra->release();
	if (att)
		att->release();

	prov->release();
	status.dispose();

	return rc;
}
예제 #3
0
int main()
{
	int rc = 0;

	setenv("ISC_USER", "sysdba", 0);
	setenv("ISC_PASSWORD", "masterkey", 0);

	ThrowStatusWrapper status(master->getStatus());
	IProvider* prov = master->getDispatcher();

	IAttachment* att = NULL;
	ITransaction* tra = NULL;
	IResultSet* rs = NULL;

	const char* dbName = "employee";

	try
	{
		att = prov->attachDatabase(&status, dbName, 0, NULL);
		tra = att->startTransaction(&status, 0, NULL);

		// Comment some tables
		att->execute(&status, tra, 0, "comment on table employee is 'Employees'",
			SAMPLES_DIALECT, NULL, NULL, NULL, NULL);
		att->execute(&status, tra, 0, "comment on table customer is 'Customers'",
			SAMPLES_DIALECT, NULL, NULL, NULL, NULL);
		att->execute(&status, tra, 0, "comment on table country is 'Countries and national currencies'",
			SAMPLES_DIALECT, NULL, NULL, NULL, NULL);
		tra->commitRetaining(&status);

		// Print tables list
		FB_MESSAGE(Input, ThrowStatusWrapper,
			(FB_INTEGER, systemFlag)
		) input(&status, master);

		FB_MESSAGE(Output, ThrowStatusWrapper,
			(FB_SMALLINT, relationId)
			(FB_VARCHAR(31), relationName)
			(FB_VARCHAR(100), description)
		) output(&status, master);

		input.clear();
		input->systemFlag = 0;

		rs = att->openCursor(&status, tra, 0,
			"select rdb$relation_id, rdb$relation_name, rdb$description"
			"  from rdb$relations"
			"  where rdb$system_flag = ?"
			"  order by rdb$relation_id",
			SAMPLES_DIALECT, input.getMetadata(), input.getData(), output.getMetadata(), NULL, 0);

		printf("  ID Name/comment\n");
		while (rs->fetchNext(&status, output.getData()) == IStatus::RESULT_OK)
		{
			unsigned lRelName = output->relationNameNull ? 0 : output->relationName.length;
			unsigned lDesc = output->descriptionNull ? 0 : output->description.length;
			printf("%4d %*.*s%c%*.*s\n", output->relationId,
				lRelName, lRelName, output->relationName.str,
				lDesc ? '/' : ' ',
				lDesc, lDesc, output->description.str);
		}

		rs->close(&status);
		rs = NULL;

		tra->commit(&status);
		tra = NULL;

		att->detach(&status);
		att = NULL;
	}
	catch (const FbException& error)
	{
		// handle error
		rc = 1;

		char buf[256];
		master->getUtilInterface()->formatStatus(buf, sizeof(buf), error.getStatus());
		fprintf(stderr, "%s\n", buf);
	}

	// release interfaces after error caught
	if (rs)
		rs->release();
	if (tra)
		tra->release();
	if (att)
		att->release();

	// generic cleanup
	prov->release();
	status.dispose();
}
예제 #4
0
int main()
{
	int rc = 0;

	// set default password if none specified in environment
	setenv("ISC_USER", "sysdba", 0);
	setenv("ISC_PASSWORD", "masterkey", 0);

	// Declare pointers to required interfaces
	// IStatus is used to return wide error description to user
	IStatus* st = NULL;

	// IProvider is needed to start to work with database (or service)
	IProvider* prov = NULL;

	// IAttachment and ITransaction contain methods to work with database attachment
	// and transactions
	IAttachment* att = NULL;
	ITransaction* tra = NULL;

	try
	{
		// status vector and main dispatcher are returned by calls to IMaster functions
		// no error return may happen - these functions always succeed
		st = master->getStatus();
		prov = master->getDispatcher();

		// status wrapper - will be used later in all calls where status interface is needed
		// With ThrowStatusWrapper passed as status interface FbException will be thrown on error
		ThrowStatusWrapper status(st);

		// create DPB (to be replaced with IPBWriter)
		unsigned char dpbBuf[32];
		unsigned char *dpb = dpbBuf;
		*dpb++ = isc_dpb_version1;
		*dpb++ = isc_dpb_page_size;
		*dpb++ = 2;
		*dpb++ = (8 * 1024) & 0xFF;
		*dpb++ = (8 * 1024) >> 8;

		// create empty database
		att = prov->createDatabase(&status, "fbtests.fdb", dpb - dpbBuf, dpbBuf);
		printf("Database fbtests.fdb created\n");

		// detach from database
		att->detach(&status);
		att = NULL;

		// attach it once again
		att = prov->attachDatabase(&status, "fbtests.fdb", 0, NULL);
		printf("Re-attached database fbtests.fdb\n");

		// start transaction
		tra = att->startTransaction(&status, 0, NULL);

		// create table
		att->execute(&status, tra, 0, "create table dates_table (d1 date)", 3,
			NULL, NULL, NULL, NULL);	// Input parameters and output data not used

		// commit transaction retaining
		tra->commitRetaining(&status);
		printf("Table dates_table created\n");

		// insert a record into dates_table
		att->execute(&status, tra, 0, "insert into dates_table values (CURRENT_DATE)", 3,
			NULL, NULL, NULL, NULL);	// Input parameters and output data not used

		// commit transaction (will close interface)
		tra->commit(&status);
		tra = NULL;

		printf("Record inserted into dates_table\n");

		// detach from database (will close interface)
		att->detach(&status);
		att = NULL;
	}
	catch (const FbException& error)
	{
		// handle error
		rc = 1;
		isc_print_status(error.getStatus()->getErrors());
	}

	// release interfaces after error caught
	if (tra)
		tra->release();
	if (att)
		att->release();
	if (prov)
		prov->release();
	if (st)
		st->dispose();

	return rc;
}
예제 #5
0
int main()
{
	int rc = 0;

	// set default password if none specified in environment
	setenv("ISC_USER", "sysdba", 0);
	setenv("ISC_PASSWORD", "masterkey", 0);

	// declare pointers to required interfaces
	IStatus* st = NULL;
	IProvider* prov = NULL;
	IAttachment* att = NULL;
	ITransaction* tra = NULL;

	// Interface executes prepared SQL statement
	IStatement* stmt = NULL;

	// Interfaces provides access to format of data in messages
	IMessageMetadata* meta = NULL;

	// Interface makes it possible to change format of data or define it yourself
	IMetadataBuilder* builder = NULL;

	const char* updstr =
	    "UPDATE department SET budget = ? * budget + budget WHERE dept_no = ?";

	try
	{
		// status vector and main dispatcher
		st = master->getStatus();
		prov = master->getDispatcher();

		// attach employee db
		att = prov->attachDatabase(st, "employee", 0, NULL);
		check(st, "attachDatabase");

		// start transaction
		tra = att->startTransaction(st, 0, NULL);
		check(st, "startTransaction");

		// prepare statement
		stmt = att->prepare(st, tra, 0, updstr, 3, 0);
		check(st, "prepare");

		// build metadata
		// IMaster creates empty new metadata in builder
		builder = master->getMetadataBuilder(st, 2);
		check(st, "getMetadataBuilder");
		// set required info on fields
		builder->setType(st, 0, SQL_DOUBLE + 1);
		check(st, "setType");
		builder->setType(st, 1, SQL_TEXT + 1);
		check(st, "setType");
		builder->setLength(st, 1, 3);
		check(st, "setLength");
		// IMetadata should be ready
		meta = builder->getMetadata(st);
		check(st, "getMetadata");
		// no need in builder any more
		builder->release();
		builder = NULL;

		// allocate buffer
		char buffer[256];
		unsigned len = meta->getMessageLength(st);
		check(st, "getMessageLength");
		if (len > 256)
		{
			throw "Input message length too big - can't continue";
		}

		// locations of parameters in input message
		char* dept_no = &buffer[meta->getOffset(st, 1)];
		check(st, "getOffset");
 		double* percent_inc = (double*) &buffer[meta->getOffset(st, 0)];
		check(st, "getOffset");

		// null IDs (set to NOT NULL)
 		short* flag = (short*)&buffer[meta->getNullOffset(st, 0)];
		check(st, "getNullOffset");
 		*flag = 0;
 		flag = (short*) &buffer[meta->getNullOffset(st, 1)];
		check(st, "getNullOffset");
 		*flag = 0;

		// Get the next department-percent increase input pair.
	    while (get_input(dept_no, percent_inc))
    	{
	        printf("\nIncreasing budget for department:  %s  by %5.2lf percent.\n",
    	           dept_no, *percent_inc);

			// Update the budget.
	        stmt->execute(st, tra, meta, buffer, NULL, NULL);
	        if (st->getStatus() & IStatus::FB_HAS_ERRORS)
    	    {
		        int sqlcode = isc_sqlcode(st->getErrors());
    	        // Don't save the update, if the new budget exceeds the limit.
        	    if (sqlcode == -625)
            	{
	                printf("\tExceeded budget limit -- not updated.\n");

    	            tra->rollbackRetaining(st);
    	            check(st, "rollback");
	                continue;
    	        }

    	        // use default handler
    	        check(st, "execute");
            }

			// Save each department's update independently.
			// *** Change to commitRetaining() to see changes
			// *** tra->commitRetaining(st);
			tra->rollbackRetaining(st);
			check(st, "rollback");
        }

		// close interfaces
		stmt->free(st);
		check(st, "free");
		stmt = NULL;

		meta->release();
		meta = NULL;

		tra->commit(st);
		check(st, "commit");
		tra = NULL;

		att->detach(st);
		check(st, "detach");
		att = NULL;
	}
	catch (const char* text)
	{
		// handle error
		rc = 1;
		fprintf(stderr, "%s:\n", text);
		if (st)
			isc_print_status(st->getErrors());
	}

	// release interfaces after error caught
	if (builder)
		builder->release();
	if (meta)
		meta->release();
	if (stmt)
		stmt->release();
	if (tra)
		tra->release();
	if (att)
		att->release();
	if (prov)
		prov->release();
	if (st)
		st->dispose();

	return rc;
}
예제 #6
0
int main()
{
	int rc = 0;

	// set default password if none specified in environment
	setenv("ISC_USER", "sysdba", 0);
	setenv("ISC_PASSWORD", "masterkey", 0);

	// status vector and main dispatcher
	ThrowStatusWrapper status(master->getStatus());
	IProvider* prov = master->getDispatcher();
	IUtil* utl = master->getUtilInterface();

	// declare pointers to required interfaces
	IAttachment* att = NULL;
	ITransaction* tra = NULL;
	IStatement* stmt = NULL;
	IMessageMetadata* meta = NULL;
	IMetadataBuilder* builder = NULL;
	IXpbBuilder* tpb = NULL;

	// Interface provides access to data returned by SELECT statement
	IResultSet* curs = NULL;

	try
	{
		// attach employee db
		att = prov->attachDatabase(&status, "employee", 0, NULL);

		// start read only transaction
		tpb = utl->getXpbBuilder(&status, IXpbBuilder::TPB, NULL, 0);
		tpb->insertTag(&status, isc_tpb_read_committed);
		tpb->insertTag(&status, isc_tpb_no_rec_version);
		tpb->insertTag(&status, isc_tpb_wait);
		tpb->insertTag(&status, isc_tpb_read);
		tra = att->startTransaction(&status, tpb->getBufferLength(&status), tpb->getBuffer(&status));

		// prepare statement
		stmt = att->prepare(&status, tra, 0, "select last_name, first_name, phone_ext from phone_list "
										"where location = 'Monterey' order by last_name, first_name",
			SAMPLES_DIALECT, IStatement::PREPARE_PREFETCH_METADATA);

		// get list of columns
		meta = stmt->getOutputMetadata(&status);
		builder = meta->getBuilder(&status);
		unsigned cols = meta->getCount(&status);

		// struct to cache received metadata
		struct MyField
		{
			const char* name;
			unsigned length, offset;
		};
		MyField* fields = new MyField[cols];
		memset(fields, 0, sizeof(MyField) * cols);

		// parse columns list & coerce datatype(s)
		for (unsigned j = 0; j < cols; ++j)
		{
			unsigned t = meta->getType(&status, j);

			if (t == SQL_VARYING || t == SQL_TEXT)
			{
				builder->setType(&status, j, SQL_TEXT);
				fields[j].name = meta->getField(&status, j);
			}
		}

		// release automatically created metadata
		// metadata is not database object, therefore no specific call to close it
		meta->release();

		// get metadata with coerced datatypes
		meta = builder->getMetadata(&status);

		// builder not needed any more
		builder->release();
		builder = NULL;

		// now we may also get offsets info
		for (unsigned j = 0; j < cols; ++j)
		{
			if (fields[j].name)
			{
				fields[j].length = meta->getLength(&status, j);
				fields[j].offset = meta->getOffset(&status, j);
			}
		}

		// open cursor
		curs = stmt->openCursor(&status, tra, NULL, NULL, meta, 0);

		// allocate output buffer
		unsigned l = meta->getMessageLength(&status);
		unsigned char* buffer = new unsigned char[l];

		// fetch records from cursor and print them
		for (int line = 0; curs->fetchNext(&status, buffer) == IStatus::RESULT_OK; ++line)
		{
			if (line % 10 == 0)
			{
				printf("\n");
				for (unsigned j = 0; j < cols; ++j)
				{
					if (fields[j].name)
					{
						printf("%-*.*s ", fields[j].length, fields[j].length, fields[j].name);
					}
				}
				printf("\n");
			}

			for (unsigned j = 0; j < cols; ++j)
			{
				if (fields[j].name)
				{
					printf("%*.*s ", fields[j].length, fields[j].length, buffer + fields[j].offset);
				}
			}
			printf("\n");
		}
		printf("\n");

		// close interfaces
		curs->close(&status);
		curs = NULL;

		stmt->free(&status);
		stmt = NULL;

		meta->release();
		meta = NULL;

		tra->commit(&status);
		tra = NULL;

		att->detach(&status);
		att = NULL;
	}
	catch (const FbException& error)
	{
		// handle error
		rc = 1;

		char buf[256];
		master->getUtilInterface()->formatStatus(buf, sizeof(buf), error.getStatus());
		fprintf(stderr, "%s\n", buf);
	}

	// release interfaces after error caught
	if (meta)
		meta->release();
	if (builder)
		builder->release();
	if (curs)
		curs->release();
	if (stmt)
		stmt->release();
	if (tra)
		tra->release();
	if (att)
		att->release();
	if (tpb)
		tpb->dispose();

	prov->release();
	status.dispose();

	return rc;
}