Jump to content
  • 0

Query_sql error


Question

Posted

Hey guys, looks like I need some help.

I have 3 tables, mob_db_re, mob_drop_re, and item_db_re.
mob_drop_re is created with this ddl statement:

CREATE TABLE `mob_drop_re` (
    `mobid` mediumint(9) unsigned NOT NULL default '0',
    `dtype` smallint(5) unsigned NOT NULL default '0',
    `index` smallint(5) unsigned NOT NULL,
    `nameid` smallint(5) unsigned NOT NULL default '0',
    `rate` smallint(9) unsigned NOT NULL default '0',
    `stealProtected` bool default false,
    `randomOptionGroup` text,

    PRIMARY KEY (`mobId`, `dtype`, `index`)

) ENGINE=MyISAM;

 

I'm working on the card_seller in npc/custom/card_seller.txt, and I get an error at the first query_sql call. Here's the code:

OnInit:
    if (checkre(0)) {
        .@mob_db$  = "mob_db_re";
        .@mob_drop$ = "mob_drop_re";
        .@item_db$ = "item_db_re";
    } else {
        .@mob_db$  = "mob_db";
        .@mob_drop$ = "mob_drop";
        .@item_db$ = "item_db";
    }
    freeloop 1;
    .@total = query_sql("SELECT DISTINCT LEFT(`name_japanese`, 1) AS alphabets FROM `"+ .@item_db$ +"` RIGHT JOIN `"+ .@mob_drop$ +"` ON `"+ .@mob_drop$ +"`.`nameid` = `"+ .@item_db$ +"`.`id` RIGHT JOIN `"+.@mob_db$ +"` ON `"+ .@mob_db$ +"`.`ID` = `"+ .@mob_drop$ +"`.`mobid` WHERE `"+ .@item_db$ +"`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) & 0x4000000 ORDER BY alphabets;", .alphabet$);
    for ( .@i = 0; .@i < .@total; .@i++ ) {
        .alphabet_menu$ = .alphabet_menu$ + .alphabet$[.@i] +" Cards:";
        .@nb = query_sql("SELECT `"+.@item_db$ +"`.`id` FROM `"+ .@item_db$ +"` RIGHT JOIN `"+ .@mob_drop$ +"` ON `"+ .@mob_drop$ +"`.`nameid` = `"+ .@item_db$ +"`.`id` RIGHT JOIN `"+.@mob_db$ +"` ON `"+ .@mob_db$ +"`.`ID` = `"+ .@mob_drop$ +"`.`mobid` WHERE `"+ .@item_db$ +"`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) & 0x4000000 AND LEFT(`name_japanese`, 1) = '"+ .alphabet$[.@i]+"' GROUP BY `name_japanese` ORDER BY `name_japanese` LIMIT 128;", .@id);
        npcshopdelitem "card_mob#"+ .alphabet$[.@i], 501;
        for ( .@j = 0; .@j < .@nb; .@j++ )
            npcshopadditem "card_mob#"+ .alphabet$[.@i], .@id[.@j], 1000000;
    }
    freeloop 0;
    end;

 

The script creates a sql statement as so:

 

SELECT DISTINCT LEFT(`name_japanese`, 1) AS alphabets FROM `item_db_re` RIGHT JOIN `mob_drop_re` ON `mob_drop_re`.`nameid` = `item_db_re`.`id` RIGHT JOIN `mob_db_re` ON `mob_db_re`.`ID` = `mob_drop_re`.`mobid` WHERE `item_db_re`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) &0x4000000 ORDER BY alphabets;

 

It works fine on MySQL Workbench, but produces an error when using it in the script:

[SQL]: DB error - Unknown column 'mob_drop_re.nameid' in 'on clause'
[Debug]: at script.cpp:16666 - SELECT DISTINCT LEFT(`name_japanese`, 1) AS alpha
bets FROM `item_db_re` RIGHT JOIN `mob_drop_re` ON `mob_drop_re`.`nameid` = `ite
m_db_re`.`id` RIGHT JOIN `mob_db_re` ON `mob_db_re`.`ID` = `mob_drop_re`.`mobid`
 WHERE `item_db_re`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) &
0x4000000 ORDER BY alphabets;
[Debug]: Source (NPC): Card Seller at prontera (155,177)

 

Can anyone help?

3 answers to this question

Recommended Posts

  • 0
Posted (edited)
6 hours ago, Nitrous said:

Hey guys, looks like I need some help.

I have 3 tables, mob_db_re, mob_drop_re, and item_db_re.
mob_drop_re is created with this ddl statement:


