ESP32 MySQL 데이터베이스에 데이터 등록 조회 하는 방법

예전에는 웹개발자 또는 앱개발자들이 많이 다루던 데이터베이스를
이제는 하드웨어 개발자들도 다룰줄 알아야 하는 시대가 왔다...
IoT 시대가 되면서 다양한 상태값을 온라인 상에 저장하고 데이터를 공유하고, 다른 데이터를 조회할 줄 알아야 제품을 개발할 수 있게되었다.
그래서 이번에 다뤄볼 내용은 ESP32 를 이용해서 DB(데이터베이스)에 접근하여 데이터를 등록하거나 조회하는 방법을 다뤄보려 한다.

우선 MySQL 이나 Maria DB를 설치되어 있어야 한다.
이 내용까지 다루려면 너무 길어지니깐, 직접 찾아서 설치하길 바란다.
필자는 MySQL로 진행하겠지만,
Maria DB는 MySQL 을 호환하기 때문에 Maria DB로 설치해서 따라해도 된다.
Maria DB가 설치하기 더 편하다. MySQL은 오라클로 넘어가면서 설치가 좀 복잡하다 느껴졌었다.

일단 connection 을 새롭게 하나 만들어준다.
서버를 만든다 생각하면 된다.
위의 설정 그대로 따라하면 되겠다.

DB연결설정을 마쳤으면 이제 스키마(=Data base)를 새롭게 만들어줘야한다.

표시해놓은 버튼에 마우스 올려보면 영어로 스키마를 새로 만든다는 설명이 나온다.
이 버튼을 눌러서 스키마를 만들어 준다.

스키마 이름은 esp32 로 했다.
따라하시는 분들은 자신이 원하는 이름으로 설정해주면 된다.

스키마가 만들어졌다면,
네비게이터 에서 스키마 >> 생성한 esp32 >> Tables 우클릭 을 해서
테이블 생성을 해준다.

테이블이름은 item 으로 짓고,
빨간색 네모박스를 클릭해서 컬럼을 추가해주고, 이름을 위 처럼 해준다.
그리고 데이터타입과 PK NN UN AI 등 체크해줄 부분은 체크해준다.
PK NN UN AI 등은 데이터 베이스 제약조건이라고 하는데,
테이블 단위에서 데이터의 무결성을 보장하는 규칙이다.
PK 는 primary key 로 테이블에서 특정 row를 고유하게 나타낼 수 있는지 속성을 부여하는 것이고,
NN은 Not null 로, 빈값이 저장될 수 없다는 속성을 부여하는 것이다.
UN 은 Unique key 로 고유키 속성으로 고유 인덱스를 자동 생성하는 것이다.
AI 는 Auto Increment 로 테이블에 데이터를 신규 등록하는 경우 해당 컬럼값이 자동으로 1씩 증가하면서 등록되는 속성이다.
id_ 는 고유 번호라고 생각하면 좋다.
1부터 순차적으로 증가하는 것이 마치 학급에서 출석번호와 같다고 보면 된다.
barcode_ 와 name_ , location_, item_code_ 는 해당 id_ 에 해당하는 값이 된다.
예를 들면, 학생부에 출석번호마다 학생 이름, 집주소, 학번이 적혀있는 것과 같다.
5번 학생의 이름을 조회하거나 학번을 조회한다 생각하면 된다.

위 사진에 빨간색 표시 해둔 버튼을 클릭하면
테이블 내용을 볼 수 있고,
값을 추가할 수 있다.
위의 사진처럼 row 값을 추가해주면 되겠다.
이렇게 별도의 DB 관리 프로그램으로 하면 GUI로 데이터를 추가할 수 있는데,
명령어로 추가하고 싶은 경우 아래 명령어를 Query 에서 실행시키면 된다.

insert into esp32.item(barcode_,name_,location_,item_code_) values('80810007','pencil','A02','E3921');

위의 명령어를 치고 Ctrl + Enter 를 입력하면 해당 명령문이 실행된다.
그리고 DB를 다시 확인해보면 새로운 데이터가 추가되게 된다.

