Corso Italia, 178 - 56125 Pisa
Telefono/fax 050 46380
e-mail picosoft@picosoft.it


The project of picoSQL was born in 1995. It was an ODBC driver for COBOL indexed files. It consisted of a DLL written in C++ on Windows 3.1 operating system. In the beginning the main effort was made in order to obtain a good compatibity with existing front-end (mainly the MS-Access ©, MS-Query © and VisualBasic ©) and in order to see COBOL files as a normalized relational DB.

The obtained success concurs to develop ulteriorly the product, refining the query optimizer and implementing an extended SQL, to exclusion of the DDL (Data Description Language). A later version, called PicoDB, were devoloped on UNIX system in client/server architecture.

PicoSql is released under GPL license; it derives from PicoDB (that remains a Picosoft proprietary product) with the addition of some SQL-DDL statement (CREATE/DROP TABLE(INDEX) and excluding COBOL files compatibility). The C/C++ library that allow the database access from C/C++ programs using the ODBC 2.5 API is released under LGPL license; this allows to link the library with programs that are not under GPL.

PicoSQL can be used from Windows applications (like MS-ACCESS ;©, MS-Query ©, VB © etc.) in client/server modality using the ODBC driver picoSQLNet. It can moreover be used from appropriate Java programs through the JDBC driver comprised in the distribution. Finally it can be used from programs written in C and C++ through its set of ODBC 2.5 compliant API.

PicoSQL lacks at the moment of some common characteristics to the most common RDBMS (like the schema and catalogue concepts and the management of the user permissions who can be obtained anyway using the permissions of the host operating system) but they are just these lacks to make it particularly simple to install and to use and fast in the logons and the queries.


The installation is particularly simple: if you have the binary distribution, is sufficient to extract the compressed files in one directory (\ under Windows NT/2000/XP) and execute:

startTestDb.sh (Unix/Linux)
startTestDb.bat (Windows) 
in order to activate the DB with a test database already configured.

Warning! under Windows NT/2000/XP the script setReg.js is launched to insert some informations in the registry file. One of this information is about the test DB location and it must be changed if the installation directory is not \

At this point, if you have Java on your computer, you can run:
iSQLViewerTest.sh (Unix/Linux)
iSQLViewerTest.bat (Windows)
in order to query the DB. If you does not have Java, you can use the program
picosql.sh (Unix/Linux)
picosql.bat (Windows)
in order to make SQL query to the DB from the command line.

The installation directory contains the following subdirectory:

PicoSQL consists fundamentally in a program, picosqld, to activate through inetd (or xinetd) or picoserv. In such a way it becomes a net service binded to a port, that is to a entire number comprised between 1 and 65535; this number (as an example 6789) must always be specified on the command line of picosqld. As an example:

picosqld 6789
For the activation with inetd (or xinetd), you must see the relative documentation. picoserv is a simple program that replaces inetd, mainly for scopes of test or debugging: its usage is the following:
picoserv [-f] commandLine portNumber
In our case the activation of picosqld can be obtained with the following command line:
picoserv "picosqld 6789" 6789
To notice as the port number is specified two times, as an argument of picosqld and as an argument of picoserv.

When picosqld receive a connection request, it reads a configuration file to find the necessary informations. This file is a text file on Unix/Linux systems and is the system registry file on Windows. In the latter case you can modify the configuration using the regedit program.

A DB is simply a directory that contains all the tables created by the user more three, __TABLES __COLUMNS and __VIEWS, created automatically at the first logon and containing the DB data dictionary. Valid attributes are:
Attribute name Description Valid values
The directory path under which the data will be archived. This is the only mandatory attribute. A valid path
The user that can access to this DB. A user name
The password that must be used to access to this DB. A password name
Designates a log file in which picosql stores the log of all the transaction done. This file can be used by the program pIrecover to rebuild the DB on the bese of the last valid backup. To activate this feature is necessary to put picosql in AUTOCOMMIT=OFF mode using the appropriate ODBC/JDBC API.
Warning! In the current release, operation on BLOBS cannot be rollbacked.
A valid path
Set the transaction mode ON/OFF without log file. To activate this feature is necessary to put picosql in AUTOCOMMIT=OFF mode using the appropriate ODBC/JDBC API.
Warning! In the current release, operation on BLOBS cannot be rollbacked.
Y or N (default)
When a program try to lock a row already locked by another program, using for example a "SELECT ... FOR UPDATE" statement, it receive an appropriate error. Putting Y in this attribute, picoSQL waits until the row become available. Y or N (default)
Setting this option to Y, the DB user becomes the system user too and it acts with the system user permissions. To do this option working, the daemon mut be run with the appropriate permission (from root in Linux/Unix environments and with privileges SeTcbPrivilege, SeAssignPrimary e SeIncreaseQuota on Windows; in Windows environments, the DB user and password must correspond with those of system user). Y o N (default)
Activate a log file reporting all API calls. The log file is located under /tmp (or C:\ in Windows) and it is called PICOSQL<pid>.log Y or N (default)

Accessing the DB

To access the data stored in picoSQL you can use Java programs by means of driver JDBC, or you can use Windows programs by means of driver ODBC. C/C++ programs can use the libpicosqlnet.a (libpicosqlnet.lib on Windows system), who implements the ODBC API set. These libraries use a configuration file again (picosql.ini on Unix/Linux systems and the system registry file on Windows systems) to get the informations about connection. You can have a single file for both server side and client side, but you can do this only if you access the DB from the same local computer and if you do not have security issues. A more general configuration consists of a file /etc/picosql.ini (in Windows the key HKEY_LOCAL_MACHINE\Software\PicoSoft\PicoSQL ) in which are stored the server information and that can be read only by the DB administrator; each user has his own file .picosql.ini (in Windows the key HKEY_CURRENT_USER\Software\PicoSoft\PicoSQL ) under his home directory in which resides the connection information.

From a client program point of view, each section correspond to a logic DB name (the equivalent of the Data Source Name in the ODBC architecture) which may differs from the corresponding server DB name. The valid attributes in the section are:

Attribute name Description
Address or network name of the host where the DB is running
Port number where picoSQL is listening
Server DB name