SQLite3_CRM.incl

/*
* Shows all Customer records in NSLog
*
* table:  person
* column: id
* column: type
* column: givenName
* column: middleName
* column: surName
*/


local fn GetAllCustomers as CFMutableArrayRef
'~'1
sqlite3_stmtPtr         statement
NSInteger               i
CFMutableDictionaryRef  aPerson
CFMutableArrayRef       people

if ( fn SQL3Open( fn URLPath( gDatabaseURL ), @gDatabasePtr ) == _SQLITEOK )

if ( fn SQL3PrepareV2( gDatabasePtr, @"SELECT id, type, givenName, middleName, surName FROM person", @statement ) == _SQLITEOK )
//~NSLogClear
i = 1

'Initiate the people array
people = fn CFArrayCreateMutable( _kCFAllocatorDefault, 0, @kCFTypeArrayCallBacks )

while ( fn SQL3Step( statement ) == _SQLITEROW )

'Initiate a person dictionary
aPerson = fn CFDictionaryCreateMutable( _kCFAllocatorDefault, NULL, @kCFCopyStringDictionaryKeyCallBacks, @kCFTypeDictionaryValueCallBacks )

fn CFDictionaryAddValue( aPerson, @"id",         fn SQL3ColumnInt( statement,  _firstCol  ))
fn CFDictionaryAddValue( aPerson, @"type",       fn SQL3ColumnText( statement, _secondCol ))
fn CFDictionaryAddValue( aPerson, @"givenName",  fn SQL3ColumnText( statement, _thirdCol  ))
fn CFDictionaryAddValue( aPerson, @"middleName", fn SQL3ColumnText( statement, _fourthCol ))
fn CFDictionaryAddValue( aPerson, @"surName",    fn SQL3ColumnText( statement, _fifthCol  ))

fn CFArrayAppendValue( people, aPerson )
fn CFRelease( aPerson )

i++

wend

fn SQL3Finalize( statement )
else
ControlSetStringValue( _status1Label, @"Query failed." )
end if
fn SQL3Close( gDatabasePtr )
else
ControlSetStringValue( _status1Label, @"Couldn't open database." )
end if

end fn = people


void local fn GetCustomers

CFMutableArrayRef people

'get and print an array JSON output
people = fn GetAllCustomers

//~NSLog( @"People :%@", people )
fn CFRelease( people )

end fn


local mode
dim as CFMutableDictionaryRef   queryDictionaryRef

local fn SelectAllPersonQuery as CFMutableDictionaryRef
'~'1
queryDictionaryRef = fn InitQueryDictionary
fn AddTableToQuery( queryDictionaryRef, @"person", @"a" )
fn AddColumnToQuery( queryDictionaryRef, @"id", @"ID" )
fn AddColumnToQuery( queryDictionaryRef, @"type", @"Type" )
fn AddColumnToQuery( queryDictionaryRef, @"givenName", @"'Given Name'" )
fn AddColumnToQuery( queryDictionaryRef, @"middleName", @"'Middle Name'" )
fn AddColumnToQuery( queryDictionaryRef, @"surName", @"'Sur Name'" )
fn AddColumnToQuery( queryDictionaryRef, @"balance", @"Balance" )
fn AddFilterToQuery( queryDictionaryRef, @"a", @"givenName", @"Thomas", _textType, NULL, _isEqual, NULL )

end fn = queryDictionaryRef

local mode
dim as CFMutableDictionaryRef   queryDictionaryRef

local fn SelectPersonByIDQuery ( anID as CFStringRef ) as CFMutableDictionaryRef
'~'1
queryDictionaryRef = fn InitQueryDictionary
fn AddTableToQuery( queryDictionaryRef, @"person", @"a" )
fn AddColumnToQuery( queryDictionaryRef, @"id", @"ID" )
fn AddColumnToQuery( queryDictionaryRef, @"type", @"Type" )
fn AddColumnToQuery( queryDictionaryRef, @"givenName", @"'Given Name'" )
fn AddColumnToQuery( queryDictionaryRef, @"middleName", @"'Middle Name'" )
fn AddColumnToQuery( queryDictionaryRef, @"surName", @"'Sur Name'" )
fn AddColumnToQuery( queryDictionaryRef, @"balance", @"Balance" )
fn AddFilterToQuery( queryDictionaryRef, @"a", @"id", anID, _integerType, NULL, _isEqual, NULL )

end fn = queryDictionaryRef

local mode
dim as CFMutableDictionaryRef   queryDictionaryRef