CREATE TABLE `mob_drop_re` (
    `mobid` mediumint(9) unsigned NOT NULL default '0',
    `dtype` smallint(5) unsigned NOT NULL default '0',
    `index` smallint(5) unsigned NOT NULL,
    `nameid` smallint(5) unsigned NOT NULL default '0',
    `rate` smallint(9) unsigned NOT NULL default '0',
    `stealProtected` bool default false,
    `randomOptionGroup` text,

    PRIMARY KEY (`mobId`, `dtype`, `index`)

) ENGINE=MyISAM;

 

I'm working on the card_seller in npc/custom/card_seller.txt, and I get an error at the first query_sql call. Here's the code:


OnInit:
    if (checkre(0)) {
        .@mob_db$  = "mob_db_re";
        .@mob_drop$ = "mob_drop_re";
        .@item_db$ = "item_db_re";
    } else {
        .@mob_db$  = "mob_db";
        .@mob_drop$ = "mob_drop";
        .@item_db$ = "item_db";
    }
    freeloop 1;
    .@total = query_sql("SELECT DISTINCT LEFT(`name_japanese`, 1) AS alphabets FROM `"+ .@item_db$ +"` RIGHT JOIN `"+ .@mob_drop$ +"` ON `"+ .@mob_drop$ +"`.`nameid` = `"+ .@item_db$ +"`.`id` RIGHT JOIN `"+.@mob_db$ +"` ON `"+ .@mob_db$ +"`.`ID` = `"+ .@mob_drop$ +"`.`mobid` WHERE `"+ .@item_db$ +"`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) & 0x4000000 ORDER BY alphabets;", .alphabet$);
    for ( .@i = 0; .@i < .@total; .@i++ ) {
        .alphabet_menu$ = .alphabet_menu$ + .alphabet$[.@i] +" Cards:";
        .@nb = query_sql("SELECT `"+.@item_db$ +"`.`id` FROM `"+ .@item_db$ +"` RIGHT JOIN `"+ .@mob_drop$ +"` ON `"+ .@mob_drop$ +"`.`nameid` = `"+ .@item_db$ +"`.`id` RIGHT JOIN `"+.@mob_db$ +"` ON `"+ .@mob_db$ +"`.`ID` = `"+ .@mob_drop$ +"`.`mobid` WHERE `"+ .@item_db$ +"`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) & 0x4000000 AND LEFT(`name_japanese`, 1) = '"+ .alphabet$[.@i]+"' GROUP BY `name_japanese` ORDER BY `name_japanese` LIMIT 128;", .@id);
        npcshopdelitem "card_mob#"+ .alphabet$[.@i], 501;
        for ( .@j = 0; .@j < .@nb; .@j++ )
            npcshopadditem "card_mob#"+ .alphabet$[.@i], .@id[.@j], 1000000;
    }
    freeloop 0;
    end;

 

The script creates a sql statement as so:

 


SELECT DISTINCT LEFT(`name_japanese`, 1) AS alphabets FROM `item_db_re` RIGHT JOIN `mob_drop_re` ON `mob_drop_re`.`nameid` = `item_db_re`.`id` RIGHT JOIN `mob_db_re` ON `mob_db_re`.`ID` = `mob_drop_re`.`mobid` WHERE `item_db_re`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) &0x4000000 ORDER BY alphabets;

 

It works fine on MySQL Workbench, but produces an error when using it in the script:


[SQL]: DB error - Unknown column 'mob_drop_re.nameid' in 'on clause'
[Debug]: at script.cpp:16666 - SELECT DISTINCT LEFT(`name_japanese`, 1) AS alpha
bets FROM `item_db_re` RIGHT JOIN `mob_drop_re` ON `mob_drop_re`.`nameid` = `ite
m_db_re`.`id` RIGHT JOIN `mob_db_re` ON `mob_db_re`.`ID` = `mob_drop_re`.`mobid`
 WHERE `item_db_re`.`type` = 6 AND `name_english` LIKE '%_Card' AND ~(`MODE`) &
0x4000000 ORDER BY alphabets;
[Debug]: Source (NPC): Card Seller at prontera (155,177)

 

Can anyone help?

What is the expected result in modifying this card seller?

Edited by GodKnows Jhomz
  • 0
Posted

To make the automated build work.

I split the mobdb and mobdrop tables in the database, which requires me to fix this script.

The first SQL statement should return a list of letters in alphabetical order so that there exists at least one normal monster that drops a card that starts with that letter.

 

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...