바뀐 DB 데이터를 보기 위해서 새로고침 버튼을 누르거나
SELECT * FROM esp32.item;
명령을 실행시켜주면 된다.


그러면 이제 DB 설정은 다 끝마친 것이다.
DB 명령어를 SQL 언어로 사용할 수 있다.
만약 id_ 가 3인 데이터의 name_ 을 얻어오고 싶다면
select name_ from esp32.item where id_ = 3;
위의 명령을 실행시키면 된다.

실행시키면 name_ 이 Iphone14 로 뜨는 것을 확인할 수 있다.

이제 이 기능을 ESP32 에서 사용해 보도록 하자.


우선 ESP32 에서 사용할 라이브러리를 설치해주어야 한다.

라이브러리 관리에서
mysql 을 검색하고
MySQL_MariaDB_Generic
라이브러리를 설치해주면 된다.


그다음 코드는 아래 코드를 이용해도되고
라이브러리의 basic insert ESP 예제 코드를 이용해도 된다.

#if ! (ESP8266 || ESP32 )
  #error This code is intended to run on the ESP8266/ESP32 platform! Please check your Tools->Board setting
#endif

char ssid[] = "";             // your network SSID (name)
char pass[] = "";         // your network password

char user[]         = "";          // MySQL user login username
char password[]     = "";          // MySQL user login password

#define MYSQL_DEBUG_PORT      Serial

// Debug Level from 0 to 4
#define _MYSQL_LOGLEVEL_      1

#include <MySQL_Generic.h>

#define USING_HOST_NAME     false

#if USING_HOST_NAME
  // Optional using hostname, and Ethernet built-in DNS lookup
  char SQL_server[] = ""; // change to your server's hostname/URL
#else
  IPAddress SQL_server(192, 168, 1, 100);
#endif

uint16_t server_port = 3306;    //3306;


char default_database[] = "esp32";           //"test_arduino";
char default_table[]    = "item";          //"test_arduino";

MySQL_Connection conn((Client *)&client);
MySQL_Query *query_mem;
MySQL_Query sql_query = MySQL_Query(&conn);

void setup()
{
  Serial.begin(115200);

  MYSQL_DISPLAY1("\nStarting Basic_Insert_ESP on", ARDUINO_BOARD);
  MYSQL_DISPLAY(MYSQL_MARIADB_GENERIC_VERSION);

  // Begin WiFi section
  MYSQL_DISPLAY1("Connecting to", ssid);
  
  WiFi.begin(ssid, pass);
  
  while (WiFi.status() != WL_CONNECTED) 
  {
    delay(500);
    MYSQL_DISPLAY0(".");
  }

  // print out info about the connection:
  MYSQL_DISPLAY1("Connected to network. My IP address is:", WiFi.localIP());

  MYSQL_DISPLAY3("Connecting to SQL Server @", SQL_server, ", Port =", server_port);
  MYSQL_DISPLAY5("User =", user, ", PW =", password, ", DB =", default_database);
}


String query = "";
String INSERT_SQL = "";

void loop()
{
  INSERT_SQL = "insert into esp32.item(barcode_,name_,location_,item_code_) values('80810007','pencil','A02','E3921')";
  
  if (conn.connectNonBlocking(SQL_server, server_port, user, password) != RESULT_FAIL)
  {
    delay(500);
    runInsert();
    conn.close();                     // close the connection
  } 
  else 
  {
    MYSQL_DISPLAY("\nConnect failed. Trying again on next iteration.");
  }

  delay(5000);

  query = "select name_ from esp32.item where id_ = 3";

  if (conn.connectNonBlocking(SQL_server, server_port, user, password) != RESULT_FAIL)
  {
    delay(500);
    
    runQuery();
    conn.close();                     // close the connection
  } 
  else 
  {
    MYSQL_DISPLAY("\nConnect failed. Trying again on next iteration.");
  }
  
  delay(1000);
  while(1)
  {
    delay(20);
  }



}