local fn SelectPersonAddressesQuery ( anID as CFStringRef ) as CFMutableDictionaryRef
'~'1
queryDictionaryRef = fn InitQueryDictionary
fn AddTableToQuery( queryDictionaryRef, @"address", @"a" )
fn AddColumnToQuery( queryDictionaryRef, @"id", @"ID" )
fn AddColumnToQuery( queryDictionaryRef, @"type", @"Type" )
fn AddColumnToQuery( queryDictionaryRef, @"personID", @"'Person ID'" )
fn AddColumnToQuery( queryDictionaryRef, @"street", @"Street" )
fn AddColumnToQuery( queryDictionaryRef, @"apartment", @"Apartment" )
fn AddColumnToQuery( queryDictionaryRef, @"city", @"City" )
fn AddColumnToQuery( queryDictionaryRef, @"state", @"State" )
fn AddColumnToQuery( queryDictionaryRef, @"zip", @"Zip" )
fn AddFilterToQuery( queryDictionaryRef, @"a", @"personID", anID, _integerType, NULL, _isEqual, NULL )

end fn = queryDictionaryRef

local mode
dim as CFMutableDictionaryRef   queryDictionaryRef

local fn SelectPersonContactsQuery ( anID as CFStringRef ) as CFMutableDictionaryRef
'~'1
queryDictionaryRef = fn InitQueryDictionary
fn AddTableToQuery( queryDictionaryRef, @"contact", @"a" )
fn AddColumnToQuery( queryDictionaryRef, @"id", @"ID" )
fn AddColumnToQuery( queryDictionaryRef, @"type", @"Type" )
fn AddColumnToQuery( queryDictionaryRef, @"personID", @"'Person ID'" )
fn AddColumnToQuery( queryDictionaryRef, @"contact", @"Contact" )
fn AddFilterToQuery( queryDictionaryRef, @"a", @"personID", anID, _integerType, NULL, _isEqual, NULL )

end fn = queryDictionaryRef


void local fn PopulatePersonView( resultsDictionaryRef as CFDictionaryRef )

if( resultsDictionaryRef != NULL )

EnumeratorRef enumerator = fn ArrayObjectEnumerator( fn DictionaryValueForKey( resultsDictionaryRef, @"data" ))
CFTypeRef obj = fn EnumeratorNextObject( enumerator )

while ( obj )

fn PopUpButtonSetTitle(   _personTypePopup,  fn DictionaryValueForKey( obj, @"Type"        ))
fn ControlSetStringValue( _personGivenName,  fn DictionaryValueForKey( obj, @"Given Name"  ))
fn ControlSetStringValue( _personMiddleName, fn DictionaryValueForKey( obj, @"Middle Name" ))
fn ControlSetStringValue( _personSurName,    fn DictionaryValueForKey( obj, @"Sur Name"    ))
fn ControlSetStringValue( _personBalance,    fn DictionaryValueForKey( obj, @"Balance"     ))

obj = fn EnumeratorNextObject( enumerator )

wend
end if

end fn


local fn AddAddressRecord
'~'1
sqlite3_stmtPtr statement
CFStringRef     query, type, address1, address2, city, state, postalCode

type       = fn ControlStringValue( _addressTypePopup  )
address1   = fn ControlStringValue( _addressStreet     )
address2   = fn ControlStringValue( _addressApt        )
city       = fn ControlStringValue( _addressCity       )
state      = fn ControlStringValue( _addressStatePopup )
postalCode = fn ControlStringValue( _addressPostalCode )

if ( fn SQL3Open( fn URLPath( gDatabaseURL ), @gDatabasePtr ) == _SQLITEOK )
query = fn StringWithFormat(  @"INSERT INTO CONTACTS (type, street, apartment, city, state, zip) VALUES (\"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\")", type, address1, address2, city, state, postalCode )
fn SQL3PrepareV2( gDatabasePtr, query, @statement )
if ( fn SQL3Step( statement ) == _SQLITEDONE )
//~NSLog( @"Contact added." )
else
//~NSLog(  @"Failed to add contact." )
end if
fn SQL3Finalize( statement )
fn SQL3Close( gDatabasePtr )
end if
end fn


// Controls the sheet window. Cancel button causes sheet window fields to populate with data from main window fields. Haven't a clue why.
local fn DoSheetAddRecord
'~'1
fn AddAddressRecord

