SQL INSERT - tdslite.h Library

Projects Programming
1

Good morning. I am using the " tdslite.h" library to insert data into a Microsoft SQL DB. With the example I give you below, it works for me.

Ho estic provant amb un Arduino Mega

auto query{TDSL_PMEMSTR("INSERT INTO prjComptador (idComptador, valor) VALUES (7,150)")};
    Serial.println("Executing query: ");
    SERIAL_PRINTF("Executing query: ");    
    SERIAL_PRINTLNF_PROGMEM(query.raw_data());
    auto result = driver.execute_query(query);
    Serial.print("Rows affected: "); Serial.println(result.affected_rows);
    SERIAL_PRINTLNF("Rows affected: %d", result.affected_rows);
    delay(10000);

int a=0;
  int b=0;
  char idTaulaInsertar[6];
  char ValorInsertar[6];
  
  a = random(1,7);
  b = random(1,500);
  Serial.print("a : "); Serial.println(a);
  Serial.print("b : "); Serial.println(b);

  dtostrf( a , 1, 0, idTaulaInsertar);   // zero decimals
  dtostrf( b , 1, 0, ValorInsertar);   // zero decimals

  char INSERT_BODY[] = "INSERT INTO prjComptador (idComptador, valor) VALUES (%s,%s)";
  char QueryLN[256];
  sprintf(QueryLN, INSERT_BODY, idTaulaInsertar, ValorInsertar);    
  Serial.println(QueryLN);
  
  //auto query{TDSL_PMEMSTR("INSERT INTO prjComptador (idComptador, valor) VALUES (7,150)")};
  auto query{TDSL_PMEMSTR(QueryLN)};
  Serial.println("Executing query: "); 
  SERIAL_PRINTLNF_PROGMEM(query.raw_data());
  auto result = driver.execute_query(query);
  Serial.print("Rows affected: "); Serial.println(result.affected_rows);
    
  delay(10000);

I have an error in "auto"

unable to deduce 'auto' from expression error
Any idea how I can fix this? Thanks .

I also don't know if this is the best library to manage INSERTS to a SQL.

I need to be able to create the equivalent of this line:

auto query{TDSL_PMEMSTR("INSERT INTO prjComptador (idComptador, valor) VALUES (7,150)")};
2

In your second example, you declare QueryLN as a char array in memory. You then wrap it in TDSL_PMEMSTR() which I am guessing is a macro to deal with strings in PROGRAM MEMORY, which your variable is not.

3

thanks.
What I need to do is somehow create an INSERT using my variables, which will vary in each INSERT and I don't know how to do it.

"a" and "b", I must be able to enter them in the INSERT

a = random(1,7);
b = random(1,500);

I need to be able to create the equivalent of this line:

auto query{TDSL_PMEMSTR("INSERT INTO prjComptador (idComptador, valor) VALUES (7,150)")};
4

The generic way to achieve it would be like this. No idea how it suites the library that you use.

// the statement template
char *insertTemplate = "INSERT INTO prjComptador (idComptador, valor) VALUES (%d, %d)";
// buffer  to store completed insert statement; size might be overkill
char buffer[200];
// clear the buffer; makes sure that it will be null-terminated
memset(buffer, '\0', sizeof(buffer));
// create the insert statement
sprintf(buffer, sizeof(buffer) -1, insertTemplate, a, b);
// for demo, print to serial
Serial.println(buffer);

Not compiled.

5

Thanks. I already build the "query" correctly. But the auto function doesn't interpret it correctly.

6

I think the way is here, but it doesn't work for me either.

Not working:
auto result = driver.execute_query(QueryLN);

if it works !!!
auto result = driver.execute_query("INSERT INTO prjComptador (idComptador, valor) VALUES (7,150)");

7

I am testing and working on doing the INSERT like this:

int a=0;
  int b=0;
  char idTaulaInsertar[6];
  char ValorInsertar[6];
  
  a = random(1,7);
  b = random(1,500);
  Serial.print("Taula     a: "); Serial.println(a);
  Serial.print("Comptador b: "); Serial.println(b);
 
  auto query{TDSL_PMEMSTR("INSERT INTO prjComptador (idComptador, valor) VALUES (aa = @p0, bb = @p1)")};
  Serial.println("Executing query: ");
  SERIAL_PRINTLNF_PROGMEM(query.raw_data());
  tdsl::sql_parameter_int aa{a};
  tdsl::sql_parameter_int bb{b};
  tdsl::sql_parameter_binding params []{aa, bb};
  auto result = driver.execute_rpc(query, params, tdsl::rpc_mode::executesql, row_callback);
  Serial.print("Rows affected: "); Serial.println(result);
  Serial.println("Executing executat.");

It doesn't give me any error, but it doesn't do the INSERT!!!

8

do these have to be wrapped in (aa,bb)??

sorry, all i gots.. ~q

9

I think the right thing should be like this:

 auto query{TDSL_PMEMSTR("INSERT INTO prjComptador (idComptador, valor) VALUES (aa = @p0, bb = @p1)")};

But it doesn't work for me either!!!

10

it already works for me!!!

void loop() 
  {
  //Llop principal
  int a=0;
  int b=0;
  char idTaulaInsertar[6];
  char ValorInsertar[6];
  
  a = random(1,7);
  b = random(1,500);
  Serial.print("Hola a: "); Serial.println(a);
  Serial.print("Hola b: "); Serial.println(b);
  
  auto query{TDSL_PMEMSTR("INSERT INTO prjComptador (idComptador, valor) VALUES(@p0, @p1)")};
  SERIAL_PRINTF("Executing query: ");
  SERIAL_PRINTLNF_PROGMEM(query.raw_data());
  //tdsl::sql_parameter_varchar a{"test"};
  tdsl::sql_parameter_int idComptador{a};
  tdsl::sql_parameter_int valor{b};
  tdsl::sql_parameter_binding params []{idComptador, valor};
  auto result =  driver.execute_rpc(query, params, tdsl::rpc_mode::executesql, row_callback);
  Serial.print("Rows affected: "); Serial.println(result);
    
  delay(30000);
  }
11

The full program:

#include <SPI.h>
#include <Ethernet.h>
#include <tdslite.h>

// -------------------------------------------------------------------------------------------------
// Definiciosn per tdslite.h
#define SKETCH_ENABLE_SERIAL_OUTPUT

#if defined SKETCH_ENABLE_SERIAL_OUTPUT

