型号: PXF2640
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()