window output _window
' ControlSetStringValue( _status2Label, fn StringWithFormat( @"There are %d records in the SQL database.", fn RecordsInSQLDatabase ))
' StepperSetMaxValue( _stepper, fn RecordsInSQLDatabase )
' WindowEndSheet( _window, _recordSheetWindow )

end fn


void local fn PopulateAddressesView( resultsDictionaryRef as CFDictionaryRef )

if( resultsDictionaryRef != NULL )

EnumeratorRef enumerator = fn ArrayObjectEnumerator( fn DictionaryValueForKey( resultsDictionaryRef, @"data" ))
CFTypeRef obj = fn EnumeratorNextObject( enumerator )

while ( obj )

fn PopUpButtonSetTitle(   _addressTypePopup,  fn DictionaryValueForKey( obj, @"Type"      ))
fn ControlSetStringValue( _addressStreet,     fn DictionaryValueForKey( obj, @"Street"    ))
fn ControlSetStringValue( _addressApt,        fn DictionaryValueForKey( obj, @"Apartment" ))
fn ControlSetStringValue( _addressCity,       fn DictionaryValueForKey( obj, @"City"      ))
fn PopUpButtonSetTitle(   _addressStatePopup, fn DictionaryValueForKey( obj, @"State"     ))
fn ControlSetStringValue( _addressPostalCode, fn DictionaryValueForKey( obj, @"Zip"       ))

obj = fn EnumeratorNextObject( enumerator )

wend
end if

end fn


void local fn PopulateContactsView( resultsDictionaryRef as CFDictionaryRef )

if( resultsDictionaryRef != NULL )

EnumeratorRef enumerator = fn ArrayObjectEnumerator( fn DictionaryValueForKey( resultsDictionaryRef, @"data" ))
CFTypeRef obj = fn EnumeratorNextObject( enumerator )

while ( obj )

fn PopUpButtonSetTitle(   _contactTypePopup, fn DictionaryValueForKey( obj, @"Type"    ))
fn ControlSetStringValue( _contactContact,   fn DictionaryValueForKey( obj, @"Contact" ))

obj = fn EnumeratorNextObject( enumerator )

wend
end if

end fn


local fn CreateEmptySQLDatabase
'~'1
CFStringRef statement

if ( fn FileManagerFileExistsAtURL( gDatabaseURL ) == _false )
if ( fn SQL3Open( fn URLPath( gDatabaseURL ), @gDatabasePtr ) == _SQLITEOK )

// Create sequences
statement = @"PRAGMA foreign_keys=OFF;"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed to create CONTACT table." )
end if
statement = @"BEGIN TRANSACTION;"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed to begin transaction." )
end if


// Create table ADDRESS
statement = @"CREATE TABLE 'address' ('id' INTEGER PRIMARY KEY ASC AUTOINCREMENT, 'personID' INTEGER REFERENCES person (id), 'type' TEXT, 'street' TEXT, 'apartment' TEXT, 'city' TEXT, 'state' TEXT, 'zip' TEXT);"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed to create address table." )
end if
statement = @"INSERT INTO 'address' VALUES(1,1,'Business','71 Valley View Ln.','','Smithville','PA','17022');"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed insert into address table." )
end if
statement = @"INSERT INTO 'address' VALUES(2,NULL,'Business','3 E. Leatherwood Dr.','','Hopewell','VA','23860');"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed insert into address table." )
end if
statement = @"INSERT INTO 'address' VALUES(3,NULL,'Business','54 Pin Oak St.','','Stillwater','MN','55082');"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed insert into address table." )
end if
statement = @"INSERT INTO 'address' VALUES(4,NULL,'Business','414 Walt Whitman St.','','Macomb','MI','48042');"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed insert into address table." )
end if
statement = @"INSERT INTO 'address' VALUES(5,NULL,'Business','79 E. Corona Ave.','','Milwaukee','WI','53204');"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed insert into address table." )
end if
statement = @"INSERT INTO 'address' VALUES(6,NULL,'Business','260 Foxrun Dr.','Apt. 102','Warren','FL','48089');"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed insert into address table." )
end if

// Create table CONTACT
statement = @"CREATE TABLE 'contact' ('id' INTEGER PRIMARY KEY ASC AUTOINCREMENT, 'personID' INTEGER REFERENCES person (id), 'type' TEXT, 'contact' TEXT);"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed to create contact table." )
end if
statement = @"INSERT INTO 'contact' VALUES(1,1,'Email', 'testing123@test.com');"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed insert into contact table." )
end if
statement = @"INSERT INTO 'contact' VALUES(2,1,'Phone', '212-232-3434');"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed insert into contact table." )
end if

