Using the module MySQL: Introduction

Introduction

[<<<] [>>>]

This document describes how to install and use the module MySQL.

There are separate documents that describe the language (Users’ Guide), the major architecture of the source code (Source Guide).

This document describes the version 1.0 of the module.

Document version: 4.0

The module MySQL allows ScriptBasic programmers to connect to MySQL database and to execute SQL commands. The module allow access to almost all the functionality that the MySQL native C interface provides. You can connect to a database, execute SQL command, disconnect, but also execute administration commands like mysql::kill that kills a query thread on the server in case you have privilege.

[<<<] [>>>]


Using the module MySQL: Installing the module

Installing the module

The module comes in source form or as compiled binary. Binary is usually available for the Win32 platforms only. The compilation of the source code is simple. Compile the source files to a .dll or to .so shared library. To do so you will find an install.sh command file in the UNIX distribution of ScriptBasic in the directory extensions/mysql. To execute this command file you have to have root privileges and have to type:

sh install.sh

The compiled module contains a dynamically load binary file and a basic include file named mysql.bas. The include file mysql.bas should be placed in a module include directory file configured in the configuration file. The configuration file usually named scriba.conf should include a line

include /etc/scriba/include/

defining the include files location for the modules and other generally used include files. The actual directory can be different.

The binary part of the module should be placed in another directory specified in the configuration file, like

module /usr/scriba/modules/

There can be more than one include and module statement in the configuration file. In that case the interpreter searches the directories in the order they are specified until the include file or the module is found.


Using the module MySQL: While compiling the compiler complains

While compiling the compiler complains

mysqlinterf.c:10: mysql.h: No such file or directory

Be sure that you have a MySQL client installed on your UNIX. Check that the file `mysql.h' is in the directory `/usr/include' or in a directory that is specified using the –I option of the compiler. A sample `install.sh' file is the following:

cc -c -I/usr/include/mysql -o mysqlinterf.o mysqlinterf.c
ld -shared -o mysql.so mysqlinterf.o -lc -lmysqlclient
mv *.so /etc/scriba/modules


Using the module MySQL: Module can not be loaded

Module can not be loaded

You have an old MySQL installed. ScriptBasic MySQL interface was tested using MySQL v3.23. Older versions do not support mysql_real_escape_string, mysql_change_user and mysql_character_set_name functions. To be able to compile the interface for this older version edit the mysqlinterf.c file and search the line:

#define VERSION323 1

and change it to

#define VERSION323 0

This will switch off the use of these functions, however be sure that your BASIC program does not use the functions mysql::ChangeUser, mysql::EscapeString and mysql::CharacterSetName.


Using the module MySQL: Configuration

Configuration

The module is not configurable currently. However there are plans introducing configuration options and you can also define connections in the configuration file.

The section of the configuration file important for the MySQL module is under the key mysql. For example:

mysql (
  connections (
    test (        ; the name of the connection
      host "127.0.0.1" ; the host for the connection
      db "test"   ; database for the connection
      user "root" ; user for the connection
      password "" ; password for the connection
      port 0      ; the port to use
      socket ""  ; the name of the socket or ""
      flag 0      ; the client flag
      )
    )
  )

This fragment of the configuration file defines a connection that ScriptBasic programs may use. The parameters of the connections are defined in the configuration file and the BASIC program should only refer to it without knowing the actual host, user, password or other parameters. These are matters of configuration. It is also advisable not to include such sensitive information like a password into the BASIC scripts. What is more: later versions of the module loaded by multithreaded version of the interpreter may keep such configured connections alive and reuse them resulting faster database handling.


Using the module MySQL: Using the module

Using the module

To use the module the basic program should include the file mysql.bas. To do this the program should contain the line

include mysql.bas

somewhere at the start of the code. Note that there are no double quote characters before and after the file name. This tells the interpreter that the include file is located in a module include directory. This include file contains all the declarations that are needed to use the MySQL functions.

The program using the module should call the functions declared in the include file. The binary library file is loaded when the first function call is executed.


Using the module MySQL: RealConnect

RealConnect

dbh =
 mysql::RealConnect(host,user,password,database [,port] [,unixsocketname] [,clientflag])

Connect to a database using the real connection parameters.


Using the module MySQL: Connect

