Insert column values at database creation time?

Databases
1

Hi,
I use the code below to create a simple 1 column table:

mydb.SQLExecute("CREATE TABLE Types(Type STRING PRIMARY KEY)")

Do I simply use my code above just to create the database and column, and then have to connect to it and populate the column with 2 entries, as per usual - OR - can the column actually be populated at creation time, in the same line of code above?

I need to add 2 entries to the column, and was hoping that I could modify my line of code above to populate the column at creation time (as opposed to having to connect and then populate etc.).

Thank you in advance.

2

db.SQLExecute(“CREATE TABLE Types(Type STRING PRIMARY KEY); INSERT INTO Types (Type) VALUES (‘Value1’); INSERT INTO Types (Type) VALUES (‘Value2’);”)

Keep in mind it is always better to check for an error after each db action.

3

Thanks Peter, that’s exactly what I needed. :slight_smile:

4

Just out of curiosity - is this line of code invalid regarding inserting 22 values into the same column?

I keep getting an Unable to Prepare Statement error.

Dim ps As SQLitePreparedStatement = IncidentsDB.Prepare("INSERT INTO Types (Type) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);")
5

One record can’t contain 22 values for the same column. You would need 22 records.

Dim ps As SQLitePreparedStatement = IncidentsDB.Prepare("INSERT INTO Types (Type) VALUES (?);") ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) ps.Execute("val1") ps.Execute("val2") ps.Execute("val3") ps.Execute("val4") ps.Execute("val5") ps.Execute("val6") ps.Execute("val7") ps.Execute("val8") ps.Execute("val9")

6

Create a string constant that is ALL the sql you need to create & populate the DB initially
You can create this by hand to make sure it all works
Then just do something like

dim lines() as string = Split( replacelineendings(constant, endofline), endofline )
 
for each line as string in lines
    mydb.SQLExecute( line )
next

this is one of the VERY few places where I might skip error checking because if you have an error in your initial set up & data load that you missed in testing well … public flogging

7

Tim - thanks.
Your syntax needed correcting slightly though:

Dim ps As SQLitePreparedStatement = IncidentsDB.Prepare("INSERT INTO Types (Type) VALUES (?);") ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) ps.SQLExecute("val1") ps.SQLExecute("val2")

Norman,
Your code is a bit too advanced for me at the moment - but thank you anyway :slight_smile:

8

Funny I’d consider mine the simple way
Literally create global string constant in your project, lets call it “DBSetUp”, that has, as its contents,

CREATE TABLE Types(Type STRING PRIMARY KEY)
INSERT INTO Types (Type) VALUES ('1')
INSERT INTO Types (Type) VALUES ('2')
INSERT INTO Types (Type) VALUES ('3')
INSERT INTO Types (Type) VALUES ('4')
INSERT INTO Types (Type) VALUES ('5')
INSERT INTO Types (Type) VALUES ('6')
INSERT INTO Types (Type) VALUES ('7')
INSERT INTO Types (Type) VALUES ('8')
INSERT INTO Types (Type) VALUES ('9')

Then your set up code is

dim lines() as string = Split( replacelineendings(DBSetUp, endofline), endofline )
 
for each line as string in lines
    mydb.SQLExecute( line )
next
9

Will try your method Norman.
Have I put the code in the right place??? I have never used string constants before, and have added one as advised, but was not sure where to stick the code?

Thanks.

10

Why do you want a string as a Primary Key? It makes things much slower specially when indexing as it needs to be re-sequenced on every insert.

If you’re just using sequential numbers you don’t need a string constant and an array, you can just use a FOR … Next loop and str()

11

Jym,
I’m not using numbers - each value will be a text string. Norman just used numbers as an example, and I will be changing them to strings as soon as I know the code is in the right place.

Should I remove the primary key?

Thanks.

12

[quote=130688:@Richard Summers]Will try your method Norman.
Have I put the code in the right place??? I have never used string constants before, and have added one as advised, but was not sure where to stick the code?
[/quote]
The code goes wherever you want and gets called from where it is you are deciding you need to create the database (presumably because a preexisting one does not exist)

13

What I meant was - is my code in the right place i.e. - in the Edit Value area.
It is now all sorted and working perfectly - thank you very much Norman - much appreciated :slight_smile:

14

Personally I’d make the column UNIQUE and add a PRIMARY KEY column because it’s much easier to organize a numbered column then it is to organize text column.

i.e.
CREATE TABLE mytable
(
mytext NOT TEXT NULL UNIQUE, myIPK INTEGER PRIMARY KEY
)

but I think you said you were using mySQL which is a little different
CREATE TABLE mytable
(
mytext TEXT NOT NULL, myIPK INTEGER NOT NULL,
CONSTRAINT on_mytable UNIQUE (mytext, myIPK)
)

That’s a guess, I don’t use mySQL often.

15