// Create table PERSON
statement = @"CREATE TABLE 'person' ('id' INTEGER PRIMARY KEY ASC AUTOINCREMENT, 'type' TEXT, 'givenName' TEXT, 'middleName' TEXT, 'surName' TEXT);"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed to create person table." )
end if
statement = @"INSERT INTO 'person' VALUES(1,'Business','Thomas','George','Peters');"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed insert into person table." )
end if
statement = @"PRAGMA writable_schema=OFF;"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed to create ADDRESS table." )
end if
statement = @"PRAGMA foreign_keys=ON;"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed to create ADDRESS table." )
end if
statement = @"COMMIT;"
if ( fn SQL3Exec( gDatabasePtr, statement ) != _SQLITEOK )
//~NSLog( @"Failed to create ADDRESS table." )
end if
fn SQL3Close( gDatabasePtr )
else
//~NSLog( @"Couldn't open database" )
end if
else
//~NSLog( @"Database already exists" )
end if
end fn


local fn LoadInitialData
'~'1
sqlite3_stmtPtr  statement
CFDictionaryRef  dict1, dict2, dict3, dict4, dict5, dict6, tempDict
CFArrayRef       arr
CFStringRef      name, surname, address, city, state, zip, phone, query
NSUInteger       i, count

// Demo IDs are fake
dict1 = @{ @"name":@"Hannah", @"surname":@"Fisher",   @"address":@"71 Valley View Ln.",   @"city":@"Smithville", @"state":@"PA", @"zip":@"17022", @"phone":@"925-836-1546" }
dict2 = @{ @"name":@"George", @"surname":@"Heaton",   @"address":@"3 E. Leatherwood Dr.", @"city":@"Hopewell",   @"state":@"VA", @"zip":@"23860", @"phone":@"876-410-9448" }
dict3 = @{ @"name":@"Susan",  @"surname":@"Horner",   @"address":@"54 Pin Oak St.",       @"city":@"Stillwater", @"state":@"MN", @"zip":@"55082", @"phone":@"361-257-2924" }
dict4 = @{ @"name":@"Ted",    @"surname":@"Vasquez",  @"address":@"414 Walt Whitman St.", @"city":@"Macomb",     @"state":@"MI", @"zip":@"48042", @"phone":@"281-674-9390" }
dict5 = @{ @"name":@"Mandy",  @"surname":@"Fletcher", @"address":@"79 E. Corona Ave.",    @"city":@"Milwaukee",  @"state":@"WI", @"zip":@"53204", @"phone":@"524-846-7348" }
dict6 = @{ @"name":@"Fred",   @"surname":@"White",    @"address":@"260 Foxrun Dr.",       @"city":@"Warren",     @"state":@"FL", @"zip":@"48089", @"phone":@"270-293-6236" }

arr = @[dict1, dict2, dict3, dict4, dict5, dict6]

if ( fn SQL3Open( fn URLPath( gDatabaseURL ), @gDatabasePtr ) == _SQLITEOK )
count = fn ArrayCount( arr )
for i = 0 to count -1
tempDict = fn ArrayObjectAtIndex( arr, i )

name    = fn DictionaryValueForKey( tempDict, @"name"    )
surname = fn DictionaryValueForKey( tempDict, @"surname" )
address = fn DictionaryValueForKey( tempDict, @"address" )
city    = fn DictionaryValueForKey( tempDict, @"city"    )
state   = fn DictionaryValueForKey( tempDict, @"state"   )
zip     = fn DictionaryValueForKey( tempDict, @"zip"     )
phone   = fn DictionaryValueForKey( tempDict, @"phone"   )

query = fn StringWithFormat(  @"INSERT INTO CONTACTS (name, surname, address, city, state, zip, phone) VALUES (\"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\")", name, surname, address, city, state, zip, phone )
fn SQL3PrepareV2( gDatabasePtr, query, @statement )
fn SQL3Step( statement )
fn SQL3Finalize( statement )
next
ControlSetStringValue( _status1Label, @"Database ""contacts.db"" is on Desktop ready for entries." )
fn SQL3Close( gDatabasePtr )
else
ControlSetStringValue( _status1Label, @"Error populating database ""contacts.db.""" )
end if
end fn
/*
  Proof of concept only. Interface is abominable and currently only finds first record. To use, enter text you want to find in appropriate field and click Find button.
  Example, in the "State" field, enter MI (abbreviation for Michigan — case sensitive) and click Find button. It will return first record that has MI in it.
  Search text must be exactly as it appears in the database record.
*/
local fn FindContact
'~'1
sqlite3_stmtPtr statement = NULL
CFStringRef     query

