The SQL

If you are not using stored procedures, the most important part of a statement-type element is the SQL. You can use any SQL statement that is valid for your database system. Since SQLMap passes the SQL through to a standard libraries (Adodb for PHP), you can use any statement with SQLMap that you could use without SQLMap. You can use whatever functions your database system supports, and even send multiple statements, so long as your driver or provider supports them.

Escaping XML symbols

Because you are combining SQL and XML in a single document, conflicts can occur. The most common conflict is the greater-than and less-than symbols (><). SQL statements use these symbols as operators, but they are reserved symbols in XML. A simple solution is to escape the SQL statements that uses XML reserved symbols within a CDATA element. The following example demonstrates this. #value# ]]>

Auto-Generated Keys

Many database systems support auto-generation of primary key fields, as a vendor extension. Some vendors pre-generate keys (e.g. Oracle), some vendors post-generate keys (e.g. MS-SQL Server and MySQL). In either case, you can obtain a pre-generated key using a <selectKey> stanza within an <insert> element. The following example shows an <insert> statement for either approach.

SELECT STOCKIDSEQUENCE.NEXTVAL AS VALUE FROM DUAL insert into PRODUCT (PRD_ID,PRD_DESCRIPTION) values (#id#,#description#) insert into PRODUCT (PRD_DESCRIPTION) values (#description#) select @@IDENTITY as value insert into PRODUCT (PRD_DESCRIPTION) values (#description#) select LAST_INSERT_ID() as value

<generate> tag

You can use SQLMap to execute any SQL statement your application requires. When the requirements for a statement are simple and obvious, you may not even need to write a SQL statement at all. The <generate> tag can be used to create simple SQL statements automatically, based on a <parameterMap> element. The four CRUD statement types (insert, select, update, and delete) are supported. For a select, you can select all or select by a key (or keys). The following example shows an example of generating the usual array of CRUD statements.

Important: The intended use of the <generate> tag is to save developers the trouble of coding mundane SQL statements (and only mundane statements). It is not meant as a object-to-relational mapping tool. There are many frameworks that provide extensive object-to-relational mapping features. The <generate> tag is not a replacement for any of those. When the <generate> tag does not suit your needs, use a conventional statement instead.
select @@IDENTITY as value

The tag generates ANSI SQL, which should work with any compliant database. Special types, such as blobs, are not supported, and vendor-specific types are also not supported. But, the generate tag does keep the simple things simple.

Note: The SQL is generated when the DataMapper instance is built and can be cached afterward, so there is no performance impact at execution time.

The generate tag supports two attributes.

AttributeDescriptionRequired
table specifies the table name to use in the SQL statement yes
by specifies the columns to use in a WHERE clause no