I'll walk your through my process step by step so bear with me. So first I attach a sync SQLITE database to my main SQLITE database. Once that attach is complete, I then select all the columns I have available in my main database (This step here is to avoid any additional columns the sync SQLITE database might have that the main SQLITE database does not have.)
PRAGMA table_info('%@')
I then loop through all the columns and format that them into a string.
NSString * pragmaInfo = [NSString stringWithFormat:@"PRAGMA table_info('%@')",currentTable];
const char *tableInfo = [pragmaInfo UTF8String];
NSString * columns;
[columnString setString:@""];
NSString * lastColumn;
sqlite3_stmt * colStatement;
if (sqlite3_prepare(threadedMAINDB, tableInfo, -1, &colStatement, NULL)==SQLITE_OK) {
while (sqlite3_step(colStatement)== SQLITE_ROW) {
if (sqlite3_column_text(colStatement, 1)) {
columns = [NSString stringWithUTF8String:(char *)sqlite3_column_text(colStatement, 1)];
[columnString appendString:[NSString stringWithFormat:@"%@,",columns]];
lastColumn = columns;
}
}
sqlite3_finalize(colStatement);
Here's my current insert or update query. Mind you that it works.
//Where columnString is a prepared mutable string of all the columns available in the main SQLITE database
NSString *tblUpdate = [NSString stringWithFormat:@"INSERT or REPLACE INTO main.%@ (%@) SELECT %@ FROM sync_db.%@",currentTable, columnString,columnString,currentTable];
Now here's my question. Is there a way to grab all the available column names in a table without having to execute AND loop AND prepare a string to eventually plug into INSERT or REPLACE query? I was thinking something along the lines of this query:
select column_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'table';
I have tried this in my SELECT statement but SQLITE complains about my "." periods.
INSERT or REPLACE INTO main.table SELECT column_name FROM main.INFORMATION_SCHEMA.COLUMNS where sync.table_name = '%@'
Hopefully I am clear on this.
Aucun commentaire:
Enregistrer un commentaire