if ( fn FileManagerFileExistsAtURL( gDatabaseURL ) == _false )
ControlSetStringValue( _status1Label, @"Database missing. Relaunch app to create new database." )
exit fn
else
if ( fn SQL3Open( fn URLPath( gDatabaseURL ), @gDatabasePtr ) == _SQLITEOK )

select case ( fn WindowFirstResponder( _window ) )

case _firstNameField
query = fn StringWithFormat( @"SELECT surname, address, city, state, zip, phone FROM contacts WHERE name=\"%@\"", fn ControlStringValue( _firstNameField ))
fn SQL3PrepareV2( gDatabasePtr, query, @statement )
if ( fn SQL3Step( statement ) == _SQLITEROW )

ControlSetStringValue( _status1Label, @"Match found." )

ControlSetStringValue( _surnameField, fn SQL3ColumnText( statement, _firstCol  ))
ControlSetStringValue( _addressField, fn SQL3ColumnText( statement, _secondCol ))
ControlSetStringValue( _cityField,    fn SQL3ColumnText( statement, _thirdCol  ))
ControlSetStringValue( _stateField,   fn SQL3ColumnText( statement, _fourthCol ))
ControlSetStringValue( _zipField,     fn SQL3ColumnText( statement, _fifthCol  ))
ControlSetStringValue( _phoneField,   fn SQL3ColumnText( statement, _sixthCol  ))
else
' fn ClearFields
end if
fn SQL3Finalize( statement )

case _surnameField
query = fn StringWithFormat( @"SELECT name, address, city, state, zip, phone FROM contacts WHERE surname=\"%@\"", fn ControlStringValue( _surnameField ))
fn SQL3PrepareV2( gDatabasePtr, query, @statement )
if ( fn SQL3Step( statement ) == _SQLITEROW )

ControlSetStringValue( _status1Label, @"Match found." )

ControlSetStringValue( _firstNameField, fn SQL3ColumnText( statement, _firstCol  ))
ControlSetStringValue( _addressField,   fn SQL3ColumnText( statement, _secondCol ))
ControlSetStringValue( _cityField,      fn SQL3ColumnText( statement, _thirdCol  ))
ControlSetStringValue( _stateField,     fn SQL3ColumnText( statement, _fourthCol ))
ControlSetStringValue( _zipField,       fn SQL3ColumnText( statement, _fifthCol  ))
ControlSetStringValue( _phoneField,     fn SQL3ColumnText( statement, _sixthCol  ))
else
' fn ClearFields
end if
fn SQL3Finalize( statement )

case _addressField
query = fn StringWithFormat( @"SELECT name, surname, city, state, zip, phone FROM contacts WHERE address=\"%@\"", fn ControlStringValue( _addressField ))
fn SQL3PrepareV2( gDatabasePtr, query, @statement )
if ( fn SQL3Step( statement ) == _SQLITEROW )

ControlSetStringValue( _status1Label, @"Match found." )

ControlSetStringValue( _firstNameField, fn SQL3ColumnText( statement, _firstCol  ))
ControlSetStringValue( _surnameField,   fn SQL3ColumnText( statement, _secondCol ))
ControlSetStringValue( _cityField,      fn SQL3ColumnText( statement, _thirdCol  ))
ControlSetStringValue( _stateField,     fn SQL3ColumnText( statement, _fourthCol ))
ControlSetStringValue( _zipField,       fn SQL3ColumnText( statement, _fifthCol  ))
ControlSetStringValue( _phoneField,     fn SQL3ColumnText( statement, _sixthCol  ))
else
' fn ClearFields
end if
fn SQL3Finalize( statement )

case _cityField
query = fn StringWithFormat( @"SELECT name, surname, address, state, zip, phone FROM contacts WHERE city=\"%@\"", fn ControlStringValue( _cityField ))
fn SQL3PrepareV2( gDatabasePtr, query, @statement )
if ( fn SQL3Step( statement ) == _SQLITEROW )

ControlSetStringValue( _status1Label, @"Match found." )

