Swprintf and wchar_t?

What is the proper way to format wchar_t in arduino?
I need something like this : swprintf - C++ Reference
Thanks in advance

What exactly are you trying to format ?

I need to format this line :

MSSQL.executeNonQuery(L"INSERT INTO [dbo].[test]([data]) VALUES('deger1234') ");

it is from this code : GitHub - mustafakemalgilor/arduino-mssql: A TDS 7.0 implementation for Arduino, allowing connection to Microsoft SQL Server and running queries. (using UIPEthernet or Ethernet)

Which part or parts of the SQL query is constant text and which is variables ?
Does any of it consist of anything except standard ASCII characters ?

you can read about sql insert query here : SQL INSERT INTO Statement

will you be able to answer my main question?

I still don't understand why you feel the need to use swprintf()

Let's start with an example of what Arduino can do to format a string holding an SQL query query and go from there

Try this

char query[200];

void setup()
{
  Serial.begin(115200);
  while (!Serial);
  formatQuery("deger1234");
  formatQuery("test data");
  formatQuery("longer test data");  
}

void loop()
{
}

void formatQuery(char * data)
{
  sprintf(query, "MSSQL.executeNonQuery(L \"INSERT INTO [dbo].[test]([data]) VALUES('%s')\");" , data);
  Serial.println(query);
}

executeNonQuery inside of "sqlard.h" needs const wchar_t*
here is the error :

Sorry, but I have no idea what I am looking at which is not helped by you posting pictures of what I assume is Arduino code and an error message from somewhere

Do you see that nice "Copy error messages" button in the iDE ? Please use it and post your full sketch and the error message here using code tags when you do

See How to get the best out of this forum

here is the full code :

// how to compile :
// 1. download "arduino-mssql-master.zip" from https://github.com/mustafakemalgilor/arduino-mssql
// 2. extract, copy, paste "arduino-mssql-master" to C:\Users\[YOUR_USERNAME]\Documents\Arduino\libraries
// 3. delete "sqlard-test.cpp"
// 4. open "sqlard.h" and delete line 3 (#define UIPETHERNET)
// 5. find out your sql server ip, database name, user name, password, server name and then set them in the code

// tested with sql server 2012, 2014, 2016
// more info : https://forum.arduino.cc/t/arduino-daten-an-ms-sql/661791

#include "sqlard.h"

const wchar_t database[] = L"myDatabse";
const wchar_t username[] = L"myUsername";
const wchar_t password[] = L"myPassword";
const wchar_t server[] = L"mySQLserver";

char query[200];

byte Mac[] = {0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED};
IPAddress IP(192, 168, 1, 10);
IPAddress Dns(192, 168, 1, 1);
IPAddress Gateway(192, 168, 1, 1);
IPAddress Subnet(255, 255, 255, 0);
static byte server_IP[] = {1, 1, 1, 1};
EthernetClient client;

SQLard MSSQL(server_IP, 1433, &client);

void formatQuery(char * data)
{
  sprintf(query, "MSSQL.executeNonQuery(L \"INSERT INTO [dbo].[test]([data]) VALUES('%s')\");" , data);
  Serial.println(query);
  MSSQL.executeNonQuery(query);
}

void setup()
{
  Serial.begin(9600);
  while (!Serial);
  Serial.println("serial started");
  delay(1000);

  Ethernet.begin(Mac, IP, Dns, Gateway, Subnet);
  Serial.println("ethernet connected");
  delay(1000);

  if (MSSQL.connect())
  {
    Serial.println("connected to sql server ip");
    delay(1000);

    MSSQL.setCredentials(database, username, password, server);
    MSSQL.login();
    Serial.println("login ok");
    delay(10000);

    formatQuery("deger1234");
    //MSSQL.executeNonQuery(L"INSERT INTO [dbo].[test]([data]) VALUES('deger1234') ");

    delay(1000);
    Serial.println("Done!");
  }
  else
  {
    Serial.println("error! wrong sql server ip?");
  }
}

void loop()
{

}

here is the error :

Arduino: 1.8.15 (Windows 10), Board: "Arduino Uno"

D:\mssql\mssql.ino: In function 'void formatQuery(char*)':

mssql:36:30: error: no matching function for call to 'SQLard::executeNonQuery(char [200])'

MSSQL.executeNonQuery(query);

                          ^

In file included from D:\mssql\mssql.ino:20:0:

C:\Users\ELITE\Documents\Arduino\libraries\arduino-mssql-master/sqlard.h:1156:7: note: candidate: long int SQLard::executeNonQuery(const wchar_t*)

