About creating database with code

[André_van_Haren]

I am creating a script in Xojo to create my database with code. I already created the database within the DB Browser for SQLITe program, but I think it would be a good idea to have the option to be able to Create it from within the app as well (especially because I almost lost my created DB…).

I have lots of columns in the database and tried to do a simple copy/paste from the DB Browser program into the Xojo code, but that doesn’t seem to work without removing all the quotes that the DB Browser program places around the column names. No problem though. What is a little bit a problem is that I seem to have to place all the columns code on a single line of code in Xojo which makes it very difficult to get track with so many columns. Is there a way to keep if more organised as in the screenshot?

[anon20074439]

Try

S = "..." + _
"..." + _
"..."

[Kem_Tekinay]

The easiest thing to do is put the SQL into a class or module constant.

[Emile_Schwarz]

https://documentation.xojo.com/api/language/line_continuation.html#api-language-line-continuation-compatibility

[André_van_Haren]

I copied/pasted the sql as is from the DB Browser Into a constant and that constant in the sql command:
db.ExecuteSQL(storiesTable)

works like a charm! Thanks so much.

[TimStreater]

@André_van_Haren

What is a little bit a problem is that I seem to have to place all the columns code on a single line of code in Xojo which makes it very difficult to get track with so many columns. Is there a way to keep if more organised as in the screenshot?

You need to learn how to do statement continuation on the next line. You have to tell it explicitly (using the underscore) that you are doing this. And in your case you’ll have to tell it to concatenate all the bits of string, too.

[André_van_Haren]

Thanks Tim, for this issue it is resolved now using constants, but it’s indeed good to know how to do this.

[Emile_Schwarz]

@André_van_Haren

constants

André, can you explain what you mean when you talk about “Constant” ?

[André_van_Haren]

I placed all the sql code in a Constant.

[Emile_Schwarz]

Yes, that is what you says and it seems to be correct, but I do not get it.

You were talking about using multiple lines… And I… do not understand how you’re doing that in App.Constants…

From:

SQL CREATE

Example:

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

And this is an example I wrote and shared this summer:

Var db As New SQLiteDatabase

// Set the Database file to the db Reference
db.DatabaseFile = DB_FI // DB_FI is a File Reference

If db.Connect Then
  // Add a TABLE And Rows…
  Var sql_Cmd As String
  
  sql_Cmd = "CREATE TABLE IF NOT EXISTS Employee(« +_
  "Name_First TEXT, " +_
  "Name_Last TEXT, " +_
  "Job_Titre TEXT, " +_
  "Job_Salary Text, " +_
  "Employe_ID TEXT, " +_
  "Employe_Photo TEXT, " +_
  "Employe_Entry_Date DATE, " +_
  "ID INTEGER NOT NULL, PRIMARY KEY(ID));"
  
  db.SQLExecute(sql_Cmd)
  If db.Error Then
    MsgBox("Database error: " + db.ErrorMessage)
  Else
    MsgBox("The TABLE ““Employee”” have been added to the Data Base file.")
  End If
  
Else
  MsgBox("Unable to open the Data Base File. Error: " + db.ErrorMessage)
End If

This was a long search (on my hard disks) !

[Robert_Livingston]

@Emile_Schwarz

You were talking about using multiple lines… And I… do not understand how you’re doing that in App.Constants…

When you create a constant in Xojo, it can be as many lines as you like. Type what you will into the Default Value area. If you want multiple lines, click on the ellipsis button (…) and a window will a large text sheet will appear and you can write as many lines as you like here.

After you have types the lines you want in the large text sheet, the first few lines will be visible in the Default Value box.

[Emile_Schwarz]

Thanks.

I checked, but I do not add a constant.

NB: I wrote the above code especially to show how to use many lines…

[Bob_Gordon]

A long time ago I used FoxPro for development. FoxPro had Text…EndText commands.

Anything between the Text and EndText could be multiple lines (like in a Xojo constant). Very convenient for writing SQL.

[Kem_Tekinay]

Other languages use backticks, three quotes, or three single quotes. But I’d settle for same-screen view/edit of constants.