Mssql: create database

Databases
1

I’ve created a class to handle all what i need for working with a MS SQL database:

  • Verify the existence of the database
  • Create database
  • Create tables
  • Create users
  • Create authority
  • Write data, read data

Im working with XOJO 2023, Release 2 on macOS with ODBC.

But the creation of the database fails:

Private Function CreateDatabase() As boolean
  var lv_SQL       as string
  
  lv_SQL = "CREATE DATABASE "+DB_Name+";"
  
  return Execute_SQL_Statement( lv_SQL )
End Function

Private Function Execute_SQL_Statement(iv_SQL as String) As boolean
  // ----------------------------------------------------------------------------------
  // - Execute_SQL_Statement                                                     ------
  // ----------------------------------------------------------------------------------
  // - Ausführung der SQL-Statements direkt auf der Datenbank oder per ODBC           -
  // ----------------------------------------------------------------------------------
  // --> iv_SQL          SQL-Statement(s)
  // ----------------------------------------------------------------------------------
  
  try
    if DB = nil then
      DBODBC.ExecuteSQL(iv_SQL)
    else
      DB.ExecuteSQL(iv_SQL)
    end if
    return true
  catch e as DatabaseException
    MsgType = gc_msgty_e
    Message = e.Message
    return false
  end try
End Function

The SQL statement is: “CREATE DATABASE Optometrie;”

The resulting database exception has error number 226 with message “[Actual][SQL Server] CREATE DATABASE statement not allowed within multi-statement transaction.”

Does anybody know what a multi-statement transaction means? Because the app has done at this moment a “BeginTransaction” and the “CREATE DATABASE”.

2

Just by calling Begin Transaction, you’ve created the multi-state transaction. Database creation is not an operation that can be rolled back and is therefore not allowed inside a transaction. Remove the BeginTransaction statement or move it to after you call Create Database.

3

You are right!

Thank you very much!