Find error in few sql code ?

Discussion in 'Civ5 - Creation & Customization' started by Serp, Jan 13, 2017.

  1. Serp

    Serp King

    Joined:
    Apr 1, 2015
    Messages:
    650
    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:
    Code:
    -- 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.
     
  2. PawelS

    PawelS Ancient Druid

    Joined:
    Dec 11, 2003
    Messages:
    2,803
    Location:
    Poland
    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.
     
  3. LeeS

    LeeS Imperator

    Joined:
    Jul 23, 2013
    Messages:
    7,241
    Location:
    Illinois, USA
    I frankensteined this together from looking at JFD code. Hopefully it is correct. I am not a Nutty-level SQL maker either.
    Code:
    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.
     
    Last edited: Jan 14, 2017
  4. Nutty

    Nutty Deity

    Joined:
    Mar 9, 2011
    Messages:
    3,181
    Gender:
    Male
    Location:
    Orange County, California, U.S.A.
    @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 likes this.
  5. Serp

    Serp King

    Joined:
    Apr 1, 2015
    Messages:
    650
    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 ?

    Code:
    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:
    Code:
      <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...
     
  6. LeeS

    LeeS Imperator

    Joined:
    Jul 23, 2013
    Messages:
    7,241
    Location:
    Illinois, USA
    I did not look up what the game has for ART_DEF_UNIT_EXPLORER within table <ArtDefine_UnitInfoMemberInfos>, but suppose it had this:
    Code:
      <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>:
    Code:
    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:
    Code:
      <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 likes this.
  7. Serp

    Serp King

    Joined:
    Apr 1, 2015
    Messages:
    650
    great, thank you very much :)

    But how about
    Code:
    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:
    Code:
    <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:
    Code:
    <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?
     
  8. LeeS

    LeeS Imperator

    Joined:
    Jul 23, 2013
    Messages:
    7,241
    Location:
    Illinois, USA
    This statement is incorrect usage of the SELECT command, really:
    Code:
    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
    Code:
    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
    Code:
    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:
    Code:
    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.
     
    Serp likes this.

Share This Page