Data Definition Statements and PurePerl MySQL API

Using the PurePerl MySQL API – Part 2

Writing a Perl Module

Foreword: In this part of the series, I show you how data definition statements can be used in the PurePerl MySQL API.

By: Chrysanthus Date Published: 28 Jan 2015

Introduction

This is part 2 of my series, Using the PurePerl MySQL API. In this part of the series, I show you how data definition statements can be used with the PurePerl MySQL API. It is simple: type your SQL statement as a string without the ending semicolon, as argument to the query() function. PurePerl stands for Pure Perl. It is Perl Software without C software underneath. I will use a database called PetStore to explain the use of data definition statements. You should have read the previous part of the series before reaching here, as this is a continuation.

Examples of Data Definition Statements
There are many data definition statements. I will use only the CREATE DATABASE and CREATE TABLE statements here. I will use these statements to create the database, PetStore and its table. You use the other data definition statements in the same way as I use these two: as the query()  function argument. You should try all the code samples of this series.

The query()  Function Syntax
The query function syntax is:

    Boolean query("SQLstr");

You type a semicolon at the end of the query() function call and not at the end of the SQL statement. The SQL statement is typed in quotes without the ending semicolon. The function returns 1 if it succeeds or 0 if it fails. Any positive message developed is got from the variable, Message accessible from your script by, $Mysql::Message . Any error message is got by accessing $Mysql::Error (from your script).

Creating a Database
The following code segment will create a database, if connection has been accepted:

            my $crdb = "create database PetStore";
            if (Mysql::query($crdb) != 1)
                {
                    print $Mysql::Error_msg, "\n";
                }
            else
                {
                    #select and use database
                }

After creating the database, you can select it with the following code segment:

            if (Mysql::select_db("PetStore") != 1)
                {
                    print $Mysql::Error_msg, "\n";
                }
            else
                {
                    #Statements to create tables and use the database can go here.
                }

Creating a Table
After selecting the database, you can go on to create tables. The following code segment creates a table:

            my $crtbl = "CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE)";
            if (Mysql::query($crtbl) != 1)
             {
             print $Mysql::Error_msg, "\n";
             }
            else
             {
             #you can insert rows here.
             }

Now, the next time you want to use this same database, you do not have to create it again. You simply select it.

That is it for this part of the series. In the next part of the series, we do data manipulation

Chrys

Broad Network

Related Articles

Data Definition Statements and PurePerl MySQL API

Using the PurePerl MySQL API – Part 2

Writing a Perl Module

Introduction

Comments