Using SQL Create Statements

You can use the SQL CREATE statement in conjunctions with wsodbc to performa updates and additions to the database. To do this, you will typically use the db.exec() function, with the SQL statement as an argument. An example of this is shown below:

var arrObj = db.exec( "SELECT * FROM TABLE" );

Note: Only the SELECT statement will return any values. The INSERT, DELETE, UPDATE, and CREATE statements do not return any values. If you are returning values from the database, we recommend that you use an array to hold them.

In the following example, we will use the CREATE statement to create a new table in a SQL database.

Create a new database with the following code:
```
db = new Obdc(<connection parameters>);
```

Create a new SQL query in a variable. An example is shown below:

sqlQuery = "if not exists ( select * from sysobjects where name='user' and xtype='U')" +
"CREATE TABLE[dbo].[USER](" + "[RECID][varchar](15)NOT NULL," + "[USERNAME][varchar](12)NOT NULL," + 
"[PASSWORD][varchar](40)NOT NULL," + "[CLIENT][varchar](3)NOT NULL," + "[LANG][varchar](2)NULL," +
"[CREATE_DATA][date]NOT NULL," + "[MODIFIED_DATE][date]NULL," + "[CREATED_BY][varchar](10)NOT NULL," +
"[MODIFIED_BY][varchar](10)NULL," + "[LOCKED][varchar](1)NULL," + "[FIRST_LOGIN][varchar](1)NULL," +
"[PLANT][varchar](4)NOT NULL," + "

Synactive recommends always using the if not exists(select * from sysobjects where name='user' and xtype='U'); statement when creating tables. This will verify that the table 'user' exists in the specified database. If the 'user' table does not exist, it will be created. We also recommend handling the record IDs, the session information, modification information and messages for each table.

Use the db.exec() function to execute the SQL query as shown below:
```
db.exec(sqlQuery);
```

To add an entry to the table, create a new query to use in the db.exec() function. An example is shown below:

sqlQuery = "if not exists(select * from [dbo].[USER] where username = 'admin')
insert [dbo].[USER] values ('"+ system.newRecId('$S1')+ "','admin','adminpass','100','EN','10/12/2012','',
'system','','','X','1000','1000')"

Use the db.exec() function to execute the SQL query as shown below:
```
db.exec(sqlQuery);
```
The 'if not exists' statement will verify that the user 'admin' exists. If the user does not exist, the new entry will be added to the table.
```
sqlQuery = "if not exists (select * from [dbo].[USER] WHERE USERNAME = 'admin') insert [dbo].[USER] VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)"
          db.exec(sqlQuery,arrSQLQUERY);
```
Because the values are hard-coded in the example above, there is a possibility that the RecID may return a value with a quote. This would break the sequence and generate an incorrect result. To avoid this possibility, you should use an array as shown below:
```
var arrSQLQUERY = [];
arrSQLQUERY = [system.newRecId('$S1'),'admin','adminpass','100','EN','10/12/2012','',
'system','','','X','1000','1000'];
```

Your SQL query will appear as shown below:

sqlQuery = "if not exists (select * from [dbo].[USER] WHERE USERNAME = 'admin') insert [dbo].[USER] VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)"

Use the db.exec() function to execute your SQL query as shown below:
```
db.exec(sqlQuery,arrSQLQUERY);
```