[sqlite] sqlite3_column_decltype view union
Hi all, I have this view: CREATE VIEW MY_VIEW AS SELECT tip AS MYVIEW_TIP, abil AS MYVIEW_ABIL FROM table1 UNION SELECT tip,'S' FROM table2 I have defined columns with declared type 'CHAR' in the original tables If I use sqlite3_column_decltype to get my declared type I get CHAR for the first column but NULL for the latter, I think because in the last UNION the latter column is a constant 'S', even if it is defined as a table column reference in the first SELECT If I change my view: CREATE VIEW MY_VIEW AS SELECT tip AS MYVIEW_TIP, abil AS MYVIEW_ABIL FROM table1 UNION SELECT tip,abil FROM table2 using sqlite3_column_decltype returns CHAR as I want. Is it correct that SQLite uses the last UNION to get column type, then I have to prepare a last UNION with all column table reference to get the correct column type? I have several view from Oracle to convert into SQLite, have I to add a "dummy" final UNION with a WHERE clause that returns no rows to make SQLite works? Thanks in advanced. Luca - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Different ROWID and PRIMARY KEY values
Gerry Snyder ha scritto: Luca Moratto wrote: Thanks Gerry, but I can Create my table in memory from an attached db, I can Insert new rows and I can Select the new rows and all values are correct, except for my key field that is 0. My Insert statement is INSERT INTO myTable VALUES (?,?,?); I prepare this statement with sqlite3_prepare I bind all values with sqlite3_bind_int, sqlite3_bind_text, ... I bind my key field with sqlite3_bind_null I exec with sqlite3_step It works because if I exec e SELECT rowid,* FROM myTable I can find all the new rows and all values except for my key field (INTEGER PRIMARY KEY, I have tried also AUTOINCREMENT) that is 0, but rowid is correct I need to use a SELECT *, I can't get rowId so how and when my key field has the same value of rowId, why it does't works? Thanks in advanced Luke Luke, Sorry, I should not have even mentioned that part, since it obviously worked for you and it detracted from my main point, which you missed. When you create the table in the :memory: file by using CREATE TABLE AS, the PRIMARY KEY part of the field description gets dropped. Reread the last part of the example I posted. Or run select sql from sqlite_master yourself with the rest of your code. You have to create the :memory: copy of the table with a different method. I would suggest using the other format of the CREATE TABLE command and populating it with data from a SELECT. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] - Thanks Gerry and excuse me, I had not read your answer well. I have change my application: now first I read sql statements from attachedDb.sqlite_master where type='table'; then exec that sql to create tables, then I exec INSERT INTO 'mynewtable' AS SELECT * FROM 'attachedDb'.'mytable'. And now primary key works as expected! I already created all the indexes after table creation, but I believed that the constraint of primary key came copied also by "create table ... as ...)... Thanks Luke - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Different ROWID and PRIMARY KEY values
Thanks Gerry, but I can Create my table in memory from an attached db, I can Insert new rows and I can Select the new rows and all values are correct, except for my key field that is 0. My Insert statement is INSERT INTO myTable VALUES (?,?,?); I prepare this statement with sqlite3_prepare I bind all values with sqlite3_bind_int, sqlite3_bind_text, ... I bind my key field with sqlite3_bind_null I exec with sqlite3_step It works because if I exec e SELECT rowid,* FROM myTable I can find all the new rows and all values except for my key field (INTEGER PRIMARY KEY, I have tried also AUTOINCREMENT) that is 0, but rowid is correct I need to use a SELECT *, I can't get rowId so how and when my key field has the same value of rowId, why it does't works? Thanks in advanced Luke - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Different ROWID and PRIMARY KEY values
Hi, I'm using c++ interface for SQLite 3.3.8. 1) I open a db in memory, 2) attach a file with one table with a INTEGER PRIMARY KEY (ATTACH DATABASE 'SQliteFile.s3db' AS 'attachedDb'), 3) copy in memory the tble from attached db table (CREATE TABLE 'myTable' AS SELECT * FROM 'attachedDb.myTable') 4) exec in memory some INSERT with null value for the key (sqlite3_prepare,sqlite3_bind_... and sqlite3_bind_null for key field, sqlite3_step) Now if I Select both rowId and the key field I notice that the rowId has a correct value, but the key field has always a value of 0, while all other fileds are correct. But if I "write to file" using a DELETE FROM 'attachedDb.myTable' and INSERT INTO 'attachedDb.myTable' SELECT * FROM 'myTable' I can see rowId and key field with the same values in the attached database using SQLite Administrator. Why key filed hasn't the same value of rowId just after my Insert? Thanks in advance Luke - To unsubscribe, send email to [EMAIL PROTECTED] -
