raphipps2002 Active Member Licensed User Longtime User Jan 15, 2014 #1 I am new to SQL so please go easy! I have created a very simple DB in B4A using the Tutorial here - Code: SQL1.Initialize(File.DirDefaultExternal, "test1.db", True) and then Create a table... Code: SQL1.ExecNonQuery("CREATE TABLE table1 (col1 TEXT , col2 INTEGER, col3 INTEGER)") My app has an option to email the db to me and I wanted to write a vb.net app that reads the DB. However what I learned was that the tutorial in android avoids a integer PRIMARY KEY whereas in vb.net an id PRIMARY KEY seems necessary. I obtained the SQLlite library on vb from Finisar. Using SQL browser there is an 'id' in vb.net but in android there is not one. So when trying to read the android db via vb.net is says there is an unsupported format. I suppose therefore I need a similar primary key column one in my Android DB Can someone explain this to me please
I am new to SQL so please go easy! I have created a very simple DB in B4A using the Tutorial here - Code: SQL1.Initialize(File.DirDefaultExternal, "test1.db", True) and then Create a table... Code: SQL1.ExecNonQuery("CREATE TABLE table1 (col1 TEXT , col2 INTEGER, col3 INTEGER)") My app has an option to email the db to me and I wanted to write a vb.net app that reads the DB. However what I learned was that the tutorial in android avoids a integer PRIMARY KEY whereas in vb.net an id PRIMARY KEY seems necessary. I obtained the SQLlite library on vb from Finisar. Using SQL browser there is an 'id' in vb.net but in android there is not one. So when trying to read the android db via vb.net is says there is an unsupported format. I suppose therefore I need a similar primary key column one in my Android DB Can someone explain this to me please
mangojack Expert Licensed User Longtime User Jan 15, 2014 #2 What tutorial were you reading ?? If you haven't already , this might be worth viewing .. SQLite two simple SQlite projects ... both projects create dbs with a field ID Primary Key. example .. Code: SQL1.ExecNonQuery("CREATE TABLE table1 (ID INTEGER PRIMARY KEY, col2 TEXT , col3 TEXT, col4 INTEGER)") Cheers mj Last edited: Jan 15, 2014 Upvote 0
What tutorial were you reading ?? If you haven't already , this might be worth viewing .. SQLite two simple SQlite projects ... both projects create dbs with a field ID Primary Key. example .. Code: SQL1.ExecNonQuery("CREATE TABLE table1 (ID INTEGER PRIMARY KEY, col2 TEXT , col3 TEXT, col4 INTEGER)") Cheers mj
LucaMs Expert Licensed User Longtime User Jan 15, 2014 #3 Perhaps this might be useful to you too (not only Autoincrement): http://www.sqlite.org/autoinc.html Upvote 0
raphipps2002 Active Member Licensed User Longtime User Jan 16, 2014 #4 Thanks very much Mango and Luca...that is a really helpful site. It seems that the ROWID is the norm rather than exception, so why would the B4J tutorial omit this? Just a query not a critisism. SO, in order to write code to create a relational DB would I be correct in saying that If Table1, being customers, and contains 1) ROWID (2) Customer account number (3) Name would the protocol be to have a relationship with a Table2 containing Invoices for example (Columns being 1) ROWID etc)...then is the relationship with the ROWID in Table1 which is Unique or as in my example the Customer Account code which is unique? mangojack said: What tutorial were you reading ?? If you haven't already , this might be worth viewing .. SQLite two simple SQlite projects ... both projects create dbs with a field ID Primary Key. example .. Code: SQL1.ExecNonQuery("CREATE TABLE table1 (ID INTEGER PRIMARY KEY, col2 TEXT , col3 TEXT, col4 INTEGER)") Cheers mj Click to expand... Upvote 0
Thanks very much Mango and Luca...that is a really helpful site. It seems that the ROWID is the norm rather than exception, so why would the B4J tutorial omit this? Just a query not a critisism. SO, in order to write code to create a relational DB would I be correct in saying that If Table1, being customers, and contains 1) ROWID (2) Customer account number (3) Name would the protocol be to have a relationship with a Table2 containing Invoices for example (Columns being 1) ROWID etc)...then is the relationship with the ROWID in Table1 which is Unique or as in my example the Customer Account code which is unique? mangojack said: What tutorial were you reading ?? If you haven't already , this might be worth viewing .. SQLite two simple SQlite projects ... both projects create dbs with a field ID Primary Key. example .. Code: SQL1.ExecNonQuery("CREATE TABLE table1 (ID INTEGER PRIMARY KEY, col2 TEXT , col3 TEXT, col4 INTEGER)") Cheers mj Click to expand...
LucaMs Expert Licensed User Longtime User Jan 16, 2014 #5 ROWID in Customers is not automatic related to ROWID in Invoices. As you wrote, ROWID is ever present (with AutoIncrement or not). I think that the best way is (classic): Table Customers: ID (it replaces CustomerAccount) as Primary key (PK) Name ... Table Invoices: ID (PK) CustomerID as Foreign Key (FK) related to Customers.ID Date ... Table InvDetails: ID (PK) InvoiceID (FK to Invoices.ID) ... I mean, I do not hold account of the ROWID, even though, unfortunately, SQLite instead uses and indexes it Last edited: Jan 16, 2014 Upvote 0
ROWID in Customers is not automatic related to ROWID in Invoices. As you wrote, ROWID is ever present (with AutoIncrement or not). I think that the best way is (classic): Table Customers: ID (it replaces CustomerAccount) as Primary key (PK) Name ... Table Invoices: ID (PK) CustomerID as Foreign Key (FK) related to Customers.ID Date ... Table InvDetails: ID (PK) InvoiceID (FK to Invoices.ID) ... I mean, I do not hold account of the ROWID, even though, unfortunately, SQLite instead uses and indexes it