ControlSetStringValue( _firstNameField, fn SQL3ColumnText( statement, _firstCol  ))
ControlSetStringValue( _surnameField,   fn SQL3ColumnText( statement, _secondCol ))
ControlSetStringValue( _addressField,   fn SQL3ColumnText( statement, _thirdCol  ))
ControlSetStringValue( _stateField,     fn SQL3ColumnText( statement, _fourthCol ))
ControlSetStringValue( _zipField,       fn SQL3ColumnText( statement, _fifthCol  ))
ControlSetStringValue( _phoneField,     fn SQL3ColumnText( statement, _sixthCol  ))
else
' fn ClearFields
end if
fn SQL3Finalize( statement )

case _stateField
query = fn StringWithFormat( @"SELECT name, surname, address, city, zip, phone FROM contacts WHERE state=\"%@\"", fn ControlStringValue( _stateField ))
fn SQL3PrepareV2( gDatabasePtr, query, @statement )
if ( fn SQL3Step( statement ) == _SQLITEROW )

ControlSetStringValue( _status1Label, @"Match found." )

ControlSetStringValue( _firstNameField, fn SQL3ColumnText( statement, _firstCol  ))
ControlSetStringValue( _surnameField,   fn SQL3ColumnText( statement, _secondCol ))
ControlSetStringValue( _addressField,   fn SQL3ColumnText( statement, _thirdCol  ))
ControlSetStringValue( _cityField,      fn SQL3ColumnText( statement, _fourthCol ))
ControlSetStringValue( _zipField,       fn SQL3ColumnText( statement, _fifthCol  ))
ControlSetStringValue( _phoneField,     fn SQL3ColumnText( statement, _sixthCol  ))
else
' fn ClearFields
end if
fn SQL3Finalize( statement )

case _zipField
query = fn StringWithFormat( @"SELECT name, surname, address, city, state, phone FROM contacts WHERE zip=\"%@\"", fn ControlStringValue( _zipField ))
fn SQL3PrepareV2( gDatabasePtr, query, @statement )
if ( fn SQL3Step( statement ) == _SQLITEROW )

ControlSetStringValue( _status1Label, @"Match found." )

ControlSetStringValue( _firstNameField, fn SQL3ColumnText( statement, _firstCol  ))
ControlSetStringValue( _surnameField,   fn SQL3ColumnText( statement, _secondCol ))
ControlSetStringValue( _addressField,   fn SQL3ColumnText( statement, _thirdCol  ))
ControlSetStringValue( _cityField,      fn SQL3ColumnText( statement, _fourthCol ))
ControlSetStringValue( _stateField,     fn SQL3ColumnText( statement, _fifthCol  ))
ControlSetStringValue( _phoneField,     fn SQL3ColumnText( statement, _sixthCol  ))
else
' fn ClearFields
end if
fn SQL3Finalize( statement )

case _phoneField
query = fn StringWithFormat( @"SELECT name, surname, address, city, state, zip FROM contacts WHERE phone=\"%@\"", fn ControlStringValue( _phoneField ))
fn SQL3PrepareV2( gDatabasePtr, query, @statement )
if ( fn SQL3Step( statement ) == _SQLITEROW )

ControlSetStringValue( _status1Label, @"Match found." )

ControlSetStringValue( _firstNameField, fn SQL3ColumnText( statement, _firstCol  ))
ControlSetStringValue( _surnameField,   fn SQL3ColumnText( statement, _secondCol ))
ControlSetStringValue( _addressField,   fn SQL3ColumnText( statement, _thirdCol  ))
ControlSetStringValue( _cityField,      fn SQL3ColumnText( statement, _fourthCol ))
ControlSetStringValue( _stateField,     fn SQL3ColumnText( statement, _fifthCol  ))
ControlSetStringValue( _zipField,       fn SQL3ColumnText( statement, _sixthCol  ))
else
' fn ClearFields
end if
fn SQL3Finalize( statement )
end select
end if
fn SQL3Close( gDatabasePtr )
end if
end fn


/*
 * this is my SQL test app
 *
 * queryResults   <-- the return type
 *      |
 *      |___ resultsColumnNamesArrayRef        <-- an array of Column Names
 *      |
 *      |___ resultsMetadata                   <-- a Dictionary of Col Data Types     e.g. the Type of Data for each Column in a Row in the results
 *      |
 *      |___ resultsDataArrayRef               <-- an array of Row Data               e.g. each element in the array every row in the result set
 *                   |
 *                   |___ resultsColumnData    <-- a Dictionary of Col Data Types     e.g. the Data for each Column in a Row in the results
 */

local fn TestSQLite
'~'1

