create database

[Da_Elf]

Im using mysql community. I know how to create tables if none exist but i was wondering how i would setup mysql with a new user and password and permissions to create a new database to put tables into it

[zaxos]

CREATE DATABASE dbname;

[Da_Elf]

ok so do use the mysql code i will have to log onto mysql using the root username and password. Then i use this set of code

CREATE DATABASE frank;
CREATE USER 'frankie'@'localhost' IDENTIFIED BY 'peterpiper1';
GRANT ALL PRIVILEGES ON frank . * TO 'frankie'@'localhost';
FLUSH PRIVILEGES;

then i log out from using the root password and name and log in from here on in to the database frank with the supplied username and pass

The only way i know to connect to mysql is with revOpenDatabase which calls for a database name. What livecode syntax do i use to connect as root so that i can pass these commands onto the mysql server

[zaxos]

I'd propably do something like this if i were in your position:

Code: Select all

  global gConnectionID
    put "CREATE DATABASE frank;" into tSql
    if tSql contains "CREATE DATABSE" then
    put "localhost" into tDatabaseAddress
    put "mysql" into tDatabaseName
    put "root" into tDatabaseUser
    put "pass" into tDatabasePassword
    else
    put "localhost" into tDatabaseAddress
    put "frank" into tDatabaseName
    put "frankie" into tDatabaseUser
    put "pass" into tDatabasePassword
    end if
    put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
    
    if tResult is a number then
        put tResult into gConnectionID
        answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
    else
        put empty into gConnectionID
        answer error "Unable to connect to the database:" & cr & tResult
    end if

Depends on what you want, if you want the user to create the database and then connect to it then you'l need to save the database name,user name, and password when you create it, i can help you with that if give me more details.

[Da_Elf]

Ohhh so basically i will still include the database name as empty in revOpenDatabase thats where i went wrong. i omitted it altogether and naturally there was a connection error.

[zaxos]

Actualy, a better way would be:

Code: Select all

global gConnectionID
on connectToDatabase tDatabaseAddress,tDatabaseName,tDatabaseUser,tDatabasePassword
     if gConnectionID is a number then
        revCloseDatabase gConnectionID
        put empty into gConnectionID
     end if
    put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
    
    if tResult is not a number then
        put empty into gConnectionID
        answer error "Unable to connect to the database:" & cr & tResult
    end if
end if
end connectToDatabase

and then you call it like this:
connectToDatabase "localhost","mysql","root","pass"
OR
connectToDatabase "localhost","frank","frankie","pass"

[zaxos]

Ohhh so basically i will still include the database name as empty in revOpenDatabase thats where i went wrong. i omitted it altogether and naturally there was a connection error.

Actualy you cant connect if you dont specify a dbName or if you try to connect to a database that dosent exist so use something that is always present like "mysql", sorry for that, check the posts i fixed it

[Da_Elf]

thank you that was my problem. trying to do a fresh install of a database through livecode when the software is run for the first time

[Da_Elf]

This is what i ended up doing

Code: Select all

   put revOpenDatabase("MySQL", "localhost", "mysql", "root", "pass") into tResult
   if tResult is a number then
      put tResult into tempConn
   else
      put empty into tempConn
      answer error "Unable to connect to the database"&cr&"Please check your IP address"
   end if
   put empty into tableSQL
   put "CREATE DATABASE caribbean360;" &cr into tableSQL
   revExecuteSQL tempConn, tableSQL
   put "CREATE USER '360Interface'@'localhost' IDENTIFIED BY 'Caribbean360Pass';" &cr into tableSQL
   revExecuteSQL tempConn, tableSQL
   put "GRANT ALL PRIVILEGES ON caribbean360 . * TO '360Interface'@'localhost';" &cr into tableSQL
   revExecuteSQL tempConn, tableSQL
   put "FLUSH PRIVILEGES;" &cr into tableSQL
   revExecuteSQL tempConn, tableSQL
   put empty into tableSQL
   revCloseDatabase tempConn

[zaxos]

Gj man , first time i see revexecutesql btw, usualy i used revdatafromquery(or something) guess we all learned something...

[Da_Elf]

i use the datafromquery() for "SELECT"
for delete, insert and update i use execute