A little help with SQL

skodkim · Nov 18, 2014

Hi

I've got a little modmod that I'd like to use to grant a magistrate to a certain building. Since I use the modmod both with/without a certain mod (Events and Decisions) I'd however like to make a check to see if E&D is active so I thought I'd check if the Building_ResourcePerEra table is in the database (E&D adds this table to the database).

Unfortunately I'm not a great sql-coder and my code below doesn't work - I just get an error message saying that the table doesn't exist. I was hoping someone could help?

The code goes:

Spoiler :

Any help would be much appreciated

\Skodkim

whoward69 · Nov 18, 2014

The INSERT INTO bit is looking for the table that doesn't exist before the WHERE clause is evaluated.

Easiest way to do this is to put the INSERT INTO statement into it's own file in the mod and live with the error message that will be generated into database.log when it fails

skodkim · Nov 18, 2014

whoward69 said:
The INSERT INTO bit is looking for the table that doesn't exist before the WHERE clause is evaluated.

Easiest way to do this is to put the INSERT INTO statement into it's own file in the mod and live with the error message that will be generated into database.log when it fails

Thanks for the reply. Kind of guessed that was the cause but hoped for a solution

\Skodkim

Nutty · Nov 19, 2014

Why the sad face? It's an effective solution. If you wanted to run other statements if and only if "Events and Decisions" is active, then you just put the statements after your INSERT. If the table error is generated (meaning E&D isn't active), then the file stops processing at that point.

Don't forget to set E&D as a Reference to the modmod to ensure it is loaded first.

JFD · Nov 19, 2014

Just do what I do and create the table beforehand:

Code:

CREATE TABLE IF NOT EXISTS Building_ResourcePerEra (
  BuildingType text,
  ResourceType text,
  Quantity integer default 0,
  InitialQuantity integer default 0
);

INSERT INTO Building_ResourcePerEra 	
			(BuildingType, 		ResourceType,	Quantity, 	InitialQuantity)
SELECT		('BUILDING_XXX'), 	Type,			1, 			1
FROM Resources WHERE Type = 'RESOURCE_MAGISTRATES';

Then no error.

Nutty · Nov 19, 2014

...Except that there's no reason to create the table if E&D isn't active. Seems silly to create it just to prevent an error from appearing in the log.

JFD · Nov 19, 2014

Its silliness isn't relevant. It's a suggestion to the OP who may, for whatever reason, not want superfluous errors in their database.log, or who may, for whatever reason, not want the file containing the table to fail in the event that E&D isn't active. It bugs the hell out of me to see benevolent errors in the database log, which is why I do it, so I was merely pointing this out in the event that the OP was in a similar situation. Your silly is my preference.

skodkim · Nov 20, 2014

JFD said:
Its silliness isn't relevant. It's a suggestion to the OP who may, for whatever reason, not want superfluous errors in their database.log, or who may, for whatever reason, not want the file containing the table to fail in the event that E&D isn't active. It bugs the hell out of me to see benevolent errors in the database log, which is why I do it, so I was merely pointing this out in the event that the OP was in a similar situation. Your silly is my preference.

Agree 100% And Thanks for the solution - I'm definately going to use it!

I think it's vaery relevant. First of all I usually test new additions that concern aspects that are not related to E&D (or other mods) without the mod to make the game load faster + I don't have to tick on unnecessary mods. Second I look to the log files a lot to find errors and I hate having these error messages that makes you wonder what else has gone wrong, e.g. a bug that makes the game skip the rest of the file.

So once again thank you very much for the feedback :goodjob:

\Skodkim

whoward69 · Nov 20, 2014

With the CREATE TABLE IF NOT EXISTS ... statement your mod MUST reference the original mod to guarantee update order of the database

skodkim · Nov 21, 2014

whoward69 said:
With the CREATE TABLE IF NOT EXISTS ... statement your mod MUST reference the original mod to guarantee update order of the database

Good point!

\Skodkim

A little help with SQL

skodkim

Deity

whoward69

DLL Minion

skodkim

Deity

Nutty

Deity

JFD

Kathigitarkh

Nutty

Deity

JFD

Kathigitarkh

skodkim

Deity

whoward69

DLL Minion

skodkim

Deity

Similar threads