r38iztb6

1<cfscript>
2AMP = application.amp; // local var for brevity
3SQLite = AMP.getSQLite(); // load service for SQLite
4adminapi = CreateObject('component','cfide.adminapi.base'); // used for dump within cfscript
5		
6if (NOT AMP.localDBExists('testdb')) {
7   dbFile = SQLite.createDB('testdb',ExpandPath('db/'));
8} else {
9   dbFile = ExpandPath('db/testdb.db');
10}
11		
12// create table, this will drop the existing table by default if it exists
13SQLite.createTable(dbFile=dbFile,table_name='dog',columnlist='id,breed,name,description',primary_columns='id');
14		
15// execute a query and dump the empty table
16adminapi.dump(SQLite.executeSql(dbFile,"select * from dog"));
17		
18// create a prepared statement, also shows how a connection can be reused and manually closed.
19conn = SQLite.getConnection(dbFile);
20		
21prep = conn.prepareStatement("insert into dog (breed,name,description) values (?, ?, ?);");
22		
23prep.setString(1, "Shitzu");
24prep.setString(2, "Muffy");
25prep.setString(3, "An ill-tempered princess of a dog.");
26prep.addBatch();
27prep.setString(1, "Great Dane");
28prep.setString(2, "Turing");
29prep.setString(3, "A noble beast who has won many dog shows.");
30prep.addBatch();
31prep.setString(1, "German Shepherd");
32prep.setString(2, "Danke");
33prep.setString(3, "A white dog, named for the song 'Danke Shoen'.");
34prep.addBatch();
35		
36// disable autoCommit
37conn.setAutoCommit(false);
38prep.executeBatch();
39conn.setAutoCommit(true);
40	    
41// create a CF query from the db, reusing the connection object
42dogs = SQLite.executeSql(dbFile,"select * from dog",false,conn);
43	    
44adminapi.dump(dogs);
45			
46// create a SQLite table from a CF recordset, use existing dogs to create a 'mongrel' table, and reuse the existing connection
47		SQLite.convertQueryToTable(srcQuery=dogs,dbFile=dbFile,table_name='mongrel',primary_columns='id',closeConnection=false,connection=conn);
48		
49// close the connection
50conn.close();
51</cfscript>