Jump to content
  • 0

Query_sql error

nitrous

Asked by nitrous,

 Share

Question

nitrous Poring

nitrous

Posted
  • Group:  Developer
  • Topic Count:  4
  • Topics Per Day:  0.00
  • Content Count:  141
  • Reputation:   45
  • Joined:  08/14/12
  • Last Seen:  
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?

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0
nitrous Poring

nitrous

Posted
  • Group:  Developer
  • Topic Count:  4
  • Topics Per Day:  0.00
  • Content Count:  141
  • Reputation:   45
  • Joined:  08/14/12
  • Last Seen:  
  • Author
Posted

Well I'm dumb, I'm connecting to the wrong database. Please ignore this post.

Link to comment
Share on other sites
  • 0
GodKnows Jhomz Poring

GodKnows Jhomz

Posted
  • Group:  Members
  • Topic Count:  38
  • Topics Per Day:  0.01
  • Content Count:  333
  • Reputation:   67
  • Joined:  09/05/12
  • Last Seen:  
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
Link to comment
Share on other sites
  • 0
nitrous Poring

nitrous

Posted
  • Group:  Developer
  • Topic Count:  4
  • Topics Per Day:  0.00
  • Content Count:  141
  • Reputation:   45
  • Joined:  08/14/12
  • Last Seen:  
  • Author
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.

 

Link to comment
Share on other sites

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.

×
 Share
Go to question listing
×
×
  • Create New...