Connect

dbh = mysql::Connect(connection)

Connect to a database using a named connection. The connection parameters should be configured in the ScriptBasic configuration file.


Using the module MySQL: Close

Close

mysql::Close dbh

Close an opened connection. There is no need to call this function, all database connections are automatically closed when the interpreter finishes executing the BASIC script. However it is polite to close a database connection as soon as possible.


Using the module MySQL: Query

Query

mysql::query dbh,query_string

Execute a query.


Using the module MySQL: FetchArray

FetchArray

Mysql::FetchArray dbh,arr

Get a row from a result set after a query. The row will be placed in the variable arr as an array each element containing the value of the appropriate column.


Using the module MySQL: FetchHash

FetchHash

mysql::FetchHash dbh, arr

Get a row from a result set after a query. The row will be placed in the variable arr as an associative array. Each column name is associated with the column value of the actual row.


Using the module MySQL: AffectedRows

AffectedRows

n = mysql::AffectedRows(dbh)

Returns the number of affected rows after executing an INSERT, DELETE or similar command.


Using the module MySQL: ChangeUser

ChangeUser

mysql::ChangeUser dbh, user,password [,database]

Change the actual user on an existing connection. Optionally change the database of the connection also.


Using the module MySQL: CharacterSetName

CharacterSetName

A$ = mysql::CharacterSetName(dbh)

Return the character set name string actually used by the connection.


Using the module MySQL: DataSeek

DataSeek

mysql::DataSeek dbh,n

Seek to a certain row of a result set.


Using the module MySQL: ErrorMessage

ErrorMessage

A$ = mysql::ErrorMessage(dbh)

Returns the string describing the last database handling error.


Using the module MySQL: GetClientInfo

GetClientInfo

A$ = mysql::GetClientInfo()

Returns the character string that represents the MySQL client library version.


Using the module MySQL: GetHostInfo

GetHostInfo

A$ = mysql::GetHostInfo(dbh)

Returns a string describing the type of connection in use, including the server host name.


Using the module MySQL: GetProtoInfo

GetProtoInfo

n = mysql::GetProtoInfo(dbh)

Returns the protocol version used by current connection. This is an unsigned integer representing the protocol version used by the current connection.


Using the module MySQL: GetServerInfo

GetServerInfo

A$ = mysql::GetServerInfo(dbh)

This function returns a string that represents the server version number.


Using the module MySQL: Info

Info

A$ = mysql::Info(dbh)

This function retrieves a string providing information about the most recently executed query, but only for the statements listed below. For other statements, mysql::Info() returns undef. The format of the string varies depending on the type of query, as described below. The numbers are illustrative only; the string will contain values appropriate for the query.

INSERT INTO ... SELECT ...
String format: Records: 100 Duplicates: 0 Warnings: 0
INSERT INTO ... VALUES (...),(...),(...)...
String format: Records: 3 Duplicates: 0 Warnings: 0
LOAD DATA INFILE ...
String format: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
ALTER TABLE
String format: Records: 3 Duplicates: 0 Warnings: 0
UPDATE
String format: Rows matched: 40 Changed: 40 Warnings: 0

Note that mysql::Info() returns a string value for the INSERT ... VALUES statement only if multiple value lists are specified in the statement. Otherwise it returns undef.


Using the module MySQL: InsertId

InsertId

n = mysql::InsertId(dbh)

Returns the ID generated for an AUTO_INCREMENT column by the previous query. Use this function after you have performed an INSERT query into a table that contains an AUTO_INCREMENT field.

Note that mysql::InsertId() returns 0 if the previous query does not generate an AUTO_INCREMENTvalue. If you need to save the value for later, be sure to call mysql::InsertId() immediately after the query that generates the value.

Also note that the value of the SQL LAST_INSERT_ID() function always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries because the value of that function is maintained in the server.


Using the module MySQL: Kill

Kill

mysql::Kill dbh,pid

Asks the server to kill the thread specified by the pid.


Using the module MySQL: Ping

Ping

n = mysql::Ping(dbh)

Checks whether or not the connection to the server is working. If it has gone down, an automatic reconnection is attempted.

Clients that remain idle for a long while, to check whether or not the server has closed the connection and reconnect if necessary can use this function.