dim as CFMutableDictionaryRef   queryDictionaryRef, resultsDictionaryRef, resultsColumnData
dim as CFMutableArrayRef        tableArray
dim as EnumeratorRef            resultsEnumerator
dim as CFMutableStringRef       mySQLString
dim as sqlite3_stmtPtr          statement

if ( gDBIsConnected )

queryDictionaryRef = fn SelectAllPersonQuery
mySQLString = fn GenerateSelectSQL( queryDictionaryRef )

statement = fn BuildPreparedStatement_Select( gDatabasePtr, mySQLString, fn GetSelectFilterArray( queryDictionaryRef ))
if ( statement != NULL )
resultsDictionaryRef = fn ExecutePreparedStatement( statement, queryDictionaryRef )

if ( resultsDictionaryRef != NULL )

tableArray = fn TableViewData( _contactListView )

if tableArray != NULL
resultsEnumerator = fn ArrayObjectEnumerator( (CFMutableArrayRef) fn CFDictionaryGetValue( resultsDictionaryRef, @"data" ))
resultsColumnData = (CFMutableDictionaryRef) fn EnumeratorNextObject( resultsEnumerator )

while ( resultsColumnData )

fn MutableDictionarySetObjectForKey( resultsColumnData, fn StringWithFormat( @"%@ %@", fn CFDictionaryGetValue( resultsColumnData, @"Given Name" ), fn CFDictionaryGetValue( resultsColumnData, @"Sur Name" ) ), @"Full Name" )
fn MutableArrayAddObject( tableArray, resultsColumnData )

'fn NSLog( @"Row Data: %@" , resultsColumnData )

resultsColumnData = (CFMutableDictionaryRef)fn EnumeratorNextObject( resultsEnumerator )

wend

TableViewReloadData( _contactListView )

end if
end if

else
fn NSLog( @"statement is NULL" )
end if


'if ( resultsDictionaryRef != NULL ) then fn NSLog( @"SQL Results: %@", resultsDictionaryRef )

fn CFRelease( queryDictionaryRef )		'release the query dict reference
fn CFRelease( mySQLString )						'release the string reference
'fn CFRelease( resultsDictionaryRef )		'release the results dict reference

else
fn NSLog( @"Could not reopen the DB" )
end if

' fn SQL3Close( gDatabasePtr )

end fn


/*
 * this is my SQL test app
 *
 * queryResults   <-- the return type
 *      |
 *      |___ resultsColumnNamesArrayRef        <-- an array of Column Names
 *      |
 *      |___ resultsMetadata                   <-- a Dictionary of Col Data Types     e.g. the Type of Data for each Column in a Row in the results
 *      |
 *      |___ resultsDataArrayRef               <-- an array of Row Data               e.g. each element in the array every row in the result set
 *                   |
 *                   |___ resultsColumnData    <-- a Dictionary of Col Data Types     e.g. the Data for each Column in a Row in the results
 */

local fn GetAllPersonRecords as CFMutableDictionaryRef
'~'1

dim as CFMutableDictionaryRef   queryDictionaryRef, resultsDictionaryRef
dim as CFMutableStringRef       mySQLString
dim as sqlite3_stmtPtr          statement

if ( fn SQL3Open( fn URLPath( gDatabaseURL ), @gDatabasePtr ) == _SQLITEOK )

queryDictionaryRef = fn SelectAllPersonQuery
mySQLString = fn GenerateSelectSQL( queryDictionaryRef )

statement = fn BuildPreparedStatement_Select( gDatabasePtr, mySQLString, fn GetSelectFilterArray( queryDictionaryRef ))
if ( statement != NULL )
resultsDictionaryRef = fn ExecutePreparedStatement( statement, queryDictionaryRef )

else
fn NSLog( @"statement is NULL" )
end if

fn CFRelease( queryDictionaryRef )		'release the query dict reference
fn CFRelease( mySQLString )						'release the string reference

else
fn NSLog( @"Could not reopen the DB" )
end if

fn SQL3Close( gDatabasePtr )

end fn = resultsDictionaryRef

/*
 * this is my SQL test app
 *
 * queryResults   <-- the return type
 *      |
 *      |___ resultsColumnNamesArrayRef        <-- an array of Column Names
 *      |
 *      |___ resultsMetadata                   <-- a Dictionary of Col Data Types     e.g. the Type of Data for each Column in a Row in the results
 *      |
 *      |___ resultsDataArrayRef               <-- an array of Row Data               e.g. each element in the array every row in the result set
 *                   |
 *                   |___ resultsColumnData    <-- a Dictionary of Col Data Types     e.g. the Data for each Column in a Row in the results
 */

