ZWinSql - Simple C++ Library for File-Based SQL Databases

Copyright (C) 2005-... Werner.Zimmermann|AT|hs-esslingen.de


Overview

This C++ library provides a thin wrapper to use D. R. Hipp's SQLite, which is a single file based SQL database. ZWinSql does not much more than allow you to create, modify and query a database, contained in a single, local file, using standard SQL commands.

ZWinSql can be used together with my GUI library ZWinLib and/or my XML wrapper library ZWinXml or it can be used stand alone.

SQLite is available for Windows and several other operating systems from www.SQLite.org. It comes with full source and extensive documentation. A binary version of the library (compiled for Visual C++) comes with ZWinSql (see subdirectory sqlite). ZWinSql users should not need to deal with SQLite directly. However, if more advanced processing than ZWinSql provides is needed, SQLite functions can be used directly bypassing ZwinSql.

The most recent version of ZWinSql can be downloaded from www.hs-esslingen.de/~zimmerma/software Please also have a look at ZWinSql's close friends ZWinXml for XML document handling, ZWinLib for graphical user interfaces and ZWinCrypt for compression, encryption and hashing files, also available on the same web site.


Installation

This library is distributed as a zip file. To install, unzip it into your desired installation directory. You'll get the following directory structure:

To run one of the samples, go to the samples subdirectory and run the respective Sample...exe file. To recompile one of the samples, in a console window change to the sample's directory and type 'nmake'. This assumes, that Visual C++ compiler, linker, resource editor and (n)make utility are available in the path of your console window.

If you need to recompile the library itself, in a console window change to the src directory and type 'nmake'.

To develop your own ZWinSql applications, create another subdirectory in the samples directory, copy the files from Sample21 or Sample22 into this new subdirectory and start modifying these files. (Don't forget to modify the makefile, too!)

If you ever want to deinstall ZWinSql again (why should you? :--)) ) simply delete the installation directory and all its subdirectories. ZWinSql does not copy files into any other directory nor does it touch the registry.


Getting started

The ZWinSql library provides the following user relevant classes:

Basically a program must do 3 things:

  1. Open the database file by calling
    			WSqlDb *pDb = new WSqlDb("name of the SQLite database file, e.g. test.db");
    		     
  2. Execute SQL queries, where the result goes into a WSqlResult matrix by calling
    		     WSqlResult *pR = new WSqlResult();
    		     pDb->ExecuteSQL("SQL statement, e.g. select * from table1", pR);
    		     
  3. Evaluate the result of the query, which is stored in rows and colums in the matrix pointed to by pR. E.g. to dump the complete matrix to the screen including header info for each column:
                         for (int i=0; i < pR->nColumns; i++)       //Print the column headers for all columns                      
                         {   printf("%16s", pR->GetHeader(i));      
                         }
                         printf("\n\n");
                         for (int j=0; j < pR->nRows; j++)          //For all rows ...                              
                         {   for (int i=0; i < pR->nColumns; i++)   //... and colums ...
                             {   printf("%16s", pR->GetValue(j, i));//... dump the matrix elements contents
                             }
                             printf("\n");
                         }
                         

After opening a database creating a WSqlDb object, with WSqlDb::GetTablesCount() and WSqlDb::GetTableName() you can find out, how many and which tables the database has. For a single table, with WSqlDb::GetTableColumnCount(), WSqlDb::GetTableColumnName() and WSqlDb::GetTableColumnType() you can find out, how many columns the table has, what the column names and associated data types are.

When you execute SQL commands with WSqlDb::ExecuteSQL(), the results are returned in a WSqlResult object in matrix form. The size of the matrix is given in attributes WSqlResult::nRows and WSqlResult::nColumns. Values can be read by WSqlResult::GetValue(), which take the index of a row and the index of a column as an argument. Use WSqlResult::GetHeader() to get the name of a column in the result matrix. ZWinSql does not care about the data type of SQL elements, all results are returned as strings. You can use WSqlDb::GetTableColumnType() to find out the original data type. Currently, ZWinSql does not handle binary objects (BLOBS).

If an error occurs, textual information about the last error can be gathered by calling WSqlDb::GetLastSqlErrorInfo().

To get more detailed usage information, have a look at the examples below and look into the detailed functional documentation in file "ZWinSql.chm" (generated by Doxygen from ZWinSql.h, included in ZWinSql's doc subdirectory).


Examples

The package comes with some example files:

More samples and additional programming tips may be added in later versions.

Please note: If you run these examples or other ZWinSql based programs, sqlite3.dll, which resides in .\sqlite3\bin, must be accessible via the Windows path. To ensure this for these samples, run setpath.bat in the sample's subdirectory.


ZWinSql compatibility

ZWinSql was developed and tested with Microsoft Visual C++ .NET 2003 (English version) and tested under Windows XP SVP2. Other versions of Visual C++ and Windows should work, but have not been tested. If you successfully run ZWinSql with other versions, please send the author a short note.

SQLite, on which ZWinSql is based, supports a major part of SQL 92. Features not supported are CHECK constraints, FOREIGN KEY constraints, nested transactions, RIGHT and FULL OUTER JOIN, GRANT and REVOKE, writing to VIEWS. Trigger and ALTER TABLE are only partially supported. See SQLite's documentation for details.


Release Notes

V0.1.0: Oct. 2005. First public beta release.
V0.2.0: Dec. 2005. Now uses SQLite 3.2.8
V0.3.0: Oct. 2006. Now uses SQLite 3.3.8
V0.4.0: Jul. 2007. Now uses SQLite 3.4.0


Disclaimer and copyright information

All files are provided in the hope, they might be useful, but without any warranty or support. You may use or modify them on your own risk in non-commercial applications. Even if ZWinSql is provided free of charge, it is no freeware. It is copyrighted by Werner.Zimmermann|AT|hs-esslingen.de, details see file 'Copying'.

If you want to use this software in commercial applications, contact the author.

SQlite comes with its own copyright notice, which states

		SQLite Copyright

		The original author of SQLite has dedicated the code to the public domain. 
		Anyone is free to copy, modify, publish, use, compile, sell, or distribute the 
		original SQLite code, either in source code form or as a compiled binary, for 
		any purpose, commerical or non-commerical, and by any means. 
		
For details see SQLite's web site www.SQLite.org.

In case you have problems directly related to ZWinSql, you may email a detailed description of the problem to Werner.Zimmermann|AT|hs-esslingen.de (Note: Don't forget to substitute '|AT|' by '@' in my email address!). But the author does not guarantee, that he will answer your mail or solve the problem. And please: Don't send me general SQL, C, C++ or Visual Studio related questions like: 'What is SQL, ...?' or 'How do I formulate a SQL query which ...' Things like these are explained much better than I could in references widely available over the internet or in any public library.

Windows, Visual C++, Visual Studio, SQL and other product names mentioned here may be trademarks or registered trademarks of their respective owners.


Programming tips

Sorry, none yet ...