From 3d3f8d3832921f99daf8ce1953304763c2e76c62 Mon Sep 17 00:00:00 2001 From: wei <> Date: Fri, 14 Apr 2006 06:22:09 +0000 Subject: Importing SQLMap + sample + docs. --- docs/sqlmap/latex/ch4.tex | 306 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 306 insertions(+) create mode 100644 docs/sqlmap/latex/ch4.tex (limited to 'docs/sqlmap/latex/ch4.tex') diff --git a/docs/sqlmap/latex/ch4.tex b/docs/sqlmap/latex/ch4.tex new file mode 100644 index 00000000..a4cbc937 --- /dev/null +++ b/docs/sqlmap/latex/ch4.tex @@ -0,0 +1,306 @@ +\chapter{Parameter Maps and Inline Parameters}\label{section:3.4} +Most SQL statements are useful because we can pass them values at runtime. +Someone wants a database record with the ID 42, and we need to merge that ID +number into a select statement. A list of one or more parameters are passed at +runtime, and each placeholder is replaced in turn. This is simple but labor +intensive, since developers spend a lot of time counting symbols to make sure +everything is in sync. + +\begin{mybox}{Note:} +Preceding sections briefly touched on inline parameters, which automatically +map properties to named parameters. Many iBATIS developers prefer this +approach. But others prefer to stick to the standard, anonymous approach to +SQL parameters by using parameter maps. Sometimes people need to retain the +purity of the SQL statements; other times they need the detailed specification +offered by parameter maps due to database or provider-specific information +that needs to be used. +\end{mybox} + +\section{Parameter Map} +A Parameter Map defines an ordered list of values that match up with the +placeholders of a parameterized query statement. While the attributes +specified by the map still need to be in the correct order, each parameter is +named. You can populate the underlying class in any order, and the Parameter +Map ensures each value is passed in the correct order. + +Parameter Maps can be provided as an external element and \emph{inline}. +Example~\ref{example:3.16} shows an external Parameter Map. + +\begin{example}\label{example:3.16} +An external Parameter Map +\begin{verbatim} + + + + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.16}, the parts in [brackets] are optional. The +\tt{parameterMap} element only requires the id attribute. +Example~\ref{example:3.17} shows a typical \tt{}. + +\begin{example}\label{example:3.17} +A typical \tt{} element +\begin{verbatim} + + + + + + + insert into PRODUCT (PRD_DESCRIPTION, PRD_ID) values (?,?); + +\end{verbatim} +\end{example} + +\begin{mybox}{Note:} +Parameter Map names are always local to the Data Map definition file where +they are defined. You can refer to a Parameter Map in another Data Map +definition file by prefixing the \tt{id} of the Parameter Map with the +namespace of the Data Map (set in the \tt{} root element). If the +Parameter Map in Example~\ref{example:3.17} were in a Data Map named +``Product'', it could be referenced from another file using +``Product.insert-product-param''. +\end{mybox} + +\subsection{\tt{} attributes} The \tt{} element +accepts two attributes: \tt{id} (required) and \tt{extends} (optional). + +\subsubsection{\tt{id} attribute} The required \tt{id} attribute provides a +unique identifier for the \tt{} within this Data Map. + +\subsubsection{\tt{extends} attribute} +The optional \tt{extends} attribute can be set to the name of another +\tt{parameterMap} upon which to base this \tt{parameterMap}. All properties of +the super \tt{parameterMap} will be included as part of this +\tt{parameterMap}, and values from the super \tt{parameterMap} are set before +any values specified by this \tt{parameterMap}. The effect is similar to +extending a class. + +\section{\tt{} Elements} +The \tt{} element holds one or more parameter child elements +that map object properties to placeholders in a SQL statement. The sections +that follow describe each of the attributes. + +\subsection{\tt{property} attribute} +The \tt{property} attribute of \tt{} is the name of a property of +the parameter object. It may also be the name of an entry in an array. The +name can be used more than once depending on the number of times it is needed +in the statement. (In an update, you might set a column that is also part of +the where clause.) + +\subsection{\tt{direction} attribute} +The \tt{direction} attribute may be used to indicate a stored procedure +parameter's direction. + +\subsection{\tt{column} attribute} +The \tt{column} attribute is used to define to the name of a parameter used by +a stored procedure. + +\begin{table}[!h]\centering\label{table:3.3} +\caption{Parameter \tt{direction} attribute values } +\begin{tabular}{|l|l|} + \hline + % after \\: \hline or \cline{col1-col2} \cline{col3-col4} ... + \textbf{Value} & \textbf{Description}\\ + \hline + Input & input-only \\ + \hline + Output & output-only \\ + \hline + InputOutput & bidirectional \\ + \hline +\end{tabular} +\end{table} + +\subsection{\tt{dbType} attribute} +The \tt{dbType} attribute is used to explicitly specify the database column +type of the parameter to be set by this property. This attribute is normally +only required if the column is nullable. Although, another reason to use the +\tt{dbType} attribute is to explicitly specify date types. Most SQL databases +have more than one \tt{datetime} type. Usually, a database has at least three +different types (DATE, DATETIME, TIMESTAMP). In order for the value to map +correctly, you might need to specify the column's \tt{dbType}. + +\begin{mybox}{Note:} +Most providers only need the \tt{dbType} specified for nullable columns. In +this case, you only need to specify the type for the columns that are +nullable. +\end{mybox} + +\subsection{\tt{type} attribute} +The \tt{type} attribute is used to specify the type of the parameter's +property. This attribute is useful when passing \tt{InputOutput} and +\tt{Output} parameters into stored procedures. The framework uses the +specified type to properly handle and set the parameter object's properties +with the procedure's output values after execution. + +%If the attribute type is not set and the framework cannot otherwise determine +%the type, the type is assumed to be an StdObject. Section~\ref{section:6} +%details the types and available aliases that have pre-built support in the +%framework. + +\subsection{\tt{nullValue} attribute}\label{section:nullValueParameter} +The \tt{nullValue} attribute can be set to any valid value (based on property +type). The \tt{nullValue} attribute is used to specify an outgoing null value +replacement. What this means is that when the value is detected in the object +property, a NULL will be written to the database (the opposite behavior of an +inbound null value replacement). This allows you to use a magic null number in +your application for types that do not support null values (such as int, +double, float). When these types of properties contain a matching null value +(for example, say, $-9999$), a NULL will be written to the database instead of +the value. + + +\begin{mybox}{Tip:} +For round-trip transparency of null values, you must also specify database +columns null value replacements in your Result Map (see +Chapter~\ref{section:3.5}). +\end{mybox} + + +\subsection{\tt{size} attribute} +The \tt{size} attribute sets the maximum size of the data within the column. + +\subsection{\tt{precision} attribute} +The \tt{precision} attribute is used to set the maximum number of digits used +to represent the property value. + +\subsection{\tt{scale} attribute} +The \tt{scale} attribute sets the number of decimal places used to resolve the +property value. + +\subsection{\tt{typeHandler} attribute} +The \tt{typeHandler} attribute allows the use of a Custom Type Handler (see +the Custom Type Handler section). This allows you to extend the DataMapper's +capabilities in handling types that are specific to your database provider, +are not handled by your database provider, or just happen to be a part of your +application design. You can create custom type handlers to deal with storing +and retrieving booleans from your database for example. + +\section{Inline Parameter Maps}\label{section:3.4.3} +If you prefer to use inline parameters instead of parameter maps, you can add +extra type information inline too. The inline parameter map syntax lets you +embed the property name, the property type, the column type, and a null value +replacement into a parametrized SQL statement. The next four examples shows +statements written with inline parameters. + +\begin{example}\label{example:3.18} + A \tt{} using inline parameters +\begin{verbatim} + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) + values (#id#, #description#) + +\end{verbatim} +\end{example} + +The following example shows how \tt{dbTypes} can be declared inline. + +\begin{example}\label{example:3.19} + A \tt{} using an inline parameter map with a type +\begin{verbatim} + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) + values (#id, dbType=int#, #description, dbType=VarChar#) + +\end{verbatim} +\end{example} + +The next example shows how \tt{dbTypes} and null value replacements can also +be declared inline. + +\begin{example}\label{example:3.20} +A \tt{} using an inline parameter map with a null value replacement +\begin{verbatim} + + insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) + values (#id, dbType=int, nullValue=-999999#, #description, dbType=VarChar#) + +\end{verbatim} +\end{example} + +\begin{example}\label{example:3.21} +A more complete example. +\begin{verbatim} + + update Accounts set + Account_FirstName = #FirstName#, + Account_LastName = #LastName#, + Account_Email = #EmailAddress,type=string,dbType=Varchar,nullValue=no_email@provided.com# + where + Account_ID = #Id# + +\end{verbatim} +\end{example} + +\begin{mybox}{Note:} +Inline parameter maps are handy for small jobs, but when there are a lot of +type descriptors and null value replacements in a complex statement, an +industrial-strength, external \tt{parameterMap} can be easier. +\end{mybox} + +\section{Standard Type Parameters} +In practice, you will find that many statements take a single parameter, often +an \tt{integer} or a \tt{string}. Rather than wrap a single value in another +object, you can use the standard library object (string, integer, et cetera) +as the parameter directly. Example~\ref{example:3.22} shows a statement using +a standard type parameter. + +\begin{example}\label{example:3.22} +A \tt{} using standard type parameters +\begin{verbatim} + + select * from PRODUCT where PRD_ID = #value# + +\end{verbatim} +\end{example} + +Assuming \tt{PRD\_ID} is a numeric type, when a call is made to this Mapped +Statement, a standard integer can be passed in. The \tt{\#value\#} parameter +will be replaced with the value of the integer. The name \tt{value} is simply +a placeholder, you can use another moniker of your choice. Result Maps support +primitive types as results as well. + +For your convenience, the following PHP primitive types are supported. +\begin{itemize} + \item \tt{string} + \item \tt{float} or \tt{double} + \item \tt{integer} or \tt{int} + \item \tt{bool} or \tt{boolean} +\end{itemize} + +\section{Array Type Parameters} +You can also pass in a array as a parameter object. This would usually be a an +associative array. Example~\ref{example:3.23} shows a \tt{} using +an array for a \tt{parameterClass}. + + +\begin{example}\label{example:3.23} +A \tt{} using an array for a \tt{parameterClass} +\begin{verbatim} + + select * from PRODUCT + where PRD_CAT_ID = #catId# + and PRD_CODE = #code# + +\end{verbatim} +\end{example} + +In Example~\ref{example:3.23}, notice that the SQL in this Mapped Statement +looks like any other. There is no difference in how the inline parameters are +used. If an associative array is passed, it must contain keys named \tt{catId} +and \tt{code}. The values referenced by those keys must be of the appropriate +type for the column, just as they would be if passed from a properties object. -- cgit v1.2.3