hi,
how does one create a new database in mysql/mariadb programatically?
i do have a mariadb server running and like to add one or more database with a different name and content.
any ideas?
thank you in advance
Create a new mysql database
Re: Create a new mysql database
With PB's OpenDatabase() you have to specify a databasename... So I think it's not possible that way.
Maybe it's possible to do it like a telnet program.
Maybe it's possible to do it like a telnet program.
Re: Create a new mysql database
i think i found a solution and maybe someone will have nicer code for this.
Code: Select all
ImportC "libmariadb.lib"
mysql_init(*mysql) ; returns *mysql
mysql_real_connect(*mysql, host.p-utf8, user.p-utf8, password.p-utf8, DataBase.p-utf8, Port, *unix_socket, client_flag)
mysql_real_query(dbHwnd, Query.p-utf8, Length)
mysql_close(*mysql)
EndImport
UseMySQLDatabase()
Procedure.i CreateDbLow(newdatabasename.s, host.s="", user.s="", passwd.s="", port.i=3306)
If user = "" Or passwd = "" Or newdatabasename = "" Or host = ""
ProcedureReturn #False
EndIf
Protected mysql.i = mysql_init(#Null)
Protected ret.i = mysql_real_connect(mysql, host, user, passwd, "", 3306, #Null, 0)
If Not ret
mysql_close(mysql)
ProcedureReturn #False
EndIf
Protected query.s = "CREATE DATABASE IF NOT EXISTS " + newdatabasename
If mysql_real_query(mysql, query, StringByteLength(query, #PB_UTF8)) = 0
mysql_close(mysql)
ProcedureReturn #True
EndIf
mysql_close(mysql)
ProcedureReturn #False
EndProcedure
Re: Create a new mysql database
In any case, to create a new database on an existing server you must already be a registered user in this database.
This user, if not admin, must have creation rights (CREATE)
Then it is possible to create a database from the command line (Windows or Linux), so RunProgram()
With the normal shell:
MySQL ... -e "CREATE DATABASE MyNewDataBase"
(-e: pass SQL query to mysql.exe)
... is database, port, user, password etc if needed
With Mysqladmin:
mysdqladmin ... create MyNewDataBase
From an existing database opened with the same commands, and DatabaseQuery()
CREATE DATABASE MyNewDataBase
Then you need to create user for this database and set privileges.
This user, if not admin, must have creation rights (CREATE)
Then it is possible to create a database from the command line (Windows or Linux), so RunProgram()
With the normal shell:
MySQL ... -e "CREATE DATABASE MyNewDataBase"
(-e: pass SQL query to mysql.exe)
... is database, port, user, password etc if needed
With Mysqladmin:
mysdqladmin ... create MyNewDataBase
From an existing database opened with the same commands, and DatabaseQuery()
CREATE DATABASE MyNewDataBase
Then you need to create user for this database and set privileges.
Re: Create a new mysql database
This seems to do the trick quite simply:coffee wrote:how does one create a new database in mysql/mariadb programatically?
Code: Select all
UseMySQLDatabase()
If OpenDatabase(0, "host=localhost port=3306 dbname=", "root", "")
;create new database user
result = DatabaseUpdate(0, "GRANT ALL PRIVILEGES ON *.* TO 'newpbuser'@'localhost'")
If result
Debug "User 'newpbuser' created successfully."
Else
Debug "User creation error."
EndIf
;create new database
result = DatabaseUpdate(0, "CREATE DATABASE IF NOT EXISTS newpbdatabase")
If result
Debug "Database 'newpbdatabase' exists/created successfully."
Else
Debug "Database creation error."
EndIf
Else
Debug DatabaseError()
EndIf
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel
Re: Create a new mysql database
There is only one trick:
The user who connects to postgresql needs to be a 'superuser'.
Then he can do what he want. Also create new databases.
The user who connects to postgresql needs to be a 'superuser'.
Then he can do what he want. Also create new databases.
Re: Create a new mysql database
Thank you all for your valuable help.