SA-MP Forums

SA-MP Forums (https://forum.sa-mp.com/index.php)
-   Help Archive (https://forum.sa-mp.com/forumdisplay.php?f=89)
-   -   SQL queries using GROUP BY (https://forum.sa-mp.com/showthread.php?t=100563)

Dabombber 05/10/2009 10:47 AM

SQL queries using GROUP BY
 
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.

Dabombber 05/10/2009 07:47 PM

Re: SQL queries using GROUP BY
 
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.


All times are GMT. The time now is 01:20 PM.

Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.