Can anyone help me with this?

[Baldur2630]

I'm trying to create 4 tables. I set the tables up with MySQL Workbench and it worked fine. I used the same SQL for my Table creation in Delphi and I keep getting errors. I split the SQL up into smaller chunks. The Recipes Table - np problem, Ingredients, No problem, but as soon as I start putting in the references and Constraints, I just keep getting this Error 150 and I can't see the mistake / problem anywhere. Can anyone spot it?

Procedure CreateDatabase;
var
sdatabase, A, B, C, D, E, Csql, Dsql, Esql, Fsql : AnsiString;
Begin
sdatabase:='CREATE DATABASE IF NOT EXISTS RecABS';
A:='USE RecABS;';
B:='DROP TABLE IF EXISTS recipes;';
C:='DROP TABLE IF EXISTS recipeingredients;';
D:='DROP TABLE IF EXISTS ingredients;';
E:='DROP TABLE IF EXISTS types;';
Csql:='CREATE TABLE IF NOT EXISTS recipes ( ' +
' recipeid int(10) NOT NULL AUTO_INCREMENT,' +
' itemname varchar(50) default NULL,' +
' type int(10) NULL default NULL,'+
' description varchar(200) NULL default NULL,' +
' piclocation varchar(60) NULL default NULL,' +
' batchsize varchar(15) NULL default NULL,' +
' time2cook varchar(10) NULL default NULL,' +
' comments varchar(200) NULL default NULL,' +
' PRIMARY KEY (recipeid)' +
' )ENGINE=INNODB DEFAULT CHARSET=latin1; ';

Dsql:='CREATE TABLE recipeingredients ( ' +
' recipeid int(10) NULL,' +
' ingredientid int(10) NULL,' +
' amount varchar(20) default NULL,' +
' recipe_recipeid INT(10) NOT NULL,' +
' Ingredient_ingredientid INT(10) NOT NULL,' +
' PRIMARY KEY (recipeid),' +
' INDEX fk_recipeingredients_recipe1_idx (recipe_recipeid ASC),' +
' INDEX fk_recipeingredients_ingredient1_idx (ingredient_ingredientid ASC),' +
' CONSTRAINT fk_recipeingredients_recipe1,' +
' FOREIGN KEY (recipe_recipeid),' +
' REFERENCES recipe (recipeid),' +
' ON DELETE NO ACTION,' +
' ON UPDATE NO ACTION,' +
' CONSTRAINT fk_recipeingredients_ingredient1,' +
' FOREIGN KEY (ingredients_ingredientid),' +
' REFERENCES ingredients (ingredientid),' +
' ON DELETE NO ACTION,' +
' ON UPDATE NO ACTION' +
' )ENGINE=INNODB DEFAULT CHARSET=latin1; ';

Esql:='CREATE TABLE ingredients ( ' +
' ingredientid int(10) NOT NULL,' +
' name varchar(50) NULL,' +
' supplier varchar(50) NULL,' +
' PRIMARY KEY (ingredientid),' +
' UNIQUE INDEX iname (name ASC)' +
' )ENGINE=INNODB DEFAULT CHARSET=latin1; ';

Fsql:='CREATE TABLE types ( ' +
' typeid int(10) NOT NULL,' +
' name varchar(50) NULL,' +
' recipe_recipeid INT(10) NOT NULL,' +
' PRIMARY KEY (typeid),' +
' UNIQUE INDEX iname (name ASC),' +
' INDEX fk_types_recipe1_idx (recipe_recipeid ASC),' +
' CONSTRAINT fk_types_recipe1,' +
' FOREIGN KEY (recipe_recipeid),' +
' REFERENCES recipe (recipeid),' +
' ON DELETE NO ACTION,' +
' ON UPDATE NO ACTION' +
' )ENGINE=INNODB DEFAULT CHARSET=latin1; ';

DataModule2.dbMyRecipes.Disconnect;
DataModule2.dbMyRecipes.HostName:='192.168.0.199';
DataModule2.dbMyRecipes.User:='henry';
DataModule2.dbMyRecipes.Protocol:='mysql';
DataModule2.dbMyRecipes.Port:=3306;
DataModule2.dbMyRecipes.Password:='3tbhfhcf';
// connect
try
DataModule2.dbMyRecipes.Connect;
except
on E: Exception do showmessage(E.Message);
end;
//create database
Try
DataModule2.dbMyRecipes.ExecuteDirect(sdatabase);
except
on E: Exception do showmessage(E.Message);
end;
//create tables
Try
DataModule2.dbMyRecipes.ExecuteDirect(A);
DataModule2.dbMyRecipes.ExecuteDirect(B);
DataModule2.dbMyRecipes.ExecuteDirect(C);
DataModule2.dbMyRecipes.ExecuteDirect(D);
DataModule2.dbMyRecipes.ExecuteDirect(E);
DataModule2.dbMyRecipes.ExecuteDirect(Csql);
DataModule2.dbMyRecipes.ExecuteDirect(Dsql);
DataModule2.dbMyRecipes.ExecuteDirect(Esql);
DataModule2.dbMyRecipes.ExecuteDirect(Fsql);
showmessage('success create tables..!');
except
on E: Exception do showmessage(E.Message);
end;
End;