Returns true if the server is alive and false if an error occurred.


Using the module MySQL: EscapeString

EscapeString

A$ = mysql::EscapeString(dbh,string)

Encodes the string to an escaped SQL string, taking into account the current charset of the connection, that can be sent to the server in a SQL statement, places the result in to, and adds a terminating null byte. Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z.


Using the module MySQL: SelectDatabase

SelectDatabase

Mysql::SelectDb dbh,database

Causes the database specified by database to become the default (current) database on the connection specified by MySQL. In subsequent queries, this database is the default for table references that do not include an explicit database specifier.


Using the module MySQL: Shutdown

Shutdown

mysql::Shutdown dbh

This function asks the database server to shut down. The connected user must have shutdown privileges.


Using the module MySQL: Stat

Stat

A$ = mysql::Stat(dbh)

This function returns a character string containing information similar to that provided by the mysqladmin status command. This includes uptime in seconds and the number of running threads, questions, reloads, and open tables.


Using the module MySQL: ThreadId

ThreadId

n = mysql::ThreadId(dbh)

Returns the thread ID of the current connection. This value can be used as an argument to mysq::Kill() to kill the thread.

If the connection is lost and you reconnect with mysql::Ping(), the thread ID will change. This means you should not get the thread ID and store it for later. You should get it when you need it.


Using the module MySQL: Sample code

Sample code

import mysql.bas

dbh = mysql::Connect("test")
print "The data base handle is: ",dbh,"\n"
' mysql::Shutdown dbh
' print mysql::ErrorMessage(),"\n"
print mysql::Stat(dbh)
print
mysql::query dbh,"delete from users where name='Kakukk'"
print "Affected rows after delete is: ",mysql::AffectedRows(dbh)
print
mysql::query dbh,"insert into users values ('Kakukk',52)"
print "Affected rows after inserting kakukk: ",mysql::AffectedRows(dbh)
print
print "Info is: ",mysql::Info(dbh)
print
mysql::query dbh,"select * from users order by name desc"
print "Affected rows after select: ",mysql::AffectedRows(dbh)
print
' mysql::DataSeek dbh,1
' print
i=0
while mysql::fetcharray(dbh,q)
i=i+1
print i,". ",q[0]," ",q[1]
print
wend
print "Character set name is: ",mysql::CharacterSetName(dbh)
print

mysql::query dbh,"select * from users order by name desc"
print "Affected rows after select: ",mysql::AffectedRows(dbh)
print
' mysql::DataSeek dbh,1
' print
i=0
while mysql::FetchHash(dbh,q)
i=i+1
print i,". "
print
print "name=",q{"name"}
print
print "age=",q{"age"}
print
wend

' mysql::DataSeek dbh,0
on error resume next
mysql::query dbh,"select * from user"
print "Last error is: ",mysql::ErrorMessage(dbh)
print
print "Client info is: ",mysql::GetClientInfo()
print
print "Host info is: ",mysql::GetHostInfo(dbh)
print
print "Proto info is: ",mysql::GetProtoInfo(dbh)
print
print "Server info is: ",mysql::GetServerInfo(dbh)
print

mysql::query dbh,"SHOW PROCESSLIST"
print "Affected rows after show processlistselect: ",mysql::AffectedRows(dbh)
print
i=0
while mysql::fetcharray(dbh,q)
i=i+1
print i,". ",q[0]
' mysql::kill dbh,q[0]
print
wend
print "ping result: ",mysql::Ping(dbh)
print "haho"
print
on error resume next
mysql::Query dbh,"INSERT INTO autoinc values ('huuuh',null)"
print mysql::ErrorMessage(dbh)
print
print mysql::InsertId(dbh)
print

print "Thread id=",mysql::ThreadId(dbh)
print
mysql::kill dbh, mysql::ThreadId(dbh)

mysql::Close(dbh)


Using the module MySQL: Future development, aims

Future development, aims

Later with development of interfaces to other databases (Oracle and PostgreSQL is planned and, well ODBC) there will be a definite need to develop an interface that hides the difference between the databases.

As soon as we are ready with the ScriptBasic daemon engine for fast web applications a new version of this module will be developed that will keep connection pools available and not close and reconnect for each client.


Using the module MySQL

by Peter Verhas