#define SERIAL_PRINTF_PROGMEM(FMTPM, ...)                                                          \
    char buf [64] = {};                                                                            \
    snprintf_P(buf, sizeof(buf), FMTPM, ##__VA_ARGS__);                                            \
    Serial.print(buf);

#define SERIAL_PRINTF(FMTSTR, ...)                                                                 \
    [&]() {                                                                                        \
        /* Save format string into program */                                                      \
        /* memory to save flash space */                                                           \
        static const char __fmtpm [] PROGMEM = FMTSTR;                                             \
        SERIAL_PRINTF_PROGMEM(__fmtpm, ##__VA_ARGS__)                                              \
    }()

#define SERIAL_PRINTLNF_PROGMEM(FMTPM, ...)                                                        \
    SERIAL_PRINTF_PROGMEM(FMTPM, ##__VA_ARGS__)                                                    \
    Serial.println("");                                                                            \
    Serial.flush()

#define SERIAL_PRINTLNF(FMTSTR, ...)                                                               \
    SERIAL_PRINTF(FMTSTR, ##__VA_ARGS__);                                                          \
    Serial.println("");                                                                            \
    Serial.flush()

#define SERIAL_PRINT_U16_AS_MB(U16SPAN)                                                            \
    [](tdsl::u16char_view v) {                                                                     \
        for (const auto ch : v) {                                                                  \
            Serial.print(static_cast<char>(ch));                                                   \
        }                                                                                          \
    }(U16SPAN)
#else
#define SERIAL_PRINTF_PROGMEM(FMTPM, ...)
#define SERIAL_PRINTF(FMTSTR, ...)
#define SERIAL_PRINTLNF_PROGMEM(FMTPM, ...)
#define SERIAL_PRINTLNF(FMTSTR, ...)
#define SERIAL_PRINT_U16_AS_MB(U16SPAN)
#endif
// -------------------------------------------------------------------------------------------------

// Enter a MAC address for your controller below.
// Newer Ethernet shields have a MAC address printed on a sticker on the shield
byte mac[] = { 0x00, 0xAA, 0xBB, 0xCC, 0xDE, 0x04};

tdsl::uint8_t net_buf [768] = {};
tdsl::arduino_driver<EthernetClient> driver{net_buf};


void setup() 
  {
  Serial.begin(9600);
  Serial.println("Initialize Ethernet with DHCP:");
  if (Ethernet.begin(mac) == 0) 
    {
    Serial.println("Failed to configure Ethernet using DHCP");
    if (Ethernet.hardwareStatus() == EthernetNoHardware) 
      {
      Serial.println("Ethernet shield was not found.  Sorry, can't run without hardware. :(");
      } 
    else if (Ethernet.linkStatus() == LinkOFF) 
    {
      Serial.println("Ethernet cable is not connected.");
    }
    // no point in carrying on, so do nothing forevermore:
    while (true) 
      {
      delay(1);
      }
    }
  // print your local IP address:
  Serial.print("My IP address: ");
  Serial.println(Ethernet.localIP());


  //****************************************************
  // Initialize tdslite

  decltype(driver)::progmem_connection_parameters params;
  // Server's hostname or IP address.
  params.server_name = TDSL_PMEMSTR("x.x.x.x"); // WL
  // SQL server port number
  params.port        = 1433; // default port is 1433
  // SQL server login user
  params.user_name   = TDSL_PMEMSTR("user");
  // SQL server login user password
  params.password    = TDSL_PMEMSTR("pw");
  // Client name(optional)
  params.client_name = TDSL_PMEMSTR("arduino mega");
  // App name(optional)
  params.app_name    = TDSL_PMEMSTR("sketch");
  // Database name(optional)
  params.db_name     = TDSL_PMEMSTR("NomBD");
  // TDS packet size
  // Recommendation: Half of the network buffer.
  // This is the PDU size that TDS protocol will use.
  // Given that the example has 768 bytes of network buffer space,
  // we set this to 512 to allow some headroom for fragmentation.
  params.packet_size = {512};
  auto result = driver.connect(params);
  
  if (not(decltype(driver)::e_driver_error_code::success == result)) 
    {
      Serial.println("Error: Database connection failed!");
      // Database connection failed.
      while (true) 
        {
        delay(1000);
          }         
    }
  Serial.println("Fi initializing tdslite"); 
  // Fi Initialize tdslite
  //****************************************************     
  Serial.println("Setup Ok, tot es correcte.");
   
  } 

static void row_callback(void * u, const tdsl::tds_colmetadata_token & colmd,
                         const tdsl::tdsl_row & row) {
    SERIAL_PRINTLNF("row: %.4s %d", row [0].as<tdsl::char_view>().data(),
                    row [1].as<tdsl::int32_t>());
  }


void loop() 
  {
  //Llop principal
  int a=0;
  int b=0;
  
  a = random(1,7);
  b = random(1,500);
  Serial.print("Hola a: "); Serial.println(a);
  Serial.print("Hola b: "); Serial.println(b);
  
  auto query{TDSL_PMEMSTR("INSERT INTO NomBD (idComptador, valor) VALUES(@p0, @p1)")};
  SERIAL_PRINTF("Executing query: ");
  SERIAL_PRINTLNF_PROGMEM(query.raw_data());
  //tdsl::sql_parameter_varchar a{"test"};
  tdsl::sql_parameter_int idComptador{a};
  tdsl::sql_parameter_int valor{b};
  tdsl::sql_parameter_binding params []{idComptador, valor};
  auto result =  driver.execute_rpc(query, params, tdsl::rpc_mode::executesql, row_callback);
  Serial.print("Rows affected: "); Serial.println(result);
    
  delay(30000);
  }
12

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.