Simple PostgreSQL connector for Arduino, ESP32 and ESP8266.
Only simple queries are implemented. COPY is not implemented due to code size limit,
but probably will be for ESP32 only. Large objects are not implemented as obsolete
and never be.
Available authorization method are trust, password and md5.
Due to code size limit, md5 method may be disabled in compilation time,
decreasing code size for Arduino by some kilobytes.
All methods are asynchronous, but sometimes may block for a while in case of poor network connection.
As column names and notifications are rarely needed in microcontrollers applications, may be disabled. In this case only number of fields will be fetched from row description packet, and notification rows will be simply skipped.
All methods uses single internal buffer, allocated at setDbLogin and freed on close.
It's possible to provide external (statically allocated) buffer, which may be reused in rest of application.
Parameter progmem has no meaning for ESP32.
PGconnection(Client *c, int flags = 0, int memory = 0, char *foreignBuffer = NULL);Class constructor.
Client - Client instance (WiFi or Ethernet)flags - some flags:
PG_FLAG_IGNORE_NOTICES - ignore notices and notificationsPG_FLAG_IGNORE_COLUMNS - ignore column namesmemory - internal buffer size. Defaults to PG_BUFFER_SIZEforeignBuffer - static buffer addressint setDbLogin(IPAddress server,
const char *user,
const char *passwd = NULL,
const char *db = NULL,
const char *charset = NULL,
int port = 5432);Initialize connection.
server - IP address of backend serveruser - database user namepasswd - database user passworddb - database name (defaults to user name)charset - client encodingport - server portconnection status (see below)
int status(void);Check current connection status and perform authorization action if needed.
Must be called after setDbLogin until CONNECTION_OK, CONNECTION_BAD or CONNECTION_NEEDED.
Current connection status. May be one of:
CONNECTION_NEEDED - no connection yet, call setDbLogin.CONNECTION_OK - ready for queries.CONNECTION_BAD - connection can't be realized or was abandoned. Call close().CONNECTION_AWAITING_RESPONSE - Waiting for a response from the postmaster. Call status() again.CONNECTION_AUTH_OK - Received authentication; waiting for backend startup. Call status() again.void close(void);Send termination command if needed and close connection. Free internal buffers.
execute(const char *query, int progmem = 0);Send query to backend. Invalidates data in internal buffer.
query - PostgreSQL queryprogmem - if not zero, query is in Flash memoryNegative value on error or zero on success. In case of error you must check connection status - some errors invalidates connection.
int getData(void);Retrieve any response from backend.
Must be called after execute() until PG_RSTAT_READY.
May be called even in READY state and fetch possible notifications.
Each call invalidates data in internal buffer, so values returned by
getColumn(), getValue() and getMessage() will be valid only until next getData() or execute() call.
int dataStatus(void);Get current data status.
Combination of bits:
PG_RSTAT_READY - ready for next queryPG_RSTAT_COMMAND_SENT - command was sent to backendPG_RSTAT_HAVE_COLUMNS - column names in bufferPG_RSTAT_HAVE_ROW - row data in bufferPG_RSTAT_HAVE_SUMMARY - execution finished, number of affected rows availablePG_RSTAT_HAVE_ERROR - error message in bufferPG_RSTAT_HAVE_NOTICE - notice/notification in bufferchar *getColumn(int n);Get n-th column name if available
PG_RSTAT_HAVE_COLUMNS state or n out of range.char *getValue(int n);Get n-th row value if available
PG_RSTAT_HAVE_ROW statechar *getMessage(void);Get current error or notification message
Pointer to message text in internal buffer or NULL if no message.
int nfields(void);Get column count in result. Valid only after PG_RSTAT_HAVE_COLUMNS or first PG_RSTAT_HAVE_ROW.
Column count or zero if not known.
int ntuples(void);Get number of tuples in result. Valid only after PG_RSTAT_HAVE_SUMMARY.
SELECT or INSERT/UPDATE...RETURNINGINSERT, UPDATE or DELETEint escapeString(const char *inbuf, char *outbuf);Creates escaped version of literal. Single quotes and 'E' marker (if needed) will be added.
inbuf - string to escapeoutbuf - output buffer or NULL if we only count length of resultLength of escaped string
int escapeName(const char *inbuf, char *outbuf);Creates escaped version of name. Double quotes will be added.
inbuf - string to escapeoutbuf - output buffer or NULL if we only count length of resultLength of escaped string
int executeFormat(int progmem, const char *format, ...);Send formatted query to backend. Format string may be placed in Flash memory. Formatting sequences are:
Any % character not followed by 's', 'n', 'd', 'l' or '%' causes error. Query may be long, but results of any formatted value must fit in internal buffer.
progmem - indicates format in Flash memoryformat - formatting stringZero on success or negative value on error.