CINXE.COM

<!DOCTYPE html> <html><head> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <link href="../sqlite.css" rel="stylesheet"> <title>Compiling An SQL Statement</title>  </head> <body> <div class=nosearch> <a href="../index.html"> <img class="logo" src="../images/sqlite370_banner.svg" alt="SQLite" border="0"> </a> <div></div> <div class="tagline desktoponly"> Small. Fast. Reliable.<br>Choose any three. </div> <div class="menu mainmenu"> <ul> <li><a href="../index.html">Home</a> <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> <li class='wideonly'><a href='../about.html'>About</a> <li class='desktoponly'><a href="../docs.html">Documentation</a> <li class='desktoponly'><a href="../download.html">Download</a> <li class='wideonly'><a href='../copyright.html'>License</a> <li class='desktoponly'><a href="../support.html">Support</a> <li class='desktoponly'><a href="../prosupport.html">Purchase</a> <li class='search' id='search_menubutton'> <a href="javascript:void(0)" onclick='toggle_search()'>Search</a> </ul> </div> <div class="menu submenu" id="submenu"> <ul> <li><a href='../about.html'>About</a> <li><a href='../docs.html'>Documentation</a> <li><a href='../download.html'>Download</a> <li><a href='../support.html'>Support</a> <li><a href='../prosupport.html'>Purchase</a> </ul> </div> <div class="searchmenu" id="searchmenu"> <form method="GET" action="../search"> <select name="s" id="searchtype"> <option value="d">Search Documentation</option> <option value="c">Search Changelog</option> </select> <input type="text" name="q" id="searchbox" value=""> <input type="submit" value="Go"> </form> </div> </div> <script> function toggle_div(nm) { var w = document.getElementById(nm); if( w.style.display=="block" ){ w.style.display = "none"; }else{ w.style.display = "block"; } } function toggle_search() { var w = document.getElementById("searchmenu"); if( w.style.display=="block" ){ w.style.display = "none"; } else { w.style.display = "block"; setTimeout(function(){ document.getElementById("searchbox").focus() }, 30); } } function div_off(nm){document.getElementById(nm).style.display="none";} window.onbeforeunload = function(e){div_off("submenu");} /* Disable the Search feature if we are not operating from CGI, since */ /* Search is accomplished using CGI and will not work without it. */ if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ document.getElementById("search_menubutton").style.display = "none"; } /* Used by the Hide/Show button beside syntax diagrams, to toggle the */ function hideorshow(btn,obj){ var x = document.getElementById(obj); var b = document.getElementById(btn); if( x.style.display!='none' ){ x.style.display = 'none'; b.innerHTML='show'; }else{ x.style.display = ''; b.innerHTML='hide'; } return false; } var antiRobot = 0; function antiRobotGo(){ if( antiRobot!=3 ) return; antiRobot = 7; var j = document.getElementById("mtimelink"); if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); } function antiRobotDefense(){ document.body.onmousedown=function(){ antiRobot |= 2; antiRobotGo(); document.body.onmousedown=null; } document.body.onmousemove=function(){ antiRobot |= 2; antiRobotGo(); document.body.onmousemove=null; } setTimeout(function(){ antiRobot |= 1; antiRobotGo(); }, 100) antiRobotGo(); } antiRobotDefense(); </script>  <div class=nosearch> <a href="../c3ref/intro.html"><h2>SQLite C Interface</h2></a> <h2>Compiling An SQL Statement</h2> </div> <blockquote><pre> int sqlite3_prepare( sqlite3 *db, /* Database handle */ const char *zSql, /* SQL statement, UTF-8 encoded */ int nByte, /* Maximum length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const char **pzTail /* OUT: Pointer to unused portion of zSql */ ); int sqlite3_prepare_v2( sqlite3 *db, /* Database handle */ const char *zSql, /* SQL statement, UTF-8 encoded */ int nByte, /* Maximum length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const char **pzTail /* OUT: Pointer to unused portion of zSql */ ); int sqlite3_prepare_v3( sqlite3 *db, /* Database handle */ const char *zSql, /* SQL statement, UTF-8 encoded */ int nByte, /* Maximum length of zSql in bytes. */ unsigned int prepFlags, /* Zero or more SQLITE_PREPARE_ flags */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const char **pzTail /* OUT: Pointer to unused portion of zSql */ ); int sqlite3_prepare16( sqlite3 *db, /* Database handle */ const void *zSql, /* SQL statement, UTF-16 encoded */ int nByte, /* Maximum length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const void **pzTail /* OUT: Pointer to unused portion of zSql */ ); int sqlite3_prepare16_v2( sqlite3 *db, /* Database handle */ const void *zSql, /* SQL statement, UTF-16 encoded */ int nByte, /* Maximum length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const void **pzTail /* OUT: Pointer to unused portion of zSql */ ); int sqlite3_prepare16_v3( sqlite3 *db, /* Database handle */ const void *zSql, /* SQL statement, UTF-16 encoded */ int nByte, /* Maximum length of zSql in bytes. */ unsigned int prepFlags, /* Zero or more SQLITE_PREPARE_ flags */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const void **pzTail /* OUT: Pointer to unused portion of zSql */ ); </pre></blockquote> <p> To execute an SQL statement, it must first be compiled into a byte-code program using one of these routines. Or, in other words, these routines are constructors for the <a href="../c3ref/stmt.html">prepared statement</a> object.</p> <p>The preferred routine to use is <a href="../c3ref/prepare.html">sqlite3_prepare_v2()</a>. The <a href="../c3ref/prepare.html">sqlite3_prepare()</a> interface is legacy and should be avoided. <a href="../c3ref/prepare.html">sqlite3_prepare_v3()</a> has an extra "prepFlags" option that is used for special purposes.</p> <p>The use of the UTF-8 interfaces is preferred, as SQLite currently does all parsing using UTF-8. The UTF-16 interfaces are provided as a convenience. The UTF-16 interfaces work by converting the input text into UTF-8, then invoking the corresponding UTF-8 interface.</p> <p>The first argument, "db", is a <a href="../c3ref/sqlite3.html">database connection</a> obtained from a prior successful call to <a href="../c3ref/open.html">sqlite3_open()</a>, <a href="../c3ref/open.html">sqlite3_open_v2()</a> or <a href="../c3ref/open.html">sqlite3_open16()</a>. The database connection must not have been closed.</p> <p>The second argument, "zSql", is the statement to be compiled, encoded as either UTF-8 or UTF-16. The sqlite3_prepare(), sqlite3_prepare_v2(), and sqlite3_prepare_v3() interfaces use UTF-8, and sqlite3_prepare16(), sqlite3_prepare16_v2(), and sqlite3_prepare16_v3() use UTF-16.</p> <p>If the nByte argument is negative, then zSql is read up to the first zero terminator. If nByte is positive, then it is the maximum number of bytes read from zSql. When nByte is positive, zSql is read up to the first zero terminator or until the nByte bytes have been read, whichever comes first. If nByte is zero, then no prepared statement is generated. If the caller knows that the supplied string is nul-terminated, then there is a small performance advantage to passing an nByte parameter that is the number of bytes in the input string <i>including</i> the nul-terminator. Note that nByte measure the length of the input in bytes, not characters, even for the UTF-16 interfaces.</p> <p>If pzTail is not NULL then *pzTail is made to point to the first byte past the end of the first SQL statement in zSql. These routines only compile the first statement in zSql, so *pzTail is left pointing to what remains uncompiled.</p> <p>*ppStmt is left pointing to a compiled <a href="../c3ref/stmt.html">prepared statement</a> that can be executed using <a href="../c3ref/step.html">sqlite3_step()</a>. If there is an error, *ppStmt is set to NULL. If the input text contains no SQL (if the input is an empty string or a comment) then *ppStmt is set to NULL. The calling procedure is responsible for deleting the compiled SQL statement using <a href="../c3ref/finalize.html">sqlite3_finalize()</a> after it has finished with it. ppStmt may not be NULL.</p> <p>On success, the sqlite3_prepare() family of routines return <a href="../rescode.html#ok">SQLITE_OK</a>; otherwise an <a href="../rescode.html">error code</a> is returned.</p> <p>The sqlite3_prepare_v2(), sqlite3_prepare_v3(), sqlite3_prepare16_v2(), and sqlite3_prepare16_v3() interfaces are recommended for all new programs. The older interfaces (sqlite3_prepare() and sqlite3_prepare16()) are retained for backwards compatibility, but their use is discouraged. In the "vX" interfaces, the prepared statement that is returned (the <a href="../c3ref/stmt.html">sqlite3_stmt</a> object) contains a copy of the original SQL text. This causes the <a href="../c3ref/step.html">sqlite3_step()</a> interface to behave differently in three ways:</p> <p><ol> <li> If the database schema changes, instead of returning <a href="../rescode.html#schema">SQLITE_SCHEMA</a> as it always used to do, <a href="../c3ref/step.html">sqlite3_step()</a> will automatically recompile the SQL statement and try to run it again. As many as <a href="../compile.html#max_schema_retry">SQLITE_MAX_SCHEMA_RETRY</a> retries will occur before sqlite3_step() gives up and returns an error. </li></p> <p><li> When an error occurs, <a href="../c3ref/step.html">sqlite3_step()</a> will return one of the detailed <a href="../rescode.html">error codes</a> or <a href="../rescode.html#extrc">extended error codes</a>. The legacy behavior was that <a href="../c3ref/step.html">sqlite3_step()</a> would only return a generic <a href="../rescode.html#error">SQLITE_ERROR</a> result code and the application would have to make a second call to <a href="../c3ref/reset.html">sqlite3_reset()</a> in order to find the underlying cause of the problem. With the "v2" prepare interfaces, the underlying reason for the error is returned immediately. </li></p> <p><li> If the specific value bound to a <a href="../lang_expr.html#varparam">host parameter</a> in the WHERE clause might influence the choice of query plan for a statement, then the statement will be automatically recompiled, as if there had been a schema change, on the first <a href="../c3ref/step.html">sqlite3_step()</a> call following any change to the <a href="../c3ref/bind_blob.html">bindings</a> of that <a href="../lang_expr.html#varparam">parameter</a>. The specific value of a WHERE-clause <a href="../lang_expr.html#varparam">parameter</a> might influence the choice of query plan if the parameter is the left-hand side of a <a href="../lang_expr.html#like">LIKE</a> or <a href="../lang_expr.html#glob">GLOB</a> operator or if the parameter is compared to an indexed column and the <a href="../compile.html#enable_stat4">SQLITE_ENABLE_STAT4</a> compile-time option is enabled. </li> </ol></p> <p><p>sqlite3_prepare_v3() differs from sqlite3_prepare_v2() only in having the extra prepFlags parameter, which is a bit array consisting of zero or more of the <a href="../c3ref/c_prepare_dont_log.html#sqlitepreparepersistent">SQLITE_PREPARE_*</a> flags. The sqlite3_prepare_v2() interface works exactly the same as sqlite3_prepare_v3() with a zero prepFlags parameter. </p><p>See also lists of <a href="../c3ref/objlist.html">Objects</a>, <a href="../c3ref/constlist.html">Constants</a>, and <a href="../c3ref/funclist.html">Functions</a>.</p>