[Pitfiend]

Mate, your trouble lies in the order in which you create each table, take a closer look, you want to set a relation between tables and some not exists yet. You must create your master tables first, then your dependents.

[Baldur2630]

I got all excited. I thought this was the problem, so I created the Recipe Table, then the Ingredients Table then the Type Table and finally the RecipeIngredients Table. (I took out all the
CONSTRAINT fk_types_recipe1,' +
' FOREIGN KEY (recipe_recipeid),' +
' REFERENCES recipe (recipeid),' +
' ON DELETE NO ACTION,' +
' ON UPDATE NO ACTION' +

It created the Tables perfectly.

I then tried to run Alter Table with the constraint, Foreign Key etc (above), but the minute I try to execute ANY of the above statements I get the "There is an error in . . .

I tried doing the Alter Table in all possible sequences - same result. Delphi just doesn't like any of the above SQL.

[Pitfiend]

I just noticed you use a username and a password, be sure your user has all the rights granted to do any of these operations. Delphi has no preference or dislike with sql, you can do anything allowed by the server. Then, if your server forbids something to your user, your going to get that kind of errors.

[Baldur2630]

No, that wasn't the problem, it was something that I'm actually amazed that no-one picked up on.

It makes me wonder if other people actually use MySQL, not only with ZeosLib, but with Delphi at all, because in 3 different Forums, not a single person could point me in the right direction, I spent MANY hours of trial and error before I found the problem.

[marsupilami]

Hello Baldur2630,

could you please share the solution of your problem with us?
Best regards,

Jan

[Baldur2630]

No problem. Create database as normal.
Create Tables, Primary Keys and Indexes as normal
Now use Alter Table to create the Constraints etc, BUT . . .

It only works if you put the CONSTRAINT FOREIGN KEY REFERENCES and ON UPDATE and ON DELETE all on one single line. I tried every way possible to split the command over several lines but none of them worked.

Csql := ' alter table types ' +
' ADD CONSTRAINT fk_types_recipes FOREIGN KEY (recipes_recipeid) REFERENCES recipes (recipeid) ON UPDATE NO ACTION ON DELETE RESTRICT;';

Then all you have to do is make sure that if you are going to re-create the tables, to drop them in the right order so you don't violate the constraints.

I'm not sure how this will work if there are long names. i seem to recall that the max length of a line in the IDE is either 128 or 255 characters - but it's been a while. . .

[Pitfiend]

CONSTRAINT fk_types_recipe1 , ' +
' FOREIGN KEY (recipe_recipeid) , ' +
' REFERENCES recipe (recipeid) , ' +
' ON DELETE NO ACTION , ' +
' ON UPDATE NO ACTION' +

Did you notice that? you put some , in the middle of your command, in the wrong places, that's what I think is your trouble from the very begining.

[Baldur2630]

I am not completely senile, I already tried it without the commas. Perhaps if you tried it yourself, you would find that it DOES NOT WORK if you try to split the line up and if you spotted it from the very beginning, why didn't you point it out?

[Pitfiend]

Hey man, no need to be rude, we are trying to help you.

I take some time to run your code and check what's going on.

First, next time you want to catch an exception do it line by line, that way you are going to know what happens in every step.

Second, take a time to check your code many times, it helps if you can run your scripts in an interactive client, just to see you are doing it right. As said before, Delphi or Freepascal have no like or dislike for SQL, if you write it wrong and never try it on an interactive client, it'll be very hard to spot your mistakes.

Finally, I try your code, some fix here and there, and guess what, it runs smoothly, everything was created as expected, including the references and dependencies.

And then I found the source of all your troubles. You are trying to use an engine that not supports some features you are using. I tested two engines, INNODB and MYISAM, the last accepted the foreign keys, the former responds with error 150, everytime I run the script. It even refuses to create the references using alter table.

Hope this helps you.

[Baldur2630]

I wasn't trying to be rude. As I said, I'm not senile even though I am 72 and I check EVERYTHING line by line and add watches and breaks everywhere.

I'm obviously one up on you, because I DID manage to create the Tables, the Constraints and everything using INNODB and MySQL

As I said if you put the entire CONSTRAINT, FOREIGN KEY, REFERENCES and ON UPDATE and ON DELETE all on ONE LINE using an alter table, it works PERFECTLY. I'll be happy to give you the whole code to prove it if you want it.