5.1.1.1. INSERT/UPDATE/DELETE

$Self->{DBObject}->Do(
    SQL=> "INSERT INTO table (name, id) VALUES ('SomeName', 123)",
);

$Self->{DBObject}->Do(
    SQL=> "UPDATE table SET name = 'SomeName', id = 123",
);

$Self->{DBObject}->Do(
    SQL=> "DELETE FROM table WHERE id = 123",
);
            

5.1.1.2. SELECT

my $SQL = "SELECT id FROM table WHERE tn = '123'";

$Self->{DBObject}->Prepare(SQL => $SQL, Limit => 15);

while (my @Row = $Self->{DBObject}->FetchrowArray()) {
    $Id = $Row[0];
}
return $Id;
            

Take care to use Limit as param and not in the SQL string because not all databases support LIMIT in SQL strings.

5.1.1.3. QUOTE

String:

my $QuotedString = $Self->{DBObject}->Quote("It's a problem!");
                

Integer:

my $QuotedInteger = $Self->{DBObject}->Quote('123', 'Integer');
                

Number:

my $QuotedNumber = $Self->{DBObject}->Quote('21.35', 'Number');
                

5.1.2.1. INSERT

<Insert Table="some_table">
    <Data Key="id">1</Data>
    <Data Key="description" Type="Quote">exploit</Data>
</Insert>
            

5.1.2.2. CREATE TABLE

Possible data types are: BIGINT, SMALLINT, INTEGER, VARCHAR (Size=1-1000000), DATE (Format: yyyy-mm-dd hh:mm:ss) and LONGBLOB.

<TableCreate Name="calendar_event">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="BIGINT"/>
    <Column Name="title" Required="true" Size="250" Type="VARCHAR"/>
    <Column Name="content" Required="false" Size="250" Type="VARCHAR"/>
    <Column Name="start_time" Required="true" Type="DATE"/>
    <Column Name="end_time" Required="true" Type="DATE"/>
    <Column Name="owner_id" Required="true" Type="INTEGER"/>
    <Column Name="event_status" Required="true" Size="50" Type="VARCHAR"/>
    <Index Name="calendar_event_title">
        <IndexColumn Name="title"/>
    </Index>
    <Unique Name="calendar_event_title">
        <UniqueColumn Name="title"/>
    </Unique>
    <ForeignKey ForeignTable="users">
        <Reference Local="owner_id" Foreign="id"/>
    </ForeignKey>
</TableCreate>
            

5.1.2.3. DROP TABLE

<TableDrop Name="calendar_event"/>
            

5.1.2.4. ALTER TABLE

The following shows an example of add, change and drop columns.

<TableAlter Name="calendar_event">
    <ColumnAdd Name="test_name" Type="varchar" Size="20" Required="1"/>

    <ColumnChange NameOld="test_name" NameNew="test_title" Type="varchar" Size="30" Required="1"/>

    <ColumnChange NameOld="test_title" NameNew="test_title" Type="varchar" Size="100" Required="0"/>

    <ColumnDrop Name="test_title"/>

    <IndexCreate Name="index_test3">
        <IndexColumn Name="test3"/>
    </IndexCreate>

    <IndexDrop Name="index_test3"/>

    <UniqueCreate Name="uniq_test3">
        <UniqueColumn Name="test3"/>
    </UniqueCreate>

    <UniqueDrop Name="uniq_test3"/>
</TableAlter>
            

The next shows an example how to rename a table.

<TableAlter NameOld="calendar_event" NameNew="calendar_event_new"/>
            

5.1.2.5. Code to process XML

my @XMLARRAY = @{$Self->ParseXML(String => $XML)};

my @SQL = $Self->{DBObject}->SQLProcessor(
    Database => \@XMLARRAY,
);
push(@SQL, $Self->{DBObject}->SQLProcessorPost());

foreach (@SQL) {
    $Self->{DBObject}->Do(SQL => $_);
}