long executeNonQuery(const wchar_t* query) {

   ^~~~~~~~~~~~~~~

C:\Users\ELITE\Documents\Arduino\libraries\arduino-mssql-master/sqlard.h:1156:7: note: no known conversion for argument 1 from 'char [200]' to 'const wchar_t*'

exit status 1

no matching function for call to 'SQLard::executeNonQuery(char [200])'

This report would have more information with
"Show verbose output during compilation"
option enabled in File -> Preferences.

What happens if you call the MSSQL.executeNonQuery(() function with const text as the parameter rather than a variable ?

By the way, look carefully at what is printed when you print the data string. You need to alter the text in the sprintf() function to suit what you really want in the query

it works fine. but that way I can't send any variable. for example I need to read a sensor value and then format that query : MSSQL.executeNonQuery(string+value+string)

If it works with fixed text then I see no reason why it would not work with a string created with sprintf(). Start by changing the sprintf() output so that it is actually the query that you want to run, complete with the data from the variable, rather than what I put in it

sprintf works with char* but MSSQL.executeNonQuery needs wchar_t* which is called wide string (double byte char)

A little research shows that you can convert the char query string to a wchar_t data type like this

 wchar_t * wideQuery = new wchar_t[strlen(query)+1];

See Convert char* to wchar_t* in C | Convert Data Types

It cannot be printed using Serial.print() but try using the converted value in your function call

I can't compile even the example in that page :

void setup()
{
  char * vIn = "FOO";
  wchar_t * vOut = new wchar_t[strlen(vIn) + 1];
  mbstowcs_s(NULL, vOut, strlen(vIn) + 1, vIn, strlen(vIn));
}

void loop()
{

}

here is the error :

Arduino: 1.8.15 (Windows 10), Board: "Arduino Uno"

D:\convert\convert.ino: In function 'void setup()':

convert:5:3: error: 'mbstowcs_s' was not declared in this scope

mbstowcs_s(NULL, vOut, strlen(vIn) + 1, vIn, strlen(vIn));

^~~~~~~~~~

exit status 1

'mbstowcs_s' was not declared in this scope

This report would have more information with
"Show verbose output during compilation"
option enabled in File -> Preferences.

This compiles for me

char query[200];

void setup()
{
  Serial.begin(115200);
  while (!Serial);
  formatQuery("deger1234");
  formatQuery("test data");
  formatQuery("longer test data");
}

void loop()
{
}

void formatQuery(char * data)
{
  sprintf(query, "MSSQL.executeNonQuery(L \"INSERT INTO [dbo].[test]([data]) VALUES('%s')\");" , data);
  Serial.println(query);
  wchar_t * wideQuery = new wchar_t[strlen(query) + 1];
}

Try using the wideQuery variable in your function call but you must adjust the text in the call to sprintf() to produce the correct text of your query first

yes it compiles but lets analyze your code :

formatQuery("deger1234"); // --> calls formatQuery and sends "deger1234" as char* data
sprintf(query, "MSSQL.executeNonQuery(L \"INSERT INTO [dbo].[test]([data]) VALUES('%s')\");" , data); // --> formats the sql query string
Serial.println(query); // --> prints the formatted sql query string in serial monitor
wchar_t * wideQuery = new wchar_t[strlen(query) + 1]; // --> defines a new wchar_t*

so where is the mbstowcs_s() which converts the char* to wchar_t*?
your code creates just an empty wchar_t* without giving it any data!

You are, of course, correct, so that looks like another dead end

this code works but it can insert into the database up to 5 times. maybe it is because of overflow of memory?

// how to compile :
// 1. download "arduino-mssql-master.zip" from https://github.com/mustafakemalgilor/arduino-mssql
// 2. extract, copy, paste "arduino-mssql-master" to C:\Users\[YOUR_USERNAME]\Documents\Arduino\libraries
// 3. delete "sqlard-test.cpp"
// 4. open "sqlard.h" and delete line 3 (#define UIPETHERNET)
// 5. find out your sql server ip, database name, user name, password, server name and then set them in the code
// tested with sql server 2012, 2014, 2016
// more info : https://forum.arduino.cc/t/arduino-daten-an-ms-sql/661791

#include "sqlard.h"

const wchar_t database[] = L"mydatabase";
const wchar_t username[] = L"myusername";
const wchar_t password[] = L"mypassword";
const wchar_t server[] = L"myserver";

byte Mac[] = {0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED};
IPAddress IP(192, 168, 1, 10);
IPAddress Dns(192, 168, 1, 1);
IPAddress Gateway(192, 168, 1, 1);
IPAddress Subnet(255, 255, 255, 0);
static byte server_IP[] = {1, 1, 1, 1};
EthernetClient client;

SQLard MSSQL(server_IP, 1433, &client);

void setup()
{
  Ethernet.begin(Mac, IP, Dns, Gateway, Subnet);
  if (MSSQL.connect())
  {
    MSSQL.setCredentials(database, username, password, server);
    MSSQL.login();           
  }
}

void loop()
{    
  for(int i=0 ; i<100 ; i++)
  {
    delay(10000); 
    MSSQL.executeNonQuery(int2wchar_t(i));      
  }
}

wchar_t* int2wchar_t(int value)
{
  int i, len, len1, len2, len3, index = 0;
  wchar_t str1[]  = L"INSERT INTO [dbo].[myTable] ([data]) VALUES (";
  len1 = sizeof(str1) / sizeof(wchar_t);
  len2 = log10(value) + 1;
  wchar_t str2[len2];
  wchar_t str3[]  = L")";
  len3 = sizeof(str3) / sizeof(wchar_t);
  len = len1 + len2 + len3 - 1;
  wchar_t* str = malloc(len * sizeof(wchar_t));

  for (i = 0 ; i < len1 - 1 ; i++)
    str[i] = str1[i];
  index = i;

  for (i = len2 - 1; i >= 0; --i)
  {
    str2[i] = (value % 10) + '0';
    value /= 10;
  }

  for (i = 0 ; i < len2 ; i++)
    str[index + i] = str2[i];
  index = index + i;

  for (i = 0 ; i < len3 ; i++)
    str[index + i] = str3[i];

  return str;
}

isn't wchar_t something specific to MSFT compiler for Unicode UTF-16LE on Windows?

do you really need wchar_t?