SQL queries using GROUP BY - Printable Version

SQL queries using GROUP BY - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Help Archive (https://sampforum.blast.hk/forumdisplay.php?fid=89)
+---- Thread: SQL queries using GROUP BY (/showthread.php?tid=100563)

SQL queries using GROUP BY - Dabombber - 05.10.2009

I'm not sure if this is a bug or some SQL thing, but using GROUP BY in SQL queries seems to break db_get_field_assoc.

pawn Code:

#include <a_samp>

public OnFilterScriptInit()
{
    new DB:db = db_open("test.sqlite");
    if(db) {
        db_free_result(db_query(db, "CREATE TABLE IF NOT EXISTS `Test` ( `string` TEXT NOT NULL DEFAULT '' , `value` INTEGER NOT NULL DEFAULT 0 ); DELETE FROM `Test`"));

        db_free_result(db_query(db, "INSERT INTO `Test` ( `string` , `value` ) VALUES ( 'abc' , 123 )"));
        db_free_result(db_query(db, "INSERT INTO `Test` ( `string` , `value` ) VALUES ( 'def' , 456 )"));
        db_free_result(db_query(db, "INSERT INTO `Test` ( `string` , `value` ) VALUES ( 'hij' , 789 )"));

        new DBResult:result = db_query(db, "SELECT `string` , `value` FROM `Test`"),
            rows = db_num_rows(result);
        for(new i; i < rows; i++) {
            new string[20],
                value;
            db_get_field_assoc(result, "value", string, sizeof(string));
            value = strval(string);
            db_get_field_assoc(result, "string", string, sizeof(string));
            printf("String: \"%s\", Value: %i", string, value);
            db_next_row(result);
        }
        db_free_result(result);
        db_close(db);
    }
    return 1;
}

Using this gives

Quote:

String: "abc", Value: 123
String: "def", Value: 456
String: "hij", Value: 789

as expected, but when the SELECT query is changed to

pawn Code:

"SELECT `string` , `value` FROM `Test` GROUP BY `string`"

(or grouping by value), it gives

Quote:

String: "", Value: 0
String: "", Value: 0
String: "", Value: 0

It does work properly when using the query

pawn Code:

"SELECT `string` AS `string` , `value` AS `value` FROM `Test` GROUP BY `string`"

but shouldn't that be the same?

Using db_field_name without the AS gives "`string`" and "`value`" instead of "string" and "value", maybe this is the problem.

Re: SQL queries using GROUP BY - Dabombber - 05.10.2009

Not using quotes works but that's not really the point.

Quote:

Originally Posted by http://www.sqlite.org/lang_keywords.html

SQLite adds new keywords from time to time when it takes on new features. So to prevent your code from being broken by future enhancements, you should normally quote any identifier that is an English language word, even if you do not have to.

Code:

#include <a_samp>

public OnFilterScriptInit()
{
	new DB:db = db_open("test.sqlite");
	if(db) {
		db_free_result(db_query(db, "DROP TABLE IF EXISTS `Test`; CREATE TABLE `Test` ( `a` TEXT NOT NULL DEFAULT '', `b` TEXT NOT NULL DEFAULT '' , `c` TEXT NOT NULL DEFAULT '' , `d` TEXT NOT NULL DEFAULT '' , `e` TEXT NOT NULL DEFAULT '' )"));

		db_free_result(db_query(db, "INSERT INTO `Test` ( `a` , `b` ,`c` , `d` ,`e` ) VALUES ( 'abc' , 'def', 'ghi' , 'jkl' , 'mno' )"));

		new DBResult:result = db_query(db, "SELECT a , `b` , [c] , \"d\" , `e` AS `e` FROM Test GROUP BY `a`"),
			rows = db_num_rows(result),
			string[128];
		for(new i; i < rows; i++) {
			new fields = db_num_fields(result);
			format(string, sizeof(string), "%sRow %i\n", string, i);
			for(new j; j < fields; j++) {
				new field_name[20],
					field_value[20];
				db_field_name(result, j, field_name, sizeof(field_name));
				db_get_field(result, j, field_value, sizeof(field_value));
				format(string, sizeof(string), "%s %s: %s\n", string, field_name, field_value);
			}
			db_next_row(result);
		}
		print(string);
		db_free_result(result);
		db_close(db);
	}
	return 1;
}

Quote:

Row 0
a: abc
`b`: def
[c]: ghi
"d": jkl
e: mno

For some reason sa-mp isn't striping the quotes from the field name unless AS is used.