Find error in few sql code ?

[Serp]

I think everytime there is a problem with xml or sql code, there won't be any error message and instead the file just stops executing. So it is hard to find errors =/
I'm not that familiar with sql, so I don't know whats wrong with the code...

I would like to add a new promotion to all recon units, except a few specific units:

-- Give all recon units PROMOTION_XP_FOR_SCOUTING
INSERT INTO Unit_FreePromotions(UnitType, PromotionType)
  SELECT Type, 'PROMOTION_XP_FOR_SCOUTING' FROM Units WHERE CombatClass='UNITCOMBAT_RECON' AND NOT Type='UNIT_EXPLORERX' AND NOT Type='UNIT_ADVENTURER' AND NOT Type='UNIT_AIRSHIP' AND NOT Type='UNIT_SKY_FORTRESS';
   
-- Update any additional eg. mod recon units to have PROMOTION_XP_FOR_SCOUTING as well
CREATE TRIGGER FreeXpForScouting
AFTER INSERT ON Units
WHEN ('UNITCOMBAT_RECON'=NEW.CombatClass AND NOT 'UNIT_EXPLORERX'=NEW.Type AND NOT 'UNIT_ADVENTURER'=NEW.Type AND NOT 'UNIT_AIRSHIP'=NEW.Type AND NOT 'UNIT_SKY_FORTRESS'=NEW.Type)
BEGIN
  INSERT INTO Unit_FreePromotions(UnitType, PromotionType)
    VALUES(NEW.Type, 'PROMOTION_XP_FOR_SCOUTING')
END;

I think the second code with the trigger contains an error, cause if I put it above the other code, no recon unit gets the new promotion.

 

[PawelS]

The errors are reported in database.log (see also how to enable logging).

In this case, I'm not very good at SQL either, but I think you're missing a ; at the end of the penultimate line.

 

[LeeS]

I frankensteined this together from looking at JFD code. Hopefully it is correct. I am not a Nutty-level SQL maker either.

CREATE TRIGGER FreeXpForScouting
AFTER INSERT ON Units
WHEN NEW.CombatClass = 'UNITCOMBAT_RECON' AND NEW.Type NOT IN ('UNIT_EXPLORERX', 'UNIT_ADVENTURER', 'UNIT_AIRSHIP', 'UNIT_SKY_FORTRESS')
BEGIN
  INSERT INTO Unit_FreePromotions(UnitType, PromotionType)
    VALUES(NEW.Type, 'PROMOTION_XP_FOR_SCOUTING');
END;

Fixed per the error @Nutty pointed out.
@Serp I bet that was your original problem since you have the same missing semi-colon issue in your original code.

 

[Nutty]

@LeeS: Thanks, but I also often stand on the shoulders of smarter folks than I. Triggers often confuse me too. You did forget the semicolon after the ")" just before "END;"

@Serp: The great thing about SQL is you don't have to load up the game to test. See the SQLiteSpy 101 tutorial.
I'd recommend making a backup of the db file first so you don't have to start up the game to get a fresh database once you figure out you screwed up.

 

[Serp]

Thank you very much guys!

Don't want to open a new thread for a short question:

Can someone please translate these few sql lines into xml ?

INSERT INTO ArtDefine_UnitInfoMemberInfos   
       (UnitInfoType,                           UnitMemberInfoType,                               NumMembers)
SELECT    'ART_DEF_UNIT_JFD_ADVENTURER',            UnitMemberInfoType,                            NumMembers
FROM ArtDefine_UnitInfoMemberInfos WHERE UnitInfoType = 'ART_DEF_UNIT_EXPLORER';

I think it could be something like this:

  <ArtDefine_UnitInfoMemberInfos>
        <Row>
           <UnitInfoType>ART_DEF_UNIT_JFD_ADVENTURER</UnitInfoType>
           <UnitMemberInfoType>??</UnitMemberInfoType>
           <NumMembers>??</NumMembers>
       </Row>
   </ArtDefine_UnitInfoMemberInfos>

"Insert into" okay we insert something into ArtDefine_UnitInfoMemberInfos.
I think "select" means, that we select the things we insert, from the following things.
"from" ?? I thought we already declared what we insert where. What does the "from" means now?
I thought it could be needed to fill the variables used in the "select" line, with values found in "ART_DEF_UNIT_EXPLORER", but there are more than one entry with that UnitInfoType and there are also sql lines with no such variables but still a "from" line...

 

[LeeS]

I did not look up what the game has for ART_DEF_UNIT_EXPLORER within table <ArtDefine_UnitInfoMemberInfos>, but suppose it had this:

  <ArtDefine_UnitInfoMemberInfos>
        <Row>
           <UnitInfoType>ART_DEF_UNIT_EXPLORER</UnitInfoType>
           <UnitMemberInfoType>Hamburgers</UnitMemberInfoType>
           <NumMembers>6</NumMembers>
       </Row>
        <Row>
           <UnitInfoType>ART_DEF_UNIT_EXPLORER</UnitInfoType>
           <UnitMemberInfoType>Cheeseburgers</UnitMemberInfoType>
           <NumMembers>24</NumMembers>
       </Row>
   </ArtDefine_UnitInfoMemberInfos>

Then this statement in SQL would copy the existing rows with ART_DEF_UNIT_EXPLORER while substituting ART_DEF_UNIT_JFD_ADVENTURER in place of ART_DEF_UNIT_EXPLORER and add as many new rows as required to table <ArtDefine_UnitInfoMemberInfos>:

INSERT INTO ArtDefine_UnitInfoMemberInfos   
       (UnitInfoType,                           UnitMemberInfoType,                               NumMembers)
