Kato_Yang wrote...
Hi all,
Since ODBC only accepts string data type, does it mean that the tables should only store strings, to avoid conversion/casting? My current tables have mostly integer fields and use the InnoDB engine(MySQL). I'm using integers to speed-up operations with indexes, yet I'm not sure about the real benefits.
For instance, some of my queries do not wrap passed data with ' ', since the passed data is integer. It works, but I guess that the data must be converted by ODBC before reaching MySQL itself? Would it be better to use string data in my tables and put the passed data between ' ' ?
Thank you!
Kato
Why wouldn't it work? ALL data you're passing into MySQL is string data in nwscript, but you can pass it as all sorts of types INTO MySQL. The single quotes are what strings are supposed to be bounded by when interacting with MySQL. You can pass int values in them, and MySQL will automatically parse them out, but there's absolutely no reason to pass them in as strings, or to hold them in strings in your tables, especially if you want to use that data interactively in int form.
You seem to have a fundamental confusion about how the data you're dealing with is stored. It MUST be in string format IN nwscript because of how it's hooked into the engine, but from that format it is passed into MySQL in a format that depends entirely on how you encoded your MySQL query, and on how you structured your table.You should choose whatever datatypes you like, based on the needs of your tasks.
Here are some sample table descriptions, along with our full table listing, to give you an idea of what we're doing.
mysql> show tables;
+----------------------+
| Tables_in_nwn |
+----------------------+
| arena |
| bankchest_items |
| class_list |
| delete_block |
| donor_list |
| dupe_track |
| fkymessageboard |
| guild_list |
| hardcore |
| hgll_feats |
| market |
| objdata |
| pcvars |
| plvars |
| portrait_duplicate |
| pwdata |
| qs_quests |
| qs_status |
| qs_synonyms |
| race_list |
| randomloot |
| reinc_items |
| reinc_slots |
| reinc_subexception |
| respawn_loss |
| rip_augments |
| rip_augprops |
| rip_augvars |
| rip_props |
| rip_safe_patterns |
| server_list |
| simtools |
| sort_100 |
| sort_110 |
| sort_111 |
| sort_112 |
| sort_113 |
| sort_114 |
| sort_115 |
| sort_116 |
| sort_117 |
| sort_118 |
| sort_119 |
| sort_210 |
| sort_211 |
| sort_212 |
| sort_213 |
| sort_214 |
| sort_215 |
| sort_216 |
| sort_217 |
| sort_218 |
| sort_219 |
| sort_310 |
| sort_311 |
| sort_312 |
| sort_313 |
| sort_314 |
| sort_315 |
| sort_316 |
| sort_317 |
| sort_318 |
| sort_319 |
| sort_410 |
| sort_411 |
| sort_412 |
| sort_413 |
| sort_414 |
| sort_415 |
| sort_416 |
| sort_417 |
| sort_418 |
| sort_419 |
| sub_abilities |
| sub_appearances |
| sub_feats |
| sub_languages |
| sub_list |
| sub_skills |
| sub_usable |
| user_early |
| user_list |
| user_message_history |
| user_messages |
| user_passwords |
| user_totals |
| wallet_balance |
| wallet_history |
+----------------------+
88 rows in set (0.01 sec)
mysql> describe user_messages;
+-----------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+-------------------+----------------+
| um_id | int(11) | NO | PRI | NULL | auto_increment |
| um_sender_name | varchar(64) | NO | | NULL | |
| um_sender_cdkey | varchar(16) | NO | | NULL | |
| um_recipient | varchar(64) | NO | MUL | NULL | |
| um_text | text | NO | | NULL | |
| um_guild | tinyint(4) | NO | | NULL | |
| um_tstamp | timestamp | NO | | CURRENT_TIMESTAMP | |
+-----------------+-------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)
mysql> describe bankchest_items;
+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| bc_id | bigint(20) | NO | PRI | NULL | auto_increment |
| bc_cdkey | varchar(32) | NO | MUL | NULL | |
| bc_chest | varchar(16) | NO | MUL | NULL | |
| bc_res | varchar(16) | NO | MUL | NULL | |
| bc_charges | tinyint(4) | NO | | NULL | |
| bc_stacksize | tinyint(4) | NO | | NULL | |
| bc_uniqueid | varchar(32) | YES | | NULL | |
| bc_name | varchar(255) | YES | | NULL | |
| bc_mods | text | YES | | NULL | |
| bc_vars | text | YES | | NULL | |
| bc_crafting | text | YES | | NULL | |
| bc_last | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+--------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.01 sec)
mysql> describe pcvars;
+----------+-----------------------------------------------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------------------------------------------+------+-----+-------------------+-------+
| pcv_uid | varchar(32) | NO | PRI | NULL | |
| pcv_type | enum('int','float','string','object','vector','location') | NO | PRI | NULL | |
| pcv_key | varchar(64) | NO | PRI | NULL | |
| pcv_val | text | YES | | NULL | |
| pcv_last | timestamp | NO | | CURRENT_TIMESTAMP | |
+----------+-----------------------------------------------------------+------+-----+-------------------+-------+
5 rows in set (0.00 sec)
mysql> describe simtools;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| name | varchar(63) | YES | | NULL | |
| prefix | varchar(7) | YES | | NULL | |
| type | tinyint(4) | YES | | NULL | |
| script | varchar(63) | YES | | NULL | |
| menu | varchar(63) | YES | | NULL | |
| description | varchar(511) | YES | | NULL | |
| text | varchar(511) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> describe randomloot;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| rl_res | varchar(16) | NO | PRI | | |
| rl_rarity | tinyint(4) | YES | | NULL | |
| rl_type | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> describe race_list;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| race_id | int(11) | NO | PRI | NULL | |
| race_name | varchar(255) | NO | | NULL | |
| race_abbr | varchar(
| NO | | NULL | |
| race_pc | tinyint(4) | NO | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
LMK if you have any questions.
Funky
