simple query example, writing bytes

[petaflot]

This is sort of a noob question and not really an issue...

PgConsole is cool, but not really helpful in production : I can't imagine a case where I would use a microcontroller on a serial terminal to interract with my postgresql server ; however, pulling data from the server or logging stuff to it likely to be useful and used quite a lot.

The issue I'm facing (C++ is not one of my strong suits, more of a python dude) is a simple insert statement with bytes. Below is a modified version of doPG() that supposedly logs a message to postgresql ; in my case all query arguments are bytes, but I suppose examples with other data types would be appreciated by people living "in outer space".

the code below sends a string and not bytes. Again, C++ is really not something I feel comfortable with.

I suggest adding an example to the doc, next to PgConsole, similar to the code below (another example with "SELECT rows FROM table" with row content assigned to variables would be cool) :

int pg_status = 0;                   
void pgLogger( char message[] ) {    
#ifndef USE_ARDUINO_ETHERNET         
        checkConnection();           
        if (WiFiStatus == WL_CONNECTED) {
#endif                               
                char *msg;           
                int rc;
                if (!pg_status) {
                        conn.setDbLogin(PGIP,
                                user,
                                password,
                                dbname,
                                "utf8");
                        pg_status = 1;
                        return;
                }
                if (pg_status == 2) {
                        // TODO convert message to bytes
                        if (conn.execute(String("INSERT into mytable ( col0 ) VALUES (")+message+String("');"))) goto error;
                        Serial.println("Working...");
                        pg_status = 3;
                }
                if (pg_status == 3) {
                        rc=conn.getData();
                        int i;
                        if (rc < 0) goto error;
                        if (!rc) return;
                        if (rc & PG_RSTAT_HAVE_COLUMNS) {
                                for (i=0; i < conn.nfields(); i++) {
                                        if (i) Serial.print(" | ");
                                        Serial.print(conn.getColumn(i));
                                }
                                Serial.println("\n==========");
                        }
                        else if (rc & PG_RSTAT_HAVE_ROW) {
                                for (i=0; i < conn.nfields(); i++) {
                                        if (i) Serial.print(" | ");
                                        msg = conn.getValue(i);
                                        if (!msg) msg=(char *)"NULL";
                                        Serial.print(msg);
                                }
                                Serial.println();
                        }
                        else if (rc & PG_RSTAT_HAVE_SUMMARY) {
                                Serial.print("Rows affected: ");
                                Serial.println(conn.ntuples());
                        }
                        else if (rc & PG_RSTAT_HAVE_MESSAGE) {
                                msg = conn.getMessage();
                                if (msg) Serial.println(msg);
                        }
                }
                return;
#ifndef USE_ARDUINO_ETHERNET
        }
#endif

error:  
        msg = conn.getMessage();
        if (msg) Serial.println(msg);
        else Serial.println("UNKNOWN ERROR");
        if (conn.status() == CONNECTION_BAD) {
                Serial.println("Connection is bad");
                pg_status = -1;
        }
}                                    

[ethanak]

Your example is incorrect.
Strings must be properly escaped, in your example are not. You should use executeFormat to prepare query with parameters, something like:

message="O'Neil";
conn.executeFormat(0,"insert into mytable(col0) values(%s)", message);

conn.execute() may be used only for previously prepared queries.

[petaflot]

thank you very much for the details. IMHO this should really be available in the examples.

so, far, I have not seen any difference between sending char[x] and byte[x] ; is this to be expected? even special chars such as "º" seem to go through properly (although I was expecting some sort of garbled output). I have not explicitly tried non-ascii chars yet, such as some from latin9 (ie. èàéâç...)

so far too, the answer you provided works with byte[x] and also with char[x] ; not with String though but I still have to figure out how C++ deals with these ; as a side note I have been waiting for python3 to become available because it makes a clear distinction between bytestrings and strings, not sure how this applies here and I still fail to see how String() is different than char[x]. maybe you have a clue?