SELECT    'ART_DEF_UNIT_JFD_ADVENTURER',            UnitMemberInfoType,                            NumMembers
FROM ArtDefine_UnitInfoMemberInfos WHERE UnitInfoType = 'ART_DEF_UNIT_EXPLORER';

So instead of the original two rows mentioned we would now have four rows in the table:

  <ArtDefine_UnitInfoMemberInfos>
        <Row>
           <UnitInfoType>ART_DEF_UNIT_EXPLORER</UnitInfoType>
           <UnitMemberInfoType>Hamburgers</UnitMemberInfoType>
           <NumMembers>6</NumMembers>
       </Row>
        <Row>
           <UnitInfoType>ART_DEF_UNIT_EXPLORER</UnitInfoType>
           <UnitMemberInfoType>Cheeseburgers</UnitMemberInfoType>
           <NumMembers>24</NumMembers>
       </Row>
        <Row>
           <UnitInfoType>ART_DEF_UNIT_JFD_ADVENTURER</UnitInfoType>
           <UnitMemberInfoType>Hamburgers</UnitMemberInfoType>
           <NumMembers>6</NumMembers>
       </Row>
        <Row>
           <UnitInfoType>ART_DEF_UNIT_JFD_ADVENTURER</UnitInfoType>
           <UnitMemberInfoType>Cheeseburgers</UnitMemberInfoType>
           <NumMembers>24</NumMembers>
       </Row>
   </ArtDefine_UnitInfoMemberInfos>

If table <ArtDefine_UnitInfoMemberInfos> had 27 rows with info for ART_DEF_UNIT_EXPLORER we would get 27 duplicates with ART_DEF_UNIT_JFD_ADVENTURER in place of ART_DEF_UNIT_EXPLORER.

The SELECT command is telling the database what column data to keep from what is already in the database, and what column-data to make substitutions for. In this particular case it is telling the database to substitute ART_DEF_UNIT_JFD_ADVENTURER in place of ART_DEF_UNIT_EXPLORER but to keep the original data from the other two specified columns within the designated table.

The FROM command tells the database where to grab the existing data and what to match-up the new rows to. In this particluar case it is telling the database to grab everything from table <ArtDefine_UnitInfoMemberInfos> that has ART_DEF_UNIT_EXPLORER as the UnitInfoType.

 

[Serp]

great, thank you very much

But how about

INSERT INTO ArtDefine_UnitInfoMemberInfos    
       (UnitInfoType,                           UnitMemberInfoType,                               NumMembers)
SELECT    'ART_DEF_UNIT_JFD_EXPLORER',            'ART_DEF_UNIT_MEMBER_JFD_EXPLORER_1',           2
FROM ArtDefine_UnitInfoMemberInfos WHERE UnitInfoType = 'ART_DEF_UNIT_SCOUT';

while in the database is only one entry like this:

<ArtDefine_UnitInfoMemberInfos>
       <Row>
           <UnitInfoType>ART_DEF_UNIT_SCOUT</UnitInfoType>
           <UnitMemberInfoType>ART_DEF_UNIT_MEMBER_SCOUT</UnitMemberInfoType>
           <NumMembers>6</NumMembers>
       </Row>
</ArtDefine_UnitInfoMemberInfos>

I think the sql code then results in:

<ArtDefine_UnitInfoMemberInfos>
       <Row>
           <UnitInfoType>ART_DEF_UNIT_JFD_EXPLORER</UnitInfoType>
           <UnitMemberInfoType>ART_DEF_UNIT_MEMBER_JFD_EXPLORER_1</UnitMemberInfoType>
           <NumMembers>2</NumMembers>
       </Row>
</ArtDefine_UnitInfoMemberInfos>

So the result in the end has nothing to do with the scout art def. That was what I did not understand. Why is the scout art def thing needed in sql code?

 

[LeeS]

This statement is incorrect usage of the SELECT command, really:

INSERT INTO ArtDefine_UnitInfoMemberInfos    
       (UnitInfoType,                           UnitMemberInfoType,                               NumMembers)
SELECT    'ART_DEF_UNIT_JFD_EXPLORER',            'ART_DEF_UNIT_MEMBER_JFD_EXPLORER_1',           2
FROM ArtDefine_UnitInfoMemberInfos WHERE UnitInfoType = 'ART_DEF_UNIT_SCOUT';

It does not accomplish anything different than

INSERT INTO ArtDefine_UnitInfoMemberInfos    
	(UnitInfoType,			UnitMemberInfoType,			NumMembers)
VALUES	('ART_DEF_UNIT_JFD_EXPLORER',	'ART_DEF_UNIT_MEMBER_JFD_EXPLORER_1',	2);

So in such a case where no existing information from a row within the database is being grabbed and added into the new row, just use direct insert structuring of

INSERT INTO TableName (Column1, Column2, Column3) VALUES ('COLUMN1_VALUE', 'COLUMN2_VALUE', 'COLUMN3_VALUE');

---------------------------------------------------

If you saw for example JFD using that structure using the SELECT command like this:

INSERT INTO ArtDefine_UnitInfoMemberInfos    
       (UnitInfoType,                           UnitMemberInfoType,                               NumMembers)
SELECT    'ART_DEF_UNIT_JFD_EXPLORER',            'ART_DEF_UNIT_MEMBER_JFD_EXPLORER_1',           2
FROM ArtDefine_UnitInfoMemberInfos WHERE UnitInfoType = 'ART_DEF_UNIT_SCOUT';

I would not read too much into that other than he probably found it easier to copy/paste from a chunk of code that needed the SELECT command, and then edited later without removing the SELECT command and using direct INSERT INTO --- VALUES structuring.