Heloisa Paiva · Mar 2 3m read

Quick sample database tutorialContestant


This is a simple tutorial on the quickest way I found to create a sample database for any purposes such as testing, making samples for tutorials, etc.


Creating a namespace

  1. Open the terminal
  2. Write the command "D $SYSTEM.SQL.Shell()"
  3. Write "CREATE DATABASE " and the name you want for your namespace.

Now you have a new namespace in a faster way than creating it from the Management Portal - which of course offers way more configuration options.

To select it, quit the SQL Shell writing "q" and then typing 'zn "namespaceName"'. Now everything you do in the terminal is in the scope of this namespace.


Creating a table

  1. Open the terminal
  2. Open the SQL Shell with "D $SYSTEM.SQL.Shell()"
  3. Enter the multiline mode by pressing enter
  4. Write the code such as the following example:
    [SQL]SAMPLE>>  << entering multiline statement mode, 'GO' to execute >>
        1>>CREATE TABLE Sample.PersitentData (
        2>>Name %String,
        3>>Age %Integer )
    You can specify the properties with the names you want and with types from IRIS (such as %String) or from SQL (such as VARCHAR), choose the one you're more comfortable with. You can also define many configurations within that code, as you can see in CREATE TABLE (SQL), but the objective of this article is to make everything as simple as possible.


Now you can see your class in the Management Portal, Studio, VS Code, or access it from the Terminal, with the name and package you put instead of "Sample.PersistentData" in the example.


Populating the table

Once you have a table created, access the source code of the class through Studio or VS Code and add %Populate to the "Extends" parameters.

The class signature should look like this:

Class Sample.PersistentData Extends (%Persistent, %Populate)

of course, with any other classes you might want to extend - %JSON.Adaptor and %XML.Adaptor might be useful for exporting data, viewing it in the productions portal, etc.


Now you can open the terminal and execute the following code, substituting "100" for the number of rows you want:

D #class(Sample.PersistentData).Populate(100)

Notice how in this example I've only specified the age as Integers, so I'll have unreal results because the Populate() method will only create random Integers, not worrying if they're adequate ages for people.

Here's what  I got as a result:

You can make a more complex code when creating the table, but since I'm doing everything in the simplest way possible, I ran the following code:

UPDATE Sample.PersistentData
SET Age = Age # 120


Although the tutorial might not seem that small, having learned all those steps I use them for testing ideas I want to bring to my company and to create samples for the articles I write here - you might have read something I wrote! - and it takes me only around a minute to have a well structured database and focus on developing the content itself.

1 108
Discussion (5)2
Log in or sign up to continue

Hi @Heloisa Paiva !

Great article!

Titles a little confuse me:

First title states "Create Namespace",

but in fact with "CREATE DATABASE DATABASE_NAME" both namespace and database are being created.

Second title says" Create Database", where you says how to create a table inside the database, right?

Hi @Evgeny Shvarov!
Thank you!
I've changed the second title to Create Table! 
Thank you for the feedback! I made a little confusion because I was deciding if I would use the first title as create database or create namespace and I ended up leaving both of them and deleting the "Create Table" haha 

You are welcome! Thanks for revealing such a helpful topic!

BTW, I like the method creating database via SQL command "CREATE DATABASE".

The only difference with the manual creation via Management Portal or via %Installer that in this case the default /csp/namespace application is not being introduced. 

Me too! I feel that some configurations such as where to store globals and routines, and mapping with other namespaces are more "user friendly" in the Management Portal, but when creating samples those things aren't really something to worry about or to spend time on.