void runQuery()
{
  MYSQL_DISPLAY("====================================================");
  MYSQL_DISPLAY("> Running SELECT with dynamically supplied parameter");
  
  // Supply the parameter for the query
  // Here we use the QUERY_POP as the format string and query as the
  // destination. This uses twice the memory so another option would be
  // to allocate one buffer for all formatted queries or allocate the
  // memory as needed (just make sure you allocate enough memory and
  // free it when you're done!).
  
  MYSQL_DISPLAY(query);
  
  // Initiate the query class instance
  MySQL_Query query_mem = MySQL_Query(&conn);
  
  // Execute the query
  // KH, check if valid before fetching
  // 쿼리 실행
  if ( !query_mem.execute(query.c_str()) )
  {
    MYSQL_DISPLAY("Querying error");
    return;
  }
  
  // Fetch the columns and print them
  column_names *cols = query_mem.get_columns();
  // 쿼리 조건에 해당하는 데이터 열 가져옴
  
  for (int f = 0; f < cols->num_fields; f++) 
  {
    Serial.print("f :");
    Serial.println(f);
    
    MYSQL_DISPLAY0(cols->fields[f]->name);
    
    if (f < cols->num_fields - 1) 
    {
      MYSQL_DISPLAY0(",");
    }
  }


  MYSQL_DISPLAY();
  String text = "";
  
  // Read the rows and print them
  row_values *row = NULL;

  //row 가 NULL 이 될때까지 반복
  do 
  {
    row = query_mem.get_next_row();
    
    if (row != NULL) 
    {
      for (int f = 0; f < cols->num_fields; f++) 
      {
        MYSQL_DISPLAY0(row->values[f]);
        text += row->values[f];
        if (f < cols->num_fields - 1) 
        {
          MYSQL_DISPLAY0(",");
          text += ",";
        }
      }
      Serial.println();
      Serial.print("SQL read : ");
      Serial.println(text);
      
      MYSQL_DISPLAY();
    }
  } while (row != NULL);
}







void runInsert()
{
  // Initiate the query class instance
  MySQL_Query query_mem = MySQL_Query(&conn);

  if (conn.connected())
  {
    MYSQL_DISPLAY(INSERT_SQL);
    
    // Execute the query
    // KH, check if valid before fetching
    if ( !query_mem.execute(INSERT_SQL.c_str()) )
    {
      MYSQL_DISPLAY("Insert error");
    }
    else
    {
      MYSQL_DISPLAY("Data Inserted.");
    }
  }
  else
  {
    MYSQL_DISPLAY("Disconnected from Server. Can't insert.");
  }
}

.

이 포스팅을 그대로 따라했다면 위의 아두이노 코드에서
wifi 접속 정보와 DB 사용자 계정 정보만 입력해주고 DB가 저장되있는 컴퓨터나 서버 주소를 입력해주면된다.

필자의 경우 DB가 설치된 컴퓨터의 내부 ip 주소가 192.168.1.100 이었기 때문에 위처럼 추가한 것이다.
세가지만 수정해주었다면 이제 코드를 업로드 해주면 된다.

기존에 DB 데이터가 위와 같았다면,
코드가 업로드 되고 기다리면 아래처럼 변하게 된다.

id_ 가 7이 아니라 8로 추가된 이유는 위에서 이미 한번 7번째 데이터를 만들고 삭제했기 때문이다.
데이터가 삭제되더라도 이미 자동 카운트는 하나 증가했기 때문에 증가한 값으로 추가된다.
아두이노 코드에 넣어놓았던 SQL 명령으로 데이터가 하나 추가되었음을 확인할 수 있다.
아두이노 시리얼 모니터를 확인하면 성공했다는 메세지가 떠있는 것을 볼 수 있다.

이 메세지가 뜨지 않으면 성공하지 못한것으로
다시 제대로 해볼 필요가 있다.
아마 보통 DB 계정 정보가 잘못된 경우일 것이다.