Creating Databases

Introducing a New Database

The creation of a new database can be done only through the ODBC control panel.

Filling the New Database

To create the structure of the database, first load Database Access Kit and open the database.

Needs["DatabaseAccess`"]

dbcreate  =  OpenDataSource["dbcreate"]

Next, create a table and its columns.

DataSourceEvaluate[dbcreate,  
      SQLCreateTable[
        SQLTable["employees"],  {SQLColumn[ColumnName -> "emp_id",  
            DataTypeName -> "char",  DataLength -> 4,  
            Nullable -> False],  
          SQLColumn[ColumnName -> "emp_lname",  
            DataTypeName -> "text",  DataLength -> 40,  
            Nullable -> False],  
          SQLColumn[ColumnName -> "emp_fname", 
            DataTypeName ->  "text",  DataLength -> 20,  
            Nullable -> False]}]]

Enter three rows of data in the employees table, using the SQLInsert function three times, with different data values each time.

\(DataSourceEvaluate[dbcreate, \ 
      SQLInsert[
        SQLTable["employees"], \ {{SQLColumn["emp_id"], "0001"}, \ {SQLColumn["emp_lname"], "Isaac"}, \ {SQLColumn["emp_fname"], "Newton"}}]]
\)
\(DataSourceEvaluate[dbcreate, \ 
      SQLInsert[
        SQLTable["employees"], \ {{SQLColumn["emp_id"], "0002"}, {SQLColumn["emp_lname"], "George"}, \ {SQLColumn["emp_fname"], "Washington"}}]]
\)
\(DataSourceEvaluate[dbcreate, \ 
      SQLInsert[
        SQLTable["employees"], \ {{SQLColumn["emp_id"], "0003"}, \ {SQLColumn["emp_lname"], "Jesse"}, \ {SQLColumn["emp_fname"], "James"}}]]\)

This process can also easily be automated for any list using just a couple lines of code.

\(\(addEmployee[offset_, name_, list_] := 
        Map[
iter = 
            offset; \((DataSourceEvaluate[name, \ 
                SQLInsert[
                  SQLTable["employees"], \ {{SQLColumn["emp_id"], 
                      ToString[
                        PaddedForm[\(++iter\), 3, 
                          NumberPadding -> {"0", ""}]]}, \ {SQLColumn["emp_lname"], 
                      ToString[#[\([1]\)]]}, \ {SQLColumn["emp_fname"], 
                      ToString[#[\([2]\)]]}}]] &)\), list];\)\)

The new function addEmployee takes either a name or a list of names. It could easily be generalized to read the names from a file, from another database table, or off the
web. It could also be modified to take the column name as a variable or assigned to a button.

newemployees=(({{Donald, Tubbs}, {Claire, Mulligan}, {Peter, Fontane}, {Julie, Debussy}}));

The next line adds these four names to the database dbcreate.

addEmployee[3, dbcreate, newemployees];

View the employees table with column headings. The column names each have a space in the name as defined.

DataSourceEvaluate[dbcreate,  SQLSelect[SQLTable["employees"]],  
        ShowColumnHeadings  ->  True] // TableForm

Close the data source.

CloseDataSource[dbcreate]