diff options
Diffstat (limited to 'software/michi_funcs/db_funcs.cpp')
| -rw-r--r-- | software/michi_funcs/db_funcs.cpp | 404 |
1 files changed, 0 insertions, 404 deletions
diff --git a/software/michi_funcs/db_funcs.cpp b/software/michi_funcs/db_funcs.cpp deleted file mode 100644 index c1a2f56..0000000 --- a/software/michi_funcs/db_funcs.cpp +++ /dev/null @@ -1,404 +0,0 @@ -/* -How to use db_funcs: -1. in main.cpp #include "db_funcs.h" -2. in main.cpp, in init() - while (network_connected != 1) { vTaskDelay(2000 / portTICK_PERIOD_MS); } - strcpy(dbnam ,(char *)"c10mm"); - strcpy(pg_server_ip ,(char *)"192.168.178.100"); - -2.a pqConnection = new TpgConnection(); // Verbindungsobject erzeugen -2.b if ( pqConnection->db_connect( pg_server_ip, srvpo, dbnam, PGUser, PGPassword, PGCharset) == 0) //verbinden -2.c query1 = new TResultSet( pqConnection->PGconn ); -2.d query1->db_query("select type, state from items limit 10;"); -2.4 for (int iRow = 0; iRow < query1->NumRows-1; iRow++) - { - std::cout << query1->Zeile[iRow].Spalte[0] << " | "; - std::cout << query1->Zeile[iRow].Spalte[1] << " | "; - printf("\n"); - } - -3. query1->db_query("select bla bla;"); //naechste Abfrage - //oder neues Abfrage-Object erstellen: query2 = new TResultSet( pqConnection->PGconn ); - -4. in main.cpp, main_loop() - db_loop(); //db_loop regelmaessig aufrufen, damit NOTIFY Nachrichten verarbeitet werden. - - -5. Auf Notification reagieren: Vor der mail() Schleife die NOTIFIY Nachrichten registrieren - db_query("listen door_open"); //register listen handler for NOTIFY door_open <door_no> - db_query("listen door_close"); //register listen handler for NOTIFY door_close <door_no> - -*/ -#include <sys/types.h> -#include <stdio.h> -#include <string.h> -#include "freertos/FreeRTOS.h" -#include "freertos/task.h" -#include "esp_netif.h" -#include "esp_eth.h" -#include "esp_log.h" -#include "SimplePgSQL.h" - -//For ResultSet: -#include "db_funcs.h" -#include <vector> -#include <iostream> -#include <string> - -static int pg_flags = 0; - -// PGSQL -#define PGBufferSize 16384 -#define PGCharset "utf-8" -#define PGUser "micha" -#define PGPassword "micha" -const TickType_t delay1ms = 1 / portTICK_PERIOD_MS; -const TickType_t delay10ms = 10 / portTICK_PERIOD_MS; -const TickType_t delay100ms = 100 / portTICK_PERIOD_MS; - -//static PGconnection *PGconn = NULL; -static unsigned char PGbuffer[PGBufferSize]; -static char lastNotify[40]; - -static const char *PGTAG = "SCALADIS-PG"; - -static unsigned char pg_lastError_string[80]; - -//TResultSet ResultSetObject(1); - -TpgConnection::TpgConnection() -{ - PGconn = NULL; -} - - -int TpgConnection::db_connect(const char *pg_server_ip, int pg_port, const char *s_db_name, const char *s_db_user, const char *s_db_passwd, const char *s_db_charset) -{ -//CONNECTION_OK, CONNECTION_BAD, CONNECTION_NEEDED, /* setDbLogin() needed */ -//CONNECTION_AWAITING_RESPONSE, /* Waiting for a response from the postmaster. */ -// CONNECTION_AUTH_OK - int loop_fuse = 0; - ESP_LOGI(PGTAG,"db_connect Enter"); - PGconn = new PGconnection(pg_flags, PGbuffer, PGBufferSize); - if (PGconn == NULL) { ESP_LOGE(PGTAG, "error creating PGconn object"); } - - last_pgError = PGconn->setDbLogin(pg_server_ip, pg_port, s_db_name, s_db_user, s_db_passwd, s_db_charset); - - while ( (last_pgError != CONNECTION_OK) || (last_pgError != CONNECTION_BAD) - || (last_pgError != CONNECTION_NEEDED) ) - { - last_pgError = PGconn->status(); - vTaskDelay(delay10ms); - loop_fuse++; - if (loop_fuse > 100) { ESP_LOGE(PGTAG, "PGsetDbLogin break after 10 attempts. "); break; } - } - - //ResultSetObject.Zeile.resize(1); - - switch (last_pgError) { - case CONNECTION_NEEDED: { ESP_LOGE(PGTAG,"connection needed: %s", PGconn->getMessage()); return -1; } - case CONNECTION_OK: { ESP_LOGI(PGTAG,"db_connect sucessful (OK) to db %s", s_db_name); return 0; } - case CONNECTION_BAD: { ESP_LOGE(PGTAG,"ERROR: %s", PGconn->getMessage()); return -1; } - case CONNECTION_AWAITING_RESPONSE: { ESP_LOGI(PGTAG,"ERROR AWAITING_RESPONSE: %s", PGconn->getMessage()); return -1; } - case CONNECTION_AUTH_OK: { ESP_LOGI(PGTAG,"db_connect sucessful (AUTH_OK) to db %s", s_db_name); return 0; } - } - - - ESP_LOGI(PGTAG,"ResultSet created"); - - ESP_LOGI(PGTAG,"db_connect EXIT. last_pgError=%d", last_pgError); - return 0; -} - -int TpgConnection::db_disconnect(void) -{ - ESP_LOGI(PGTAG,"db_disconnect ENTER"); - if (PGconn == NULL) { ESP_LOGE(PGTAG, "error destroy PGconn object. PGconn = NULL"); return -1; } - PGconn->close(); - delete PGconn; - PGconn = NULL; - ESP_LOGI(PGTAG,"db_disconnect EXIT"); - return 0; -} - -void TpgConnection::db_loop(void) -{ -// bool readyToQuery = false; - char *msg = NULL; - last_pgError = PGconn->status(); - if (last_pgError == CONNECTION_BAD || last_pgError == CONNECTION_NEEDED) - { - ESP_LOGE(PGTAG, "ERROR: %s", PGconn->getMessage() ); - } else - { - last_pgError = PGconn->getData(); - if (last_pgError < 0) - { - ESP_LOGE(PGTAG, "ERROR: %s", PGconn->getMessage() ); - } else - { - if (last_pgError > 0) - { - //some data arrive - msg = PGconn->getMessage(); - if (msg != NULL) - { - sprintf( lastNotify, msg); //copy message, to save the NOTIFY - ESP_LOGI(PGTAG,"NOTIFY %s", lastNotify); - } - } - } - } -} - -// ******************************************************************************************** - -TResSetRows::TResSetRows() -{ - Spalte.resize(1); - //ESP_LOGE(PGTAG, "--------------------------> TResSetRows constructor called"); -} - - -TResSetRows::~TResSetRows() -{ - Spalte.resize(0); - //ESP_LOGE(PGTAG, "--------------------------> TResSetRows destructor called"); -} - -TResultSet::TResultSet(PGconnection *PGconnFromCon) -{ - PGconn = PGconnFromCon; - if (PGconnFromCon == NULL) - { - ESP_LOGE(PGTAG, "ERROR"); - ESP_LOGE(PGTAG, "TResultSet::TResultSet(PGconnection *PGconnFromCon) -> PGconnFromCon ist NULL!!!!!!"); - ESP_LOGE(PGTAG, ""); - } - Zeile.resize(1); - NumRows = 0; - //ESP_LOGE(PGTAG, "--------------------------> TResultSet constructor called"); -} - -TResultSet::~TResultSet() -{ - //Speicher aufraeumen - ESP_LOGI(PGTAG,"\nTResultSet Destructor called"); //fflush (stdout); -} - -int TResultSet::db_query(const char *sql) -{ - int last_pgError; - bool queryEnded; - -// ESP_LOGI(PGTAG,"db_query ENTER: %s", sql); fflush (stdout); - NumRows = 0;//reset NumRows. - - //sprintf(lbuf, "SELECT name,gname FROM accounts WHERE id=(cast(x'%s' AS int));", info); - last_pgError = PGconn->execute(sql); - if (last_pgError < 0) - { - printf("ERROR: TResultSet::db_query: PGexecute: %s", PGconn->getMessage()); - return last_pgError; - } else - { - vTaskDelay(1); - queryEnded = false; - while (queryEnded == false) - { - last_pgError = PGconn->getData(); - if (last_pgError & PG_RSTAT_HAVE_COLUMNS) { process_db_col(); } - if (last_pgError & PG_RSTAT_HAVE_ROW) { process_db_row(); } - if (last_pgError & PG_RSTAT_HAVE_SUMMARY) { process_db_summary(); } - if (last_pgError & PG_RSTAT_HAVE_MESSAGE) { process_db_haveMsg(); } - if (last_pgError & PG_RSTAT_READY) { process_db_ready(); } - if (last_pgError & PG_RSTAT_COMMAND_SENT) { process_db_cmd_send(); } - if (last_pgError & PG_RSTAT_HAVE_ERROR) { process_db_have_error(); - printf("\n Error from Postgres: "); - if ( PGconn->getMessage() != NULL ) { printf("---> %s\n", PGconn->getMessage() ); } - fflush (stdout); - queryEnded = true; } - if (last_pgError & PG_RSTAT_HAVE_NOTICE) { process_db_have_notice(); } - - if (last_pgError < 0) - { - queryEnded = true; - printf("Get Data Error: %d\n", last_pgError ); - if ( PGconn->getMessage() != NULL ) { printf("---> %s\n", PGconn->getMessage() ); } - fflush (stdout); - } - if (last_pgError == CONNECTION_BAD || last_pgError == CONNECTION_NEEDED) - { - queryEnded = true; - //seem to be the normal end. No Error Message. - //ESP_LOGE(PGTAG, "ERROR: CONNECTION BAD or NEEDED:" ); - //if ( PGconn->getMessage() != NULL ) { ESP_LOGE(PGTAG, "----> %s", PGconn->getMessage() ); } - } - if (last_pgError == 0) { queryEnded = true; //no more data } - } - - - } //while (queryEnded == false) - } -// ESP_LOGI(PGTAG,"db_query EXIT"); fflush (stdout); - return 0; -} - -int TResultSet::db_exec(const char *sql) -{ - int last_pgError; - bool queryEnded; - -// ESP_LOGI(PGTAG,"db_query ENTER: %s", sql); fflush (stdout); - NumRows = 0;//reset NumRows. - - last_pgError = PGconn->execute(sql);//call old version from 17.02.2022 - if (last_pgError < 0) - { - return last_pgError; - } else - { - vTaskDelay(1); - queryEnded = false; - while (queryEnded == false) - { - last_pgError = PGconn->getData(); - if (last_pgError & PG_RSTAT_HAVE_COLUMNS) { process_db_col(); } - if (last_pgError & PG_RSTAT_HAVE_ROW) { process_db_row(); } - if (last_pgError & PG_RSTAT_HAVE_SUMMARY) { process_db_summary(); } - if (last_pgError & PG_RSTAT_HAVE_MESSAGE) { process_db_haveMsg(); } - if (last_pgError & PG_RSTAT_READY) { process_db_ready(); } - if (last_pgError & PG_RSTAT_COMMAND_SENT) { process_db_cmd_send(); } - if (last_pgError & PG_RSTAT_HAVE_ERROR) { - process_db_have_error(); - printf("\n Error from Postgres: "); - if ( PGconn->getMessage() != NULL ) { printf("---> %s\n", PGconn->getMessage() ); } - fflush (stdout); - queryEnded = true; } - if (last_pgError & PG_RSTAT_HAVE_NOTICE) { process_db_have_notice(); } - - if (last_pgError < 0) - { - queryEnded = true; - printf("Insert/Update Error: %d\n", last_pgError ); - if ( PGconn->getMessage() != NULL ) { printf("---> %s\n", PGconn->getMessage() ); } - fflush (stdout); - } - if (last_pgError == CONNECTION_BAD || last_pgError == CONNECTION_NEEDED) - { - queryEnded = true; - //seem to be the normal end. No Error Message. - //ESP_LOGE(PGTAG, "ERROR: CONNECTION BAD or NEEDED:" ); - //if ( PGconn->getMessage() != NULL ) { ESP_LOGE(PGTAG, "----> %s", PGconn->getMessage() ); } - } - if (last_pgError == 0) { queryEnded = true; //no more data } - } - - - } //while (queryEnded == false) - } - fflush (stdout); - return 0; -} - - -void TResultSet::process_db_col(void) -{ - int nCols; - int iCurrCol; -// ESP_LOGI(PGTAG,"---> PG_RSTAT_HAVE_COLS"); - printf("Spalten gefunden: \n" ); - fflush (stdout); - - //iRow = 1; // erste Zeile hat die Spaltennamen! - CurrentRow = 1; - nCols = PGconn->nfields(); - this->NumCols = nCols; - Zeile[0].numCols = nCols; - - Zeile.resize( 1 ); //erste Zeile - Zeile[0].Spalte.resize(nCols); -// printf("Set Result buffer auf Spalten: %d\n", nCols ); - fflush (stdout); - //alloc 1 row only, for the column names. More is added later. - for (iCurrCol = 0; iCurrCol < nCols; iCurrCol++) - { - if (iCurrCol) printf(" | "); - this->Zeile[0].Spalte[iCurrCol] = PGconn->getColumn(iCurrCol); -// std::cout << Zeile[0].Spalte[iCurrCol] ; -// fflush (stdout); - } - - printf("\n==========\n"); - fflush(stdout); -} - -void TResultSet::process_db_row(void) -{ - //char *msg; - int iRow; - int iCurrField; - std::string sFeld; -// ESP_LOGI(PGTAG,"---> PG_RSTAT_HAVE_ROW"); - this->CurrentRow++; - NumRows++; - iRow = this->CurrentRow; - this->Zeile.resize( iRow ); //naechste Zeile - this->Zeile[iRow-1].Spalte.resize( this->Zeile[0].numCols ); - -// printf("Row %d:", iRow ); - for (iCurrField = 0; iCurrField < PGconn->nfields(); iCurrField++) - { - //printf(" | "); - //msg = PGconn->getValue( iCurrField ); - //copy the string, to save the result - if (!PGconn->getValue( iCurrField ) ) - { Zeile[iRow-1].Spalte[iCurrField].copy( (char *)"NULL", 0, 4); } - else - { - sFeld = PGconn->getValue( iCurrField ); - Zeile[iRow-1].Spalte[iCurrField] = sFeld; //assign string to string - } - //printf(" %s", msg);fflush(stdout); -// std::cout << Zeile[iRow-1].Spalte[iCurrField] << " | " ; - } -// std::cout << "\n "; -} - - - - - -void TResultSet::process_db_summary(void) -{ -// ESP_LOGI(PGTAG,"---> PG_RSTAT_HAVE_SUMMARY"); -} - -void TResultSet::process_db_haveMsg(void) -{ -// ESP_LOGI(PGTAG,"---> PG_RSTAT_HAVE_MESSAGE"); -} - -void TResultSet::process_db_ready(void) -{ -// ESP_LOGI(PGTAG,"---> PG_RSTAT_READY"); -} - -void TResultSet::process_db_cmd_send(void) -{ -// ESP_LOGI(PGTAG,"---> PG_RSTAT_COMMAND_SENT"); -} - -void TResultSet::process_db_have_error(void) -{ -// ESP_LOGE(PGTAG,"---> PG_RSTAT_HAVE_ERROR"); -} - -void TResultSet::process_db_have_notice(void) -{ -// ESP_LOGI(PGTAG,"---> PG_RSTAT_HAVE_NOTICE"); -} - - - - |