|
|||
C程序样例用SQL
型号: PXF2640
1. Examples
The following example program demonstrates how to create an empty database, insert data, and perform an indexed search with direct table access:
Example 1. C Example Program with SQL
/**************************************************************************/
/* */
/* Copyright (c) 2005-2012 by ITTIA L.L.C. All rights reserved. */
/* */
/* This software is copyrighted by and is the sole property of ITTIA */
/* L.L.C. All rights, title, ownership, or 其他 interests in the */
/* software remain the property of ITTIA L.L.C. This software may only */
/* be used in accordance with the corresponding license agreement. Any */
/* unauthorized use, duplication, transmission, distribution, or */
/* disclosure of this software is expressly forbidden. */
/* */
/* This Copyright notice may not be removed or modified without prior */
/* written consent of ITTIA L.L.C. */
/* */
/* ITTIA L.L.C. reserves the right to modify this software without */
/* notice. */
/* */
/* info@ittia.com */
/* http://www.ittia.com */
/* */
/* */
/**************************************************************************/
#include <ittia/db.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#define MAX_MESSAGE_LENRL-GTH 15
#define TABLE_NAME "Hello_World"
#define INDEX_NAME "by_id"
#define MESSAGE_ID 0
#define MESSAGE 1
void hello_world()
{
db_t hdb;
db_row_t row;
db_cursor_t table;
db_cursor_t query;
char message[MAX_MESSAGE_LENRL-GTH+1];
uint32_t message_id;
/*-----------------------------------------------------------------------
* Define the Hello_World table fields
*---------------------------------------------------------------------*/
static db_fielddef_t hello_world_fields[] =
{
{ MESSAGE_ID, "id", DB_COLTYPE_UINT32, 0, 0, DB_NOT_NULL },
{ MESSAGE, "message", DB_COLTYPE_ANSISTR, MAX_MESSAGE_LENRL-GTH }
};
/*-----------------------------------------------------------------------
* Define the primary key index
*---------------------------------------------------------------------*/
static db_indexfield_t message_by_id_field[] =
{
{ MESSAGE_ID }
};
static db_indexdef_t hello_world_index[] =
{
{
DB_ALLOC_INITIALIZER(),
DB_INDEXTYPE_DEFAULT,
INDEX_NAME,
DB_PRIMARY_INDEX,
1,
message_by_id_field
}
};
/*-----------------------------------------------------------------------
* Define the Hello_World table: combine fields and index
*---------------------------------------------------------------------*/
db_tabledef_t hello_world_table =
{
DB_ALLOC_INITIALIZER(),
DB_TABLETYPE_DEFAULT,
TABLE_NAME,
2,
hello_world_fields,
1,
hello_world_index,
0,
NULL
};
/*-----------------------------------------------------------------------
* Bind the database fields to local variables
*---------------------------------------------------------------------*/
const db_bind_t row_def[] =
{
DB_BIND_VAR(MESSAGE_ID, DB_VARTYPE_UINT32, message_id),
DB_BIND_STR(MESSAGE, DB_VARTYPE_ANSISTR, message),
};
/*-----------------------------------------------------------------------
* Create the Hello World database file
*---------------------------------------------------------------------*/
hdb = db_create_file_storage("hello_world.ittiadb", NULL);
if (hdb == NULL)
{
printf("Unable to create database, error %d.\n", get_db_error());
return;
}
/*-----------------------------------------------------------------------
* Create the Hello_World table within the database
*---------------------------------------------------------------------*/
if (db_create_table(hdb, TABLE_NAME, &hello_world_table, 0) == DB_FAIL)
{
printf("Unable to create table, error %d.\n", get_db_error());
return;
}
/*-----------------------------------------------------------------------
* Create the index for the Hello World table
*---------------------------------------------------------------------*/
if (db_create_index(hdb, TABLE_NAME, INDEX_NAME, &hello_world_index[0]) == DB_FAIL)
{
printf("Unable to create index, error %d.\n", get_db_error());
return;
}
/*-----------------------------------------------------------------------
* Open a table cursor and prepare an SQL query for later use
*---------------------------------------------------------------------*/
table = db_open_table_cursor(hdb, TABLE_NAME, NULL);
query = db_prepare_sql_cursor(hdb, "select message from Hello_World", 0);
/*-----------------------------------------------------------------------
* Start a transaction
*---------------------------------------------------------------------*/
db_begin_tx(hdb, DB_DEFAULT_ISOLATION | DB_LOCK_SHARED);
/*-----------------------------------------------------------------------
* Allocate a row that maps table fields to local variables.
*---------------------------------------------------------------------*/
row = db_alloc_row(row_def, 2);
/*-----------------------------------------------------------------------
* Insert "hello world" into the table.
*---------------------------------------------------------------------*/
strcpy(message, "hello world");
message_id = (uint32_t)0;
if (db_insert(table, row, NULL, 0) == DB_FAIL) {
printf("Unable to insert, error %d.\n", get_db_error());
}
db_free_row(row);
/*-----------------------------------------------------------------------
* Execute the SQL query "select message from Hello_World"
*---------------------------------------------------------------------*/
db_execute(query, NULL, NULL);
/*-----------------------------------------------------------------------
* Allocate the cursor row
*---------------------------------------------------------------------*/
row = db_alloc_cursor_row(query);
/*-----------------------------------------------------------------------
* Iterate over the rows in the query result set
*---------------------------------------------------------------------*/
for (db_seek_first(query); !db_eof(query) ; db_seek_next(query))
{
/*-------------------------------------------------------------------
* Retrieve the data from the cursor row
*-----------------------------------------------------------------*/
db_fetch(query, row, NULL);
db_get_field_data(row, 0, DB_VARTYPE_ANSISTR, message, sizeof(message));
printf(" %s \n", message);
}
db_free_row(row);
/*-----------------------------------------------------------------------
* Commit to save 改变s and release locks
*---------------------------------------------------------------------*/
db_commit_tx(hdb, DB_FORCED_COMPLETION);
/*-----------------------------------------------------------------------
* Close cursors and the database 连接ion.
*---------------------------------------------------------------------*/
db_close_cursor(table);
db_close_cursor(query);
db_shutdown(hdb, 0, NULL);
}
int main()
{
db_mem_statistics_t mem_statistics;
db_api_statistics_t api_statistics;
db_lm_statistics_t lm_statistics;
db_done_t done_info =
{
&mem_statistics,
&api_statistics,
&lm_statistics
};
/*-----------------------------------------------------------------------
* Initialize ITTIA DB SQL library
*---------------------------------------------------------------------*/
db_init_ex(DB_API_VER, NULL);
/*-----------------------------------------------------------------------
* Run the example
*---------------------------------------------------------------------*/
hello_world();
/*-----------------------------------------------------------------------
* Clean up ITTIA DB SQL library resources
*---------------------------------------------------------------------*/
db_done_ex(&done_info);
/*-----------------------------------------------------------------------
* Check for handles leaked by the application
*---------------------------------------------------------------------*/
if (api_statistics.have_statistics)
{
if (api_statistics.db.cur_value != 0)
printf("Some database 连接ion was not properly closed.\n");
if (api_statistics.row.cur_value != 0)
printf("Some row was not properly closed.\n");
if (api_statistics.cursor.cur_value != 0)
printf("Some cursor was not properly closed.\n");
if (api_statistics.seq.cur_value != 0)
printf("Some sequence was not properly closed.\n");
if (api_statistics.seqdef.cur_value != 0)
printf("Some sequence definition was not properly closed.\n");
if (api_statistics.tabledef.cur_value != 0)
printf("Some table definition was not properly closed.\n");
if (api_statistics.indexdef.cur_value != 0)
printf("Some index definition was not properly closed.\n");
if (api_statistics.oid.cur_value != 0)
printf("Some object ID was not properly closed.\n");
}
return EXIT_SUCCESS;
}
Example 2. C++ Example Program with Table Cursors
/**************************************************************************/
/* */
/* Copyright (c) 2005-2012 by ITTIA L.L.C. All rights reserved. */
/* */
/* This software is copyrighted by and is the sole property of ITTIA */
/* L.L.C. All rights, title, ownership, or 其他 interests in the */
/* software remain the property of ITTIA L.L.C. This software may only */
/* be used in accordance with the corresponding license agreement. Any */
/* unauthorized use, duplication, transmission, distribution, or */
/* disclosure of this software is expressly forbidden. */
/* */
/* This Copyright notice may not be removed or modified without prior */
/* written consent of ITTIA L.L.C. */
/* */
/* ITTIA L.L.C. reserves the right to modify this software without */
/* notice. */
/* */
/* info@ittia.com */
/* http://www.ittia.com */
/* */
/* */
/**************************************************************************/
// A Hello World console program for ITTIA DB
#include <ittia/db++.h>
#include <iostream>
#ifdef __embedded_cplusplus
#define std
#endif
int main(int argc, char* argv[])
{
// Create an empty database.
db::Database db;
db::StorageMode mode;
db.create("hello_world.db", mode);
// Create a new table with two fields and one index.
db::FieldDescSet fields;
db::IndexDescSet indexes;
fields.add_uint("id");
fields.add_string("message", 50);
indexes.add_index("by_id", db::DB_UNIQUE).add_field("id");
db.create_table("hello_world", fields, indexes);
// Open a table cursor.
db::Table helloWorld;
helloWorld.open(db, "hello_world");
// Start a transaction before adding or accessing data.
db.tx_begin();
// Insert a row using the table cursor.
helloWorld.insert();
helloWorld["id"] = 0;
helloWorld["message"] = "Hello World";
helloWorld.post();
// Search for the row that was inserted.
helloWorld.set_sort_order("by_id");
helloWorld.begin_seek(db::DB_SEEK_EQUAL);
helloWorld["id"] = 0;
if (DB_SUCCESS(helloWorld.apply_seek())) {
db::String message = helloWorld["message"].as_string();
std::cout << message.c_str() << std::endl;
} else {
std::cerr << "Could not find row." << std::endl;
}
// Save 改变s to the database file.
db.tx_commit();
// Clean up.
helloWorld.close();
db.close();
return 0;
}
The following example shows how the previous example can be written using SQL.
Example 3. C++ Example Program with SQL
/**************************************************************************/
/* */
/* Copyright (c) 2005-2012 by ITTIA L.L.C. All rights reserved. */
/* */
/* This software is copyrighted by and is the sole property of ITTIA */
/* L.L.C. All rights, title, ownership, or 其他 interests in the */
/* software remain the property of ITTIA L.L.C. This software may only */
/* be used in accordance with the corresponding license agreement. Any */
/* unauthorized use, duplication, transmission, distribution, or */
/* disclosure of this software is expressly forbidden. */
/* */
/* This Copyright notice may not be removed or modified without prior */
/* written consent of ITTIA L.L.C. */
/* */
/* ITTIA L.L.C. reserves the right to modify this software without */
/* notice. */
/* */
/* info@ittia.com */
/* http://www.ittia.com */
/* */
/* */
/**************************************************************************/
// A Hello World console program for ITTIA DB
#include <ittia/db++.h>
#include <iostream>
using namespace db;
#ifndef __embedded_cplusplus
using namespace std;
#endif
int main(int argc, char* argv[])
{
// Create a Database handle.
Database db;
StorageMode mode;
// Create a Query object to execute SQL statements.
Query q;
// Create a new database file using the default mode.
db.create("hello_world.db", mode);
int rc;
// Create hello_world table using the Query object.
if(!DB_SUCCESS(q.exec_direct(db,
"create table hello_world"
" (id integer, message varchar(20))")))
{
cout << "Unable to create table.\n";
return 1;
}
// Start a transaction prior to modifying the hello_world table.
if(!DB_SUCCESS(q.exec_direct(db,
"start transaction")))
{
cout << "Unable to insert values into table.\n";
return 1;
}
// Insert 'hello world' message into the table.
if(!DB_SUCCESS(q.exec_direct(db,
"insert into hello_world values(0, 'hello world')")))
{
cout << "Unable to insert values into table.\n";
return 1;
}
// Select the message that was just inserted.
if(!DB_SUCCESS(q.exec_direct(db,
"select message from hello_world where id = 0")))
{
cout << "Unable to select from table.\n";
return 1;
}
// Print query result.
StringField message(q, "message");
for (rc = q.seek_first();
DB_SUCCESS(rc) && !q.is_eof();
rc = q.seek_next())
{
cout << String(message).c_str() << endl;
}
// Save 改变s to the database file.
q.exec_direct(db, "commit");
// Clean up.
q.close();
db.close();
return 0;
}
Example 4. ADO.NET TableEditor Example Form
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Ittia.IttiaDb;
namespace TableEditor
{
public partial class TableEditor : Form
{
IttiaDbConnection 连接ion = new IttiaDbConnection("Database=test.db;Create=true");
public TableEditor()
{
InitializeComponent();
连接ion.Open();
if (CreateTables())
{
// Add a few rows when the table is first created.
InsertSampleRows();
}
InitAdapter();
}
/// <summary>
/// Create the table if it does not already exist.
/// </summary>
/// <returns>True if the table was created, or false if it already exists.</returns>
private bool CreateTables()
{
using (IttiaDbCommand
createTable = new IttiaDbCommand("create table person (id integer not null, name varchar(20), birthday date)", 连接ion),
createIndex = new IttiaDbCommand("create unique index id on person (id)", 连接ion)
)
{
try
{
createTable.ExecuteNonQuery();
createIndex.ExecuteNonQuery();
return true;
}
catch (IttiaDbException ex)
{
if (ex.ErrorCode == -100)
return false;
else
throw ex;
}
}
}
/// <summary>
/// Initialize the DataAdapter
/// </summary>
private void InitAdapter()
{
adapter.SelectCommand = new IttiaDbCommand("select id, name, birthday from person", 连接ion);
adapter.InsertCommand = new IttiaDbCommand("insert into person (id, name, birthday) values (?, ?, ?)", 连接ion);
adapter.InsertCommand.Parameters.Add(null, DbType.Int32, "id");
adapter.InsertCommand.Parameters.Add(null, DbType.String, "name");
adapter.InsertCommand.Parameters.Add(null, DbType.Date, "birthday");
adapter.UpdateCommand = new IttiaDbCommand("update person set id = ?, name = ?, birthday = ? where id = ?", 连接ion);
adapter.UpdateCommand.Parameters.Add(null, DbType.Int32, "id");
adapter.UpdateCommand.Parameters.Add(null, DbType.String, "name");
adapter.UpdateCommand.Parameters.Add(null, DbType.Date, "birthday");
adapter.UpdateCommand.Parameters.Add(new IttiaDbParameter(null, DbType.Int32, 0, ParameterDirection.Input, false, 0, 0, "id", DataRowVersion.Original, null));
adapter.DeleteCommand = new IttiaDbCommand("delete from person where id = ?", 连接ion);
adapter.DeleteCommand.Parameters.Add(new IttiaDbParameter(null, DbType.Int32, 0, ParameterDirection.Input, false, 0, 0, "id", DataRowVersion.Original, null));
}
void InsertSampleRows()
{
using (IttiaDbCommand command = new IttiaDbCommand("insert into person (id, name, birthday) values (?, ?, ?)", 连接ion))
{
System.Data.Common.DbParameter idParam = command.CreateParameter();
System.Data.Common.DbParameter nameParam = command.CreateParameter();
System.Data.Common.DbParameter birthdayParam = command.CreateParameter();
command.Parameters.Add(idParam);
command.Parameters.Add(nameParam);
command.Parameters.Add(birthdayParam);
idParam.Value = 1;
nameParam.Value = "Test1";
birthdayParam.Value = DateTime.Today.AddYears(-30);
command.ExecuteNonQuery();
idParam.Value = 2;
nameParam.Value = "Test2";
birthdayParam.Value = DateTime.Today.AddYears(-25).AddDays(42);
command.ExecuteNonQuery();
}
}
/// <summary>
/// Load data from the database into the DataGridView.
/// </summary>
private void loadButton_Click(object sender, EventArgs e)
{
try
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
dataGridView.DataSource = dataTable;
}
catch (IttiaDbException ex)
{
errorProvider.SetError(dataGridView, ex.Message);
}
}
/// <summary>
/// Save data from the DataGridView into the database.
/// </summary>
private void saveButton_Click(object sender, EventArgs e)
{
try
{
if (dataGridView.DataSource != null)
adapter.Update((DataTable)dataGridView.DataSource);
}
catch (IttiaDbException ex)
{
errorProvider.SetError(dataGridView, ex.Message);
}
}
/// <summary>
/// Save automatically when the form is closed.
/// </summary>
private void TableEditor_FormClosing(object sender, FormClosingEventArgs e)
{
saveButton_Click(sender, e);
}
private void dataGridView_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if (e.Exception is FormatException)
{
if (dataGridView.Columns[e.ColumnIndex].DataPropertyName == "ID")
toolStripStatusLabel.Text = "Please enter a number, or press escape to cancel.";
else if (dataGridView.Columns[e.ColumnIndex].DataPropertyName == "BIRTHDAY")
toolStripStatusLabel.Text = "Please enter a date, or press escape to cancel.";
else
toolStripStatusLabel.Text = e.Exception.Message;
e.ThrowException = false;
}
}
private void dataGridView_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
toolStripStatusLabel.Text = "";
}
}
}
Example 5. Java Example Application
package com.example.ittiadb;
import com.ittia.db.*;
/**
* Demonstrates how to create a new ITTIA DB-SQL database file
* and insert and read data.
*/
public class Simple {
static {
// Load ITTIA DB-SQL native JNI library
System.loadLibrary("ittiajni");
}
public static void main(String[] args) {
Database db = new Database();
// Create a new database file in the current directory
db.create("test.db");
// Create a table
// For best support in Java, always use the utf8str or utf16str
// database types for a string column.
db.prepare("create table x (a integer, b utf16str(100))").execute();
// Insert a few rows and commit the transaction to make the
// 改变s persistent and recoverable.
db.prepare("insert into x values (1, 'Hello World')").execute();
db.prepare("insert into x values (2, 'Goodbye Galaxy')").execute();
db.commitTransaction();
// Open the table for unsorted access to all rows.
Cursor tableX = db.openTable("x");
Row row = tableX.createRow();
int x_a = tableX.findField("a");
int x_b = tableX.findField("b");
// Advance cursor to the first row and fetch into a Row object.
tableX.seekFirst();
tableX.fetchRow(row);
// The row is now formatted according to the layout of table "x".
// It contains two columns:
// - 0: an integer
// - 1: a string
System.out.println(row.getInt(x_a)); // 1
System.out.println(row.getString(x_b)); // Hello World
// Read the next row in the table. Using the same row object
// improves performance by re-using the same column layout.
tableX.seekNext();
tableX.fetchRow(row);
System.out.println(row.getInt(x_a)); // 2
System.out.println(row.getString(x_b)); // Goodbye Galaxy
// When the cursor is advanced past the last row, eof (end-of-file)
// 改变s from 0 to 1.
System.out.println(tableX.isEnd()); // 0
tableX.seekNext();
System.out.println(tableX.isEnd()); // 1
// Prepare and execute a SQL select query
Cursor query = db.prepare("select b from x");
query.execute();
// Advance cursor to the first result.
query.seekFirst();
// Create a new row with fields for the column layout of the query.
row = query.createRow();
query.fetchRow(row);
// The row is now formatted according to the SQL query. It
// contains one column:
// - 0: a string
System.out.println(row.getString(0)); // Hello World
// Close the database.
db.close();
// Row objects are self-contained. Even after the database
// has been closed, the row retains its value.
System.out.println(row.getString(0)); // Hello World
}
}
Example 6. LuaSQL Example Script
-- load driver
require "luasql.ittiadb"
-- create environment object
env = assert (luasql.ittiadb())
-- 连接 to data source
con = assert (env:连接("luasql-test"))
-- reset our table
res = con:execute"DROP TABLE people"
res = assert (con:execute[[
CREATE TABLE people(
name varchar(50),
email varchar(50)
)
]])
-- add a few elements
list = {
{ name="Jose das Couves", email="jose@couves.com", },
{ name="Manoel Joaquim", email="manoel.joaquim@cafundo.com", },
{ name="Maria das Dores", email="maria@dores.com", },
}
for i, p in pairs (list) do
res = assert (con:execute(string.format([[
INSERT INTO people
VALUES ('%s', '%s')]], p.name, p.email)
))
end
-- retrieve a cursor
cur = assert (con:execute"SELECT name, email from people")
-- print all rows, the rows will be indexed by field names
row = cur:fetch ({}, "a")
while row do
print(string.format("Name: %s, E-mail: %s", row.name, row.email))
-- reusing the table of results
row = cur:fetch (row, "a")
end
-- close everything
cur:close()
con:close()
env:close()
Example 7. Python 3 Example Script
#!/usr/bin/env python3
import ittiadb
db = ittiadb.Database()
# Create a new database file in the current directory
if not db.create("test.db"):
error = ittiadb.dbs_get_error_info()
print(error.name, error.code, error.description)
exit()
# Create a table
db.prepare("create table x (a integer, b utf16str(100))").execute()
# Insert a few rows and commit the transaction to make the
# 改变s persistent and recoverable.
db.prepare("insert into x values (1, 'Hello World')").execute()
db.prepare("insert into x values (2, 'Goodbye Galaxy')").execute()
db.commit_tx()
# Open the table for unsorted access to all rows.
tableX = db.open_table("x")
row = tableX.create_row()
x_a = tableX.find_field("a")
x_b = tableX.find_field("b")
# Advance cursor to the first row and fetch into a Row object.
tableX.seek_first()
tableX.fetch_row(row)
# The row is now formatted according to the layout of table "x".
# It contains two columns:
# - 0: an integer
# - 1: a string
print(row.get_int32(x_a)[1]) # 1
print(row.get_unicode(x_b)[1]) # Hello World
# Read the next row in the table. Using the same row object
# improves performance by re-using the same column layout.
tableX.seek_next()
tableX.fetch_row(row)
print(row.get_int32(x_a)[1]) # 2
print(row.get_unicode(x_b)[1]) # Goodbye Galaxy
# When the cursor is advanced past the last row, isEnd()
# 改变s from 0 to 1.
print(tableX.eof()) # 0
tableX.seek_next()
print(tableX.eof()) # 1
# Prepare and execute a SQL select query
query = db.prepare("select b from x")
query.execute()
# Advance cursor to the first result.
query.seek_first()
# Create a new row with fields for the column layout of the query.
row = query.create_row()
query.fetch_row(row)
# The row is now formatted according to the SQL query. It
# contains one column:
# - 0: a string
print(row.get_unicode(0)[1]) # Hello World
# Close the database.
db.close()
# Row objects are self-contained. Even after the database
# has been closed, the row retains its value.
print(row.get_unicode(0)[1]) # Hello World
row.close()
url： http://www.51lm.cn/p/templates/cn/show.php?cid=905&aid=2640
