Create a new mysql database

Just starting out? Need help? Post your questions and find answers here.
coffee
User
User
Posts: 77
Joined: Fri Oct 06, 2017 10:43 am

Create a new mysql database

Post by coffee »

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
User avatar
Bisonte
Addict
Addict
Posts: 1226
Joined: Tue Oct 09, 2007 2:15 am

Re: Create a new mysql database

Post by Bisonte »

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.
PureBasic 6.04 LTS (Windows x86/x64) | Windows10 Pro x64 | Asus TUF X570 Gaming Plus | R9 5900X | 64GB RAM | GeForce RTX 3080 TI iChill X4 | HAF XF Evo | build by vannicom​​
English is not my native language... (I often use DeepL to translate my texts.)
coffee
User
User
Posts: 77
Joined: Fri Oct 06, 2017 10:43 am

Re: Create a new mysql database

Post by coffee »

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

Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: Create a new mysql database

Post by Marc56us »

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.

:wink:
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: Create a new mysql database

Post by TI-994A »

coffee wrote:how does one create a new database in mysql/mariadb programatically?
This seems to do the trick quite simply:

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 :D
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Create a new mysql database

Post by infratec »

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.
coffee
User
User
Posts: 77
Joined: Fri Oct 06, 2017 10:43 am

Re: Create a new mysql database

Post by coffee »

Thank you all for your valuable help.
Post Reply