C程序样例用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;
}

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

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

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 = "";
        }
    }
}

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
    }
}

-- 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()

#!/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()