local fn GetPersonByIDRecord( anID as CFStringRef ) as CFMutableDictionaryRef
'~'1

dim as CFMutableDictionaryRef   queryDictionaryRef, resultsDictionaryRef
dim as CFMutableStringRef       mySQLString
dim as sqlite3_stmtPtr          statement

fn NSLog( @"GetPersonByIDRecord %@", anID )

queryDictionaryRef = fn SelectPersonByIDQuery( anID )
mySQLString = fn GenerateSelectSQL( queryDictionaryRef )

fn NSLog( @"SQL Select %@", mySQLString )

if ( gDBIsConnected )

statement = fn BuildPreparedStatement_Select( gDatabasePtr, mySQLString, fn GetSelectFilterArray( queryDictionaryRef ))
resultsDictionaryRef = fn ExecutePreparedStatement( statement, queryDictionaryRef )
fn NSLog( @"Person %@", resultsDictionaryRef )

else
fn NSLog( @"Database is not connected!" )
end if

end fn = resultsDictionaryRef

/*
 * this is my SQL test app
 *
 * queryResults   <-- the return type
 *      |
 *      |___ resultsColumnNamesArrayRef        <-- an array of Column Names
 *      |
 *      |___ resultsMetadata                   <-- a Dictionary of Col Data Types     e.g. the Type of Data for each Column in a Row in the results
 *      |
 *      |___ resultsDataArrayRef               <-- an array of Row Data               e.g. each element in the array every row in the result set
 *                   |
 *                   |___ resultsColumnData    <-- a Dictionary of Col Data Types     e.g. the Data for each Column in a Row in the results
 */

local fn GetAddressesByPersonIDRecord( anID as CFStringRef ) as CFMutableDictionaryRef
'~'1

dim as CFMutableDictionaryRef   queryDictionaryRef, resultsDictionaryRef
dim as CFMutableStringRef       mySQLString
dim as sqlite3_stmtPtr          statement

fn NSLog( @"GetAddressByPersonIDRecord %@", anID )

queryDictionaryRef = fn SelectPersonAddressesQuery( anID )
mySQLString = fn GenerateSelectSQL( queryDictionaryRef )

fn NSLog( @"Address SQL Select %@", mySQLString )

if ( gDBIsConnected )

statement = fn BuildPreparedStatement_Select( gDatabasePtr, mySQLString, fn GetSelectFilterArray( queryDictionaryRef ))
resultsDictionaryRef = fn ExecutePreparedStatement( statement, queryDictionaryRef )
fn NSLog( @"Address %@", resultsDictionaryRef )

else
fn NSLog( @"Database is not connected!" )
end if

end fn = resultsDictionaryRef

/*
 * this is my SQL test app
 *
 * queryResults   <-- the return type
 *      |
 *      |___ resultsColumnNamesArrayRef        <-- an array of Column Names
 *      |
 *      |___ resultsMetadata                   <-- a Dictionary of Col Data Types     e.g. the Type of Data for each Column in a Row in the results
 *      |
 *      |___ resultsDataArrayRef               <-- an array of Row Data               e.g. each element in the array every row in the result set
 *                   |
 *                   |___ resultsColumnData    <-- a Dictionary of Col Data Types     e.g. the Data for each Column in a Row in the results
 */

local fn GetContactsByPersonIDRecord( anID as CFStringRef ) as CFMutableDictionaryRef
'~'1

dim as CFMutableDictionaryRef   queryDictionaryRef, resultsDictionaryRef
dim as CFMutableStringRef       mySQLString
dim as sqlite3_stmtPtr          statement

fn NSLog( @"GetContactsByPersonIDRecord %@", anID )

queryDictionaryRef = fn SelectPersonContactsQuery( anID )
mySQLString = fn GenerateSelectSQL( queryDictionaryRef )

fn NSLog( @"Contact Select SQL %@", mySQLString )

if ( gDBIsConnected )

statement = fn BuildPreparedStatement_Select( gDatabasePtr, mySQLString, fn GetSelectFilterArray( queryDictionaryRef ))
resultsDictionaryRef = fn ExecutePreparedStatement( statement, queryDictionaryRef )
fn NSLog( @"Contact %@", resultsDictionaryRef )

else
fn NSLog( @"Database is not connected!" )
end if

end fn = resultsDictionaryRef