Insert Using SQL

Introduction

Application programs can execute INSERT statement through connectors to insert rows. The TDengine CLI can also be used to manually insert data.

Insert Single Row

The below SQL statement is used to insert one row into table “d1001”.

  1. INSERT INTO d1001 VALUES (1538548685000, 10.3, 219, 0.31);

Insert Multiple Rows

Multiple rows can be inserted in a single SQL statement. The example below inserts 2 rows into table “d1001”.

  1. INSERT INTO d1001 VALUES (1538548684000, 10.2, 220, 0.23) (1538548696650, 10.3, 218, 0.25);

Insert into Multiple Tables

Data can be inserted into multiple tables in the same SQL statement. The example below inserts 2 rows into table “d1001” and 1 row into table “d1002”.

  1. INSERT INTO d1001 VALUES (1538548685000, 10.3, 219, 0.31) (1538548695000, 12.6, 218, 0.33) d1002 VALUES (1538548696800, 12.3, 221, 0.31);

For more details about INSERT please refer to INSERT.

Insert Using SQL - 图1info
  • Inserting in batches can improve performance. Normally, the higher the batch size, the better the performance. Please note that a single row can’t exceed 48K bytes and each SQL statement can’t exceed 1MB.
  • Inserting with multiple threads can also improve performance. However, depending on the system resources on the application side and the server side, when the number of inserting threads grows beyond a specific point the performance may drop instead of improving. The proper number of threads needs to be tested in a specific environment to find the best number.
Insert Using SQL - 图2warning
  • If the timestamp for the row to be inserted already exists in the table, the behavior depends on the value of parameter UPDATE. If it’s set to 0 (the default value), the row will be discarded. If it’s set to 1, the new values will override the old values for the same row.
  • The timestamp to be inserted must be newer than the timestamp of subtracting current time by the parameter KEEP. If KEEP is set to 3650 days, then the data older than 3650 days ago can’t be inserted. The timestamp to be inserted can’t be newer than the timestamp of current time plus parameter DAYS. If DAYS is set to 2, the data newer than 2 days later can’t be inserted.

Examples

Insert Using SQL

  • Java
  • Python
  • Go
  • Rust
  • Node.js
  • C#
  • C
  1. package com.taos.example;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.Arrays;
  8. import java.util.List;
  11. public class RestInsertExample {
  12.     private static Connection getConnection() throws SQLException {
  13.         String jdbcUrl = "jdbc:TAOS-RS://localhost:6041?user=root&password=taosdata";
  14.         return DriverManager.getConnection(jdbcUrl);
  15.     }
  17.     private static List<String> getRawData() {
  18.         return Arrays.asList(
  19.                 "d1001,2018-10-03 14:38:05.000,10.30000,219,0.31000,California.SanFrancisco,2",
  20.                 "d1001,2018-10-03 14:38:15.000,12.60000,218,0.33000,California.SanFrancisco,2",
  21.                 "d1001,2018-10-03 14:38:16.800,12.30000,221,0.31000,California.SanFrancisco,2",
  22.                 "d1002,2018-10-03 14:38:16.650,10.30000,218,0.25000,California.SanFrancisco,3",
  23.                 "d1003,2018-10-03 14:38:05.500,11.80000,221,0.28000,California.LosAngeles,2",
  24.                 "d1003,2018-10-03 14:38:16.600,13.40000,223,0.29000,California.LosAngeles,2",
  25.                 "d1004,2018-10-03 14:38:05.000,10.80000,223,0.29000,California.LosAngeles,3",
  26.                 "d1004,2018-10-03 14:38:06.500,11.50000,221,0.35000,California.LosAngeles,3"
  27.         );
  28.     }
  31.     /**
  32.      * The generated SQL is:
  33.      * INSERT INTO power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:05.000',10.30000,219,0.31000)
  34.      * power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:15.000',12.60000,218,0.33000)
  35.      * power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:16.800',12.30000,221,0.31000)
  36.      * power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES('2018-10-03 14:38:16.650',10.30000,218,0.25000)
  37.      * power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES('2018-10-03 14:38:05.500',11.80000,221,0.28000)
  38.      * power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES('2018-10-03 14:38:16.600',13.40000,223,0.29000)
  39.      * power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES('2018-10-03 14:38:05.000',10.80000,223,0.29000)
  40.      * power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES('2018-10-03 14:38:06.500',11.50000,221,0.35000)
  41.      */
  42.     private static String getSQL() {
  43.         StringBuilder sb = new StringBuilder("INSERT INTO ");
  44.         for (String line : getRawData()) {
  45.             String[] ps = line.split(",");
  46.             sb.append("power." + ps[0]).append(" USING power.meters TAGS(")
  47.                     .append(ps[5]).append(", ") // tag: location
  48.                     .append(ps[6]) // tag: groupId
  49.                     .append(") VALUES(")
  50.                     .append('\'').append(ps[1]).append('\'').append(",") // ts
  51.                     .append(ps[2]).append(",") // current
  52.                     .append(ps[3]).append(",") // voltage
  53.                     .append(ps[4]).append(") "); // phase
  54.         }
  55.         return sb.toString();
  56.     }
  58.     public static void insertData() throws SQLException {
  59.         try (Connection conn = getConnection()) {
  60.             try (Statement stmt = conn.createStatement()) {
  61.                 stmt.execute("CREATE DATABASE power KEEP 3650");
  62.                 stmt.execute("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) " +
  63.                         "TAGS (location BINARY(64), groupId INT)");
  64.                 String sql = getSQL();
  65.                 int rowCount = stmt.executeUpdate(sql);
  66.                 System.out.println("rowCount=" + rowCount); // rowCount=8
  67.             }
  68.         }
  69.     }
  71.     public static void main(String[] args) throws SQLException {
  72.         insertData();
  73.     }
  74. }

view source code

  1. import taos
  3. lines = ["d1001,2018-10-03 14:38:05.000,10.30000,219,0.31000,California.SanFrancisco,2",
  4.          "d1004,2018-10-03 14:38:05.000,10.80000,223,0.29000,California.LosAngeles,3",
  5.          "d1003,2018-10-03 14:38:05.500,11.80000,221,0.28000,California.LosAngeles,2",
  6.          "d1004,2018-10-03 14:38:06.500,11.50000,221,0.35000,California.LosAngeles,3",
  7.          "d1002,2018-10-03 14:38:16.650,10.30000,218,0.25000,California.SanFrancisco,3",
  8.          "d1001,2018-10-03 14:38:15.000,12.60000,218,0.33000,California.SanFrancisco,2",
  9.          "d1001,2018-10-03 14:38:16.800,12.30000,221,0.31000,California.SanFrancisco,2",
  10.          "d1003,2018-10-03 14:38:16.600,13.40000,223,0.29000,California.LosAngeles,2"]
  13. def get_connection() -> taos.TaosConnection:
  14.     """
  15.     create connection use firstEp in taos.cfg and use default user and password.
  16.     """
  17.     return taos.connect()
  20. def create_stable(conn: taos.TaosConnection):
  21.     conn.execute("CREATE DATABASE power")
  22.     conn.execute("USE power")
  23.     conn.execute("CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) "
  24.                  "TAGS (location BINARY(64), groupId INT)")
  27. # The generated SQL is:
  28. # INSERT INTO d1001 USING meters TAGS(California.SanFrancisco, 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)
  29. #             d1002 USING meters TAGS(California.SanFrancisco, 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
  30. #             d1003 USING meters TAGS(California.LosAngeles, 2) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)
  31. #             d1004 USING meters TAGS(California.LosAngeles, 3) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)
  33. def get_sql():
  34.     global lines
  35.     lines = map(lambda line: line.split(','), lines)  # [['d1001', ...]...]
  36.     lines = sorted(lines, key=lambda ls: ls[0])  # sort by table name
  37.     sql = "INSERT INTO "
  38.     tb_name = None
  39.     for ps in lines:
  40.         tmp_tb_name = ps[0]
  41.         if tb_name != tmp_tb_name:
  42.             tb_name = tmp_tb_name
  43.             sql += f"{tb_name} USING meters TAGS({ps[5]}, {ps[6]}) VALUES "
  44.         sql += f"('{ps[1]}', {ps[2]}, {ps[3]}, {ps[4]}) "
  45.     return sql
  48. def insert_data(conn: taos.TaosConnection):
  49.     sql = get_sql()
  50.     affected_rows = conn.execute(sql)
  51.     print("affected_rows", affected_rows)  # 8
  54. if __name__ == '__main__':
  55.     connection = get_connection()
  56.     try:
  57.         create_stable(connection)
  58.         insert_data(connection)
  59.     finally:
  60.         connection.close()

view source code

  1. package main
  3. import (
  4.     "database/sql"
  5.     "fmt"
  7.     _ "github.com/taosdata/driver-go/v2/taosRestful"
  8. )
  10. func createStable(taos *sql.DB) {
  11.     _, err := taos.Exec("CREATE DATABASE power")
  12.     if err != nil {
  13.         fmt.Println("failed to create database, err:", err)
  14.     }
  15.     _, err = taos.Exec("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)")
  16.     if err != nil {
  17.         fmt.Println("failed to create stable, err:", err)
  18.     }
  19. }
  21. func insertData(taos *sql.DB) {
  22.     sql := `INSERT INTO power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)
  23.     power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
  24.     power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)
  25.     power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)`
  26.     result, err := taos.Exec(sql)
  27.     if err != nil {
  28.         fmt.Println("failed to insert, err:", err)
  29.         return
  30.     }
  31.     rowsAffected, err := result.RowsAffected()
  32.     if err != nil {
  33.         fmt.Println("failed to get affected rows, err:", err)
  34.         return
  35.     }
  36.     fmt.Println("RowsAffected", rowsAffected)
  37. }
  39. func main() {
  40.     var taosDSN = "root:taosdata@http(localhost:6041)/"
  41.     taos, err := sql.Open("taosRestful", taosDSN)
  42.     if err != nil {
  43.         fmt.Println("failed to connect TDengine, err:", err)
  44.         return
  45.     }
  46.     defer taos.Close()
  47.     createStable(taos)
  48.     insertData(taos)
  49. }

view source code

  1. use libtaos::*;
  3. #[tokio::main]
  4. async fn main() -> Result<(), Error> {
  5.     let taos = TaosCfg::default().connect().expect("fail to connect");
  6.     taos.create_database("power").await?;
  7.     taos.exec("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)").await?;
  8.     let sql = "INSERT INTO power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)
  9.     power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
  10.     power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)
  11.     power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)";
  12.     let result = taos.query(sql).await?;
  13.     println!("{:?}", result);
  14.     Ok(())
  15. }
  17. // output:
  18. // TaosQueryData { column_meta: [ColumnMeta { name: "affected_rows", type_: Int, bytes: 4 }], rows: [[Int(8)]] }

view source code

  1. const taos = require("td2.0-connector");
  3. const conn = taos.connect({
  4.   host: "localhost",
  5. });
  7. const cursor = conn.cursor();
  8. try {
  9.   cursor.execute("CREATE DATABASE power");
  10.   cursor.execute("USE power");
  11.   cursor.execute(
  12.     "CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)"
  13.   );
  14.   var sql = `INSERT INTO power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)
  15. power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
  16. power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)
  17. power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)`;
  18.   cursor.execute(sql);
  19. } finally {
  20.   cursor.close();
  21.   conn.close();
  22. }
  24. // run with: node insert_example.js
  25. // output:
  26. // Successfully connected to TDengine
  27. // Query OK, 0 row(s) affected (0.00509570s)
  28. // Query OK, 0 row(s) affected (0.00130880s)
  29. // Query OK, 0 row(s) affected (0.00467900s)
  30. // Query OK, 8 row(s) affected (0.04043550s)
  31. // Connection is closed

view source code

  1. using TDengineDriver;
  4. namespace TDengineExample
  5. {
  6.     internal class SQLInsertExample
  7.     {
  9.         static void Main()
  10.         {
  11.             IntPtr conn = GetConnection();
  12.             IntPtr res = TDengine.Query(conn, "CREATE DATABASE power");
  13.             CheckRes(conn, res, "failed to create database");
  14.             res = TDengine.Query(conn, "USE power");
  15.             CheckRes(conn, res, "failed to change database");
  16.             res = TDengine.Query(conn, "CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
  17.             CheckRes(conn, res, "failed to create stable");
  18.             var sql = "INSERT INTO d1001 USING meters TAGS(California.SanFrancisco, 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000) " +
  19.                         "d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000) " +
  20.                         "d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000)('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000) " +
  21.                         "d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000)('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)";
  22.             res = TDengine.Query(conn, sql);
  23.             CheckRes(conn, res, "failed to insert data");
  24.             int affectedRows = TDengine.AffectRows(res);
  25.             Console.WriteLine("affectedRows " + affectedRows);
  26.             ExitProgram(conn, 0);
  27.         }
  29.         static IntPtr GetConnection()
  30.         {
  31.             string host = "localhost";
  32.             short port = 6030;
  33.             string username = "root";
  34.             string password = "taosdata";
  35.             string dbname = "";
  36.             var conn = TDengine.Connect(host, username, password, dbname, port);
  37.             if (conn == IntPtr.Zero)
  38.             {
  39.                 Console.WriteLine("Connect to TDengine failed");
  40.                 Environment.Exit(0);
  41.             }
  42.             else
  43.             {
  44.                 Console.WriteLine("Connect to TDengine success");
  45.             }
  46.             return conn;
  47.         }
  49.         static void CheckRes(IntPtr conn, IntPtr res, String errorMsg)
  50.         {
  51.             if (TDengine.ErrorNo(res) != 0)
  52.             {
  53.                 Console.Write(errorMsg + " since: " + TDengine.Error(res));
  54.                 ExitProgram(conn, 1);
  55.             }
  56.         }
  58.         static void ExitProgram(IntPtr conn, int exitCode)
  59.         {
  60.             TDengine.Close(conn);
  61.             TDengine.Cleanup();
  62.             Environment.Exit(exitCode);
  63.         }
  64.     }
  65. }
  67. // output:
  68. // Connect to TDengine success
  69. // affectedRows 8

view source code

  1. // compile with
  2. // gcc -o insert_example insert_example.c -ltaos
  3. #include <stdio.h>
  4. #include <stdlib.h>
  5. #include "taos.h"
  8. /**
  9.  * @brief execute sql and print affected rows.
  10.  * 
  11.  * @param taos 
  12.  * @param sql 
  13.  */
  14. void executeSQL(TAOS *taos, const char *sql) {
  15.   TAOS_RES *res = taos_query(taos, sql);
  16.   int       code = taos_errno(res);
  17.   if (code != 0) {
  18.     printf("Error code: %d; Message: %s\n", code, taos_errstr(res));
  19.     taos_free_result(res);
  20.     taos_close(taos);
  21.     exit(EXIT_FAILURE);
  22.   }
  23.   int affectedRows = taos_affected_rows(res);
  24.   printf("affected rows %d\n", affectedRows);
  25.   taos_free_result(res);
  26. }
  30. int main() {
  31.    TAOS *taos = taos_connect("localhost", "root", "taosdata", NULL, 6030);
  32.   if (taos == NULL) {
  33.     printf("failed to connect to server\n");
  34.     exit(EXIT_FAILURE);
  35.   }
  36.   executeSQL(taos, "CREATE DATABASE power");
  37.   executeSQL(taos, "USE power");
  38.   executeSQL(taos, "CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
  39.   executeSQL(taos, "INSERT INTO d1001 USING meters TAGS(California.SanFrancisco, 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('2018-10-03 14:38:16.800', 12.30000, 221, 0.31000)"
  40.                 "d1002 USING meters TAGS(California.SanFrancisco, 3) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)"
  41.                 "d1003 USING meters TAGS(California.LosAngeles, 2) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000) ('2018-10-03 14:38:16.600', 13.40000, 223, 0.29000)"
  42.                 "d1004 USING meters TAGS(California.LosAngeles, 3) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ('2018-10-03 14:38:06.500', 11.50000, 221, 0.35000)");
  43.   taos_close(taos);
  44.   taos_cleanup();
  45. }
  47. // output:
  48. // affected rows 0
  49. // affected rows 0
  50. // affected rows 0
  51. // affected rows 8

view source code

Insert Using SQL - 图3note
  1. With either native connection or REST connection, the above samples can work well.
  2. Please note that use db can’t be used with a REST connection because REST connections are stateless, so in the samples dbName.tbName is used to specify the table name.

Insert with Parameter Binding

TDengine also provides API support for parameter binding. Similar to MySQL, only ? can be used in these APIs to represent the parameters to bind. From version 2.1.1.0 and 2.1.2.0, parameter binding support for inserting data has improved significantly to improve the insert performance by avoiding the cost of parsing SQL statements.

Parameter binding is available only with native connection.

  • Java
  • Python
  • Go
  • Rust
  • Node.js
  • C#
  • C
  1. package com.taos.example;
  3. import com.taosdata.jdbc.TSDBPreparedStatement;
  5. import java.sql.Connection;
  6. import java.sql.DriverManager;
  7. import java.sql.SQLException;
  8. import java.sql.Statement;
  9. import java.time.LocalDateTime;
  10. import java.time.ZoneOffset;
  11. import java.time.format.DateTimeFormatter;
  12. import java.util.ArrayList;
  13. import java.util.Arrays;
  14. import java.util.List;
  16. public class StmtInsertExample {
  17.     private static ArrayList<Long> tsToLongArray(String ts) {
  18.         ArrayList<Long> result = new ArrayList<>();
  19.         DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
  20.         LocalDateTime localDateTime = LocalDateTime.parse(ts, formatter);
  21.         result.add(localDateTime.toInstant(ZoneOffset.of("+8")).toEpochMilli());
  22.         return result;
  23.     }
  25.     private static <T> ArrayList<T> toArray(T v) {
  26.         ArrayList<T> result = new ArrayList<>();
  27.         result.add(v);
  28.         return result;
  29.     }
  31.     private static List<String> getRawData() {
  32.         return Arrays.asList(
  33.                 "d1001,2018-10-03 14:38:05.000,10.30000,219,0.31000,California.SanFrancisco,2",
  34.                 "d1001,2018-10-03 14:38:15.000,12.60000,218,0.33000,California.SanFrancisco,2",
  35.                 "d1001,2018-10-03 14:38:16.800,12.30000,221,0.31000,California.SanFrancisco,2",
  36.                 "d1002,2018-10-03 14:38:16.650,10.30000,218,0.25000,California.SanFrancisco,3",
  37.                 "d1003,2018-10-03 14:38:05.500,11.80000,221,0.28000,California.LosAngeles,2",
  38.                 "d1003,2018-10-03 14:38:16.600,13.40000,223,0.29000,California.LosAngeles,2",
  39.                 "d1004,2018-10-03 14:38:05.000,10.80000,223,0.29000,California.LosAngeles,3",
  40.                 "d1004,2018-10-03 14:38:06.500,11.50000,221,0.35000,California.LosAngeles,3"
  41.         );
  42.     }
  44.     private static Connection getConnection() throws SQLException {
  45.         String jdbcUrl = "jdbc:TAOS://localhost:6030?user=root&password=taosdata";
  46.         return DriverManager.getConnection(jdbcUrl);
  47.     }
  49.     private static void createTable(Connection conn) throws SQLException {
  50.         try (Statement stmt = conn.createStatement()) {
  51.             stmt.execute("CREATE DATABASE power KEEP 3650");
  52.             stmt.executeUpdate("USE power");
  53.             stmt.execute("CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) " +
  54.                     "TAGS (location BINARY(64), groupId INT)");
  55.         }
  56.     }
  58.     private static void insertData() throws SQLException {
  59.         try (Connection conn = getConnection()) {
  60.             createTable(conn);
  61.             String psql = "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)";
  62.             try (TSDBPreparedStatement pst = (TSDBPreparedStatement) conn.prepareStatement(psql)) {
  63.                 for (String line : getRawData()) {
  64.                     String[] ps = line.split(",");
  65.                     // bind table name and tags
  66.                     pst.setTableName(ps[0]);
  67.                     pst.setTagString(0, ps[5]);
  68.                     pst.setTagInt(1, Integer.valueOf(ps[6]));
  69.                     // bind values
  70.                     pst.setTimestamp(0, tsToLongArray(ps[1])); //ps[1] looks like: 2018-10-03 14:38:05.000
  71.                     pst.setFloat(1, toArray(Float.valueOf(ps[2])));
  72.                     pst.setInt(2, toArray(Integer.valueOf(ps[3])));
  73.                     pst.setFloat(3, toArray(Float.valueOf(ps[4])));
  74.                     pst.columnDataAddBatch();
  75.                 }
  76.                 pst.columnDataExecuteBatch();
  77.             }
  78.         }
  79.     }
  81.     public static void main(String[] args) throws SQLException {
  82.         insertData();
  83.     }
  84. }

view source code

Single

  1. import taos
  2. from datetime import datetime
  4. # note: lines have already been sorted by table name
  5. lines = [('d1001', '2018-10-03 14:38:05.000', 10.30000, 219, 0.31000, 'California.SanFrancisco', 2),
  6.          ('d1001', '2018-10-03 14:38:15.000', 12.60000, 218, 0.33000, 'California.SanFrancisco', 2),
  7.          ('d1001', '2018-10-03 14:38:16.800', 12.30000, 221, 0.31000, 'California.SanFrancisco', 2),
  8.          ('d1002', '2018-10-03 14:38:16.650', 10.30000, 218, 0.25000, 'California.SanFrancisco', 3),
  9.          ('d1003', '2018-10-03 14:38:05.500', 11.80000, 221, 0.28000, 'California.LosAngeles', 2),
  10.          ('d1003', '2018-10-03 14:38:16.600', 13.40000, 223, 0.29000, 'California.LosAngeles', 2),
  11.          ('d1004', '2018-10-03 14:38:05.000', 10.80000, 223, 0.29000, 'California.LosAngeles', 3),
  12.          ('d1004', '2018-10-03 14:38:06.500', 11.50000, 221, 0.35000, 'California.LosAngeles', 3)]
  15. def get_ts(ts: str):
  16.     dt = datetime.strptime(ts, '%Y-%m-%d %H:%M:%S.%f')
  17.     return int(dt.timestamp() * 1000)
  20. def create_stable():
  21.     conn = taos.connect()
  22.     try:
  23.         conn.execute("CREATE DATABASE power")
  24.         conn.execute("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) "
  25.                      "TAGS (location BINARY(64), groupId INT)")
  26.     finally:
  27.         conn.close()
  30. def bind_row_by_row(stmt: taos.TaosStmt):
  31.     tb_name = None
  32.     for row in lines:
  33.         if tb_name != row[0]:
  34.             tb_name = row[0]
  35.             tags: taos.TaosBind = taos.new_bind_params(2)  # 2 is count of tags
  36.             tags[0].binary(row[5])  # location
  37.             tags[1].int(row[6])  # groupId
  38.             stmt.set_tbname_tags(tb_name, tags)
  39.         values: taos.TaosBind = taos.new_bind_params(4)  # 4 is count of columns
  40.         values[0].timestamp(get_ts(row[1]))
  41.         values[1].float(row[2])
  42.         values[2].int(row[3])
  43.         values[3].float(row[4])
  44.         stmt.bind_param(values)
  47. def insert_data():
  48.     conn = taos.connect(database="power")
  49.     try:
  50.         stmt = conn.statement("INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)")
  51.         bind_row_by_row(stmt)
  52.         stmt.execute()
  53.         stmt.close()
  54.     finally:
  55.         conn.close()
  58. if __name__ == '__main__':
  59.     create_stable()
  60.     insert_data()

view source code

Multiple

  1. table_tags = {
  2.     "d1001": ('California.SanFrancisco', 2),
  3.     "d1002": ('California.SanFrancisco', 3),
  4.     "d1003": ('California.LosAngeles', 2),
  5.     "d1004": ('California.LosAngeles', 3)
  6. }
  8. table_values = {
  9.     "d1001": [
  10.         ['2018-10-03 14:38:05.000', '2018-10-03 14:38:15.000', '2018-10-03 14:38:16.800'],
  11.         [10.3, 12.6, 12.3],
  12.         [219, 218, 221],
  13.         [0.31, 0.33, 0.32]
  14.     ],
  15.     "d1002": [
  16.         ['2018-10-03 14:38:16.650'], [10.3], [218], [0.25]
  17.     ],
  18.     "d1003": [
  19.         ['2018-10-03 14:38:05.500', '2018-10-03 14:38:16.600'],
  20.         [11.8, 13.4],
  21.         [221, 223],
  22.         [0.28, 0.29]
  23.     ],
  24.     "d1004": [
  25.         ['2018-10-03 14:38:05.500', '2018-10-03 14:38:06.500'],
  26.         [10.8, 11.5],
  27.         [223, 221],
  28.         [0.29, 0.35]
  29.     ]
  30. }
  33. def bind_multi_rows(stmt: taos.TaosStmt):
  34.     """
  35.     batch bind example
  36.     """
  37.     for tb_name in table_values.keys():
  38.         tags = table_tags[tb_name]
  39.         tag_params = taos.new_bind_params(2)
  40.         tag_params[0].binary(tags[0])
  41.         tag_params[1].int(tags[1])
  42.         stmt.set_tbname_tags(tb_name, tag_params)
  44.         values = table_values[tb_name]
  45.         value_params = taos.new_multi_binds(4)
  46.         value_params[0].timestamp([get_ts(t) for t in values[0]])
  47.         value_params[1].float(values[1])
  48.         value_params[2].int(values[2])
  49.         value_params[3].float(values[3])
  50.         stmt.bind_param_batch(value_params)
  53. def insert_data():
  54.     conn = taos.connect(database="power")
  55.     try:
  56.         stmt = conn.statement("INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)")
  57.         bind_multi_rows(stmt)
  58.         stmt.execute()
  59.         stmt.close()
  60.     finally:
  61.         conn.close()

view source code

Insert Using SQL - 图4info

Multiple row binding is better in performance than single row binding, but it can only be used with INSERT statement while single row binding can be used for other SQL statements besides INSERT.

  1. package main
  3. import (
  4.     "fmt"
  5.     "time"
  7.     "github.com/taosdata/driver-go/v2/af"
  8.     "github.com/taosdata/driver-go/v2/af/param"
  9.     "github.com/taosdata/driver-go/v2/common"
  10. )
  12. func checkErr(err error, prompt string) {
  13.     if err != nil {
  14.         fmt.Printf("%s\n", prompt)
  15.         panic(err)
  16.     }
  17. }
  19. func prepareStable(conn *af.Connector) {
  20.     _, err := conn.Exec("CREATE DATABASE power")
  21.     checkErr(err, "failed to create database")
  22.     _, err = conn.Exec("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)")
  23.     checkErr(err, "failed to create stable")
  24.     _, err = conn.Exec("USE power")
  25.     checkErr(err, "failed to change database")
  26. }
  28. func main() {
  29.     conn, err := af.Open("localhost", "root", "taosdata", "", 6030)
  30.     checkErr(err, "fail to connect")
  31.     defer conn.Close()
  32.     prepareStable(conn)
  33.     // create stmt
  34.     stmt := conn.InsertStmt()
  35.     defer stmt.Close()
  36.     err = stmt.Prepare("INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)")
  37.     checkErr(err, "failed to create prepare statement")
  39.     // bind table name and tags
  40.     tagParams := param.NewParam(2).AddBinary([]byte("California.SanFrancisco")).AddInt(2)
  41.     err = stmt.SetTableNameWithTags("d1001", tagParams)
  42.     checkErr(err, "failed to execute SetTableNameWithTags")
  44.     // specify ColumnType
  45.     var bindType *param.ColumnType = param.NewColumnType(4).AddTimestamp().AddFloat().AddInt().AddFloat()
  47.     // bind values. note: can only bind one row each time.
  48.     valueParams := []*param.Param{
  49.         param.NewParam(1).AddTimestamp(time.Unix(1648432611, 249300000), common.PrecisionMilliSecond),
  50.         param.NewParam(1).AddFloat(10.3),
  51.         param.NewParam(1).AddInt(219),
  52.         param.NewParam(1).AddFloat(0.31),
  53.     }
  54.     err = stmt.BindParam(valueParams, bindType)
  55.     checkErr(err, "BindParam error")
  56.     err = stmt.AddBatch()
  57.     checkErr(err, "AddBatch error")
  59.     // bind one more row
  60.     valueParams = []*param.Param{
  61.         param.NewParam(1).AddTimestamp(time.Unix(1648432611, 749300000), common.PrecisionMilliSecond),
  62.         param.NewParam(1).AddFloat(12.6),
  63.         param.NewParam(1).AddInt(218),
  64.         param.NewParam(1).AddFloat(0.33),
  65.     }
  66.     err = stmt.BindParam(valueParams, bindType)
  67.     checkErr(err, "BindParam error")
  68.     err = stmt.AddBatch()
  69.     checkErr(err, "AddBatch error")
  70.     // execute
  71.     err = stmt.Execute()
  72.     checkErr(err, "Execute batch error")
  73. }

view source code

Insert Using SQL - 图5tip

github.com/taosdata/driver-go/v2/wrapper module in driver-go is the wrapper for C API, it can be used to insert data with parameter binding.

  1. use bstr::BString;
  2. use libtaos::*;
  4. #[tokio::main]
  5. async fn main() -> Result<(), Error> {
  6.     let taos = TaosCfg::default().connect().expect("fail to connect");
  7.     taos.create_database("power").await?;
  8.     taos.use_database("power").await?;
  9.     taos.exec("CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)").await?;
  10.     let mut stmt = taos.stmt("INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)")?;
  11.     // bind table name and tags
  12.     stmt.set_tbname_tags(
  13.         "d1001",
  14.         [
  15.             Field::Binary(BString::from("California.SanFrancisco")),
  16.             Field::Int(2),
  17.         ],
  18.     )?;
  19.     // bind values.
  20.     let values = vec![
  21.         Field::Timestamp(Timestamp::new(1648432611249, TimestampPrecision::Milli)),
  22.         Field::Float(10.3),
  23.         Field::Int(219),
  24.         Field::Float(0.31),
  25.     ];
  26.     stmt.bind(&values)?;
  27.     // bind one more row
  28.     let values2 = vec![
  29.         Field::Timestamp(Timestamp::new(1648432611749, TimestampPrecision::Milli)),
  30.         Field::Float(12.6),
  31.         Field::Int(218),
  32.         Field::Float(0.33),
  33.     ];
  34.     stmt.bind(&values2)?;
  35.     // execute
  36.     stmt.execute()?;
  37.     Ok(())
  38. }

view source code

Single

  1. const taos = require("td2.0-connector");
  3. const conn = taos.connect({
  4.   host: "localhost",
  5. });
  7. const cursor = conn.cursor();
  9. function prepareSTable() {
  10.   cursor.execute("CREATE DATABASE power");
  11.   cursor.execute("USE power");
  12.   cursor.execute(
  13.     "CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)"
  14.   );
  15. }
  17. function insertData() {
  18.   // init
  19.   cursor.stmtInit();
  20.   // prepare
  21.   cursor.stmtPrepare(
  22.     "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)"
  23.   );
  25.   // bind table name and tags
  26.   let tagBind = new taos.TaosBind(2);
  27.   tagBind.bindBinary("California.SanFrancisco");
  28.   tagBind.bindInt(2);
  29.   cursor.stmtSetTbnameTags("d1001", tagBind.getBind());
  31.   // bind values
  32.   let rows = [
  33.     [1648432611249, 10.3, 219, 0.31],
  34.     [1648432611749, 12.6, 218, 0.33],
  35.   ];
  36.   for (let row of rows) {
  37.     let valueBind = new taos.TaosBind(4);
  38.     valueBind.bindTimestamp(row[0]);
  39.     valueBind.bindFloat(row[1]);
  40.     valueBind.bindInt(row[2]);
  41.     valueBind.bindFloat(row[3]);
  42.     cursor.stmtBindParam(valueBind.getBind());
  43.     cursor.stmtAddBatch();
  44.   }
  46.   // execute
  47.   cursor.stmtExecute();
  48.   cursor.stmtClose();
  49. }
  51. try {
  52.   prepareSTable();
  53.   insertData();
  54. } finally {
  55.   cursor.close();
  56.   conn.close();
  57. }

view source code

Multiple

  1. function insertData() {
  2.   // init
  3.   cursor.stmtInit();
  4.   // prepare
  5.   cursor.stmtPrepare(
  6.     "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)"
  7.   );
  9.   // bind table name and tags
  10.   let tagBind = new taos.TaosBind(2);
  11.   tagBind.bindBinary("California.SanFrancisco");
  12.   tagBind.bindInt(2);
  13.   cursor.stmtSetTbnameTags("d1001", tagBind.getBind());
  15.   // bind values
  16.   let valueBind = new taos.TaosMultiBindArr(4);
  17.   valueBind.multiBindTimestamp([1648432611249, 1648432611749]);
  18.   valueBind.multiBindFloat([10.3, 12.6]);
  19.   valueBind.multiBindInt([219, 218]);
  20.   valueBind.multiBindFloat([0.31, 0.33]);
  21.   cursor.stmtBindParamBatch(valueBind.getMultiBindArr());
  22.   cursor.stmtAddBatch();
  24.   // execute
  25.   cursor.stmtExecute();
  26.   cursor.stmtClose();
  27. }

view source code

Insert Using SQL - 图6info

Multiple row binding is better in performance than single row binding, but it can only be used with INSERT statement while single row binding can be used for other SQL statements besides INSERT.

  1. using TDengineDriver;
  3. namespace TDengineExample
  4. {
  5.     internal class StmtInsertExample
  6.     {
  7.         private static IntPtr conn;
  8.         private static IntPtr stmt;
  9.         static void Main()
  10.         {
  11.             conn = GetConnection();
  12.             PrepareSTable();
  13.             // 1. init and prepare
  14.             stmt = TDengine.StmtInit(conn);
  15.             if (stmt == IntPtr.Zero)
  16.             {
  17.                 Console.WriteLine("failed to init stmt, " + TDengine.Error(stmt));
  18.                 ExitProgram();
  19.             }
  20.             int res = TDengine.StmtPrepare(stmt, "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)");
  21.             CheckStmtRes(res, "failed to prepare stmt");
  23.             // 2. bind table name and tags
  24.             TAOS_BIND[] tags = new TAOS_BIND[2] { TaosBind.BindBinary("California.SanFrancisco"), TaosBind.BindInt(2) };
  25.             res = TDengine.StmtSetTbnameTags(stmt, "d1001", tags);
  26.             CheckStmtRes(res, "failed to bind table name and tags");
  28.             // 3. bind values
  29.             TAOS_MULTI_BIND[] values = new TAOS_MULTI_BIND[4] {
  30.                 TaosMultiBind.MultiBindTimestamp(new long[2] { 1648432611249, 1648432611749}),
  31.                 TaosMultiBind.MultiBindFloat(new float?[2] { 10.3f, 12.6f}),
  32.                 TaosMultiBind.MultiBindInt(new int?[2] { 219, 218}),
  33.                 TaosMultiBind.MultiBindFloat(new float?[2]{ 0.31f, 0.33f})
  34.             };
  35.             res = TDengine.StmtBindParamBatch(stmt, values);
  36.             CheckStmtRes(res, "failed to bind params");
  38.             // 4. add batch
  39.             res = TDengine.StmtAddBatch(stmt);
  40.             CheckStmtRes(res, "failed to add batch");
  42.             // 5. execute
  43.             res = TDengine.StmtExecute(stmt);
  44.             CheckStmtRes(res, "faild to execute");
  46.             // 6. free 
  47.             TaosBind.FreeTaosBind(tags);
  48.             TaosMultiBind.FreeTaosBind(values);
  49.             TDengine.Close(conn);
  50.             TDengine.Cleanup();
  51.         }
  53.         static IntPtr GetConnection()
  54.         {
  55.             string host = "localhost";
  56.             short port = 6030;
  57.             string username = "root";
  58.             string password = "taosdata";
  59.             string dbname = "";
  60.             var conn = TDengine.Connect(host, username, password, dbname, port);
  61.             if (conn == IntPtr.Zero)
  62.             {
  63.                 Console.WriteLine("Connect to TDengine failed");
  64.                 Environment.Exit(0);
  65.             }
  66.             else
  67.             {
  68.                 Console.WriteLine("Connect to TDengine success");
  69.             }
  70.             return conn;
  71.         }
  75.         static void PrepareSTable()
  76.         {
  77.             IntPtr res = TDengine.Query(conn, "CREATE DATABASE power");
  78.             CheckResPtr(res, "failed to create database");
  79.             res = TDengine.Query(conn, "USE power");
  80.             CheckResPtr(res, "failed to change database");
  81.             res = TDengine.Query(conn, "CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
  82.             CheckResPtr(res, "failed to create stable");
  83.         }
  85.         static void CheckStmtRes(int res, string errorMsg)
  86.         {
  87.             if (res != 0)
  88.             {
  89.                 Console.WriteLine(errorMsg + ", " + TDengine.StmtErrorStr(stmt));
  90.                 int code = TDengine.StmtClose(stmt);
  91.                 if (code != 0)
  92.                 {
  93.                     Console.WriteLine($"falied to close stmt, {code} reason: {TDengine.StmtErrorStr(stmt)} ");
  94.                 }
  95.                 ExitProgram();
  96.             }
  97.         }
  99.         static void CheckResPtr(IntPtr res, string errorMsg)
  100.         {
  101.             if (TDengine.ErrorNo(res) != 0)
  102.             {
  103.                 Console.WriteLine(errorMsg + " since:" + TDengine.Error(res));
  104.                 ExitProgram();
  105.             }
  106.         }
  108.         static void ExitProgram()
  109.         {
  110.             TDengine.Close(conn);
  111.             TDengine.Cleanup();
  112.             Environment.Exit(1);
  113.         }
  114.     }
  115. }

view source code

Single

  1. // compile with
  2. // gcc -o stmt_example stmt_example.c -ltaos
  3. #include <stdio.h>
  4. #include <stdlib.h>
  5. #include <string.h>
  6. #include "taos.h"
  8. /**
  9.  * @brief execute sql only.
  10.  * 
  11.  * @param taos 
  12.  * @param sql 
  13.  */
  14. void executeSQL(TAOS *taos, const char *sql) {
  15.   TAOS_RES *res = taos_query(taos, sql);
  16.   int       code = taos_errno(res);
  17.   if (code != 0) {
  18.     printf("%s\n", taos_errstr(res));
  19.     taos_free_result(res);
  20.     taos_close(taos);
  21.     exit(EXIT_FAILURE);
  22.   }
  23.   taos_free_result(res);
  24. }
  26. /**
  27.  * @brief check return status and exit program when error occur.
  28.  * 
  29.  * @param stmt 
  30.  * @param code 
  31.  * @param msg 
  32.  */
  33. void checkErrorCode(TAOS_STMT *stmt, int code, const char* msg) {
  34.   if (code != 0) {
  35.     printf("%s. error: %s\n", msg, taos_stmt_errstr(stmt));
  36.     taos_stmt_close(stmt);
  37.     exit(EXIT_FAILURE);
  38.   }
  39. }
  41. typedef struct {
  42.   int64_t ts;
  43.   float current;
  44.   int voltage;
  45.   float phase;
  46. } Row;
  48. /**
  49.  * @brief insert data using stmt API
  50.  * 
  51.  * @param taos 
  52.  */
  53. void insertData(TAOS *taos) {
  54.   // init
  55.   TAOS_STMT *stmt = taos_stmt_init(taos);
  56.   // prepare
  57.   const char *sql = "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)";
  58.   int code = taos_stmt_prepare(stmt, sql, 0);
  59.   checkErrorCode(stmt, code, "failed to execute taos_stmt_prepare");
  60.   // bind table name and tags
  61.   TAOS_BIND tags[2];
  62.   char* location = "California.SanFrancisco";
  63.   int groupId = 2;
  64.   tags[0].buffer_type = TSDB_DATA_TYPE_BINARY;
  65.   tags[0].buffer_length = strlen(location);
  66.   tags[0].length = &tags[0].buffer_length;
  67.   tags[0].buffer = location;
  68.   tags[0].is_null = NULL;
  70.   tags[1].buffer_type = TSDB_DATA_TYPE_INT;
  71.   tags[1].buffer_length = sizeof(int);
  72.   tags[1].length = &tags[1].buffer_length;
  73.   tags[1].buffer = &groupId;
  74.   tags[1].is_null = NULL;
  76.   code = taos_stmt_set_tbname_tags(stmt, "d1001", tags);
  77.   checkErrorCode(stmt, code, "failed to execute taos_stmt_set_tbname_tags");
  79.   // insert two rows
  80.   Row rows[2] = {
  81.     {1648432611249, 10.3, 219, 0.31},
  82.     {1648432611749, 12.6, 218, 0.33},
  83.   };
  85.   TAOS_BIND values[4];
  86.   values[0].buffer_type = TSDB_DATA_TYPE_TIMESTAMP;
  87.   values[0].buffer_length = sizeof(int64_t);
  88.   values[0].length = &values[0].buffer_length;
  89.   values[0].is_null = NULL;
  91.   values[1].buffer_type = TSDB_DATA_TYPE_FLOAT;
  92.   values[1].buffer_length = sizeof(float);
  93.   values[1].length = &values[1].buffer_length;
  94.   values[1].is_null = NULL;
  96.   values[2].buffer_type = TSDB_DATA_TYPE_INT;
  97.   values[2].buffer_length = sizeof(int);
  98.   values[2].length = &values[2].buffer_length;
  99.   values[2].is_null = NULL;
  101.   values[3].buffer_type = TSDB_DATA_TYPE_FLOAT;
  102.   values[3].buffer_length = sizeof(float);
  103.   values[3].length = &values[3].buffer_length;
  104.   values[3].is_null = NULL;
  106.   for (int i = 0; i < 2; ++i) {
  107.     values[0].buffer = &rows[i].ts;
  108.     values[1].buffer = &rows[i].current;
  109.     values[2].buffer = &rows[i].voltage;
  110.     values[3].buffer = &rows[i].phase;
  111.     code = taos_stmt_bind_param(stmt, values); // bind param
  112.     checkErrorCode(stmt, code, "failed to execute taos_stmt_bind_param");
  113.     code = taos_stmt_add_batch(stmt); // add batch
  114.     checkErrorCode(stmt, code, "failed to execute taos_stmt_add_batch");
  115.   }
  116.   // execute
  117.   code = taos_stmt_execute(stmt);
  118.   checkErrorCode(stmt, code, "failed to execute taos_stmt_execute");
  119.   int affectedRows = taos_stmt_affected_rows(stmt);
  120.   printf("successfully inserted %d rows\n", affectedRows);
  121.   // close
  122.   taos_stmt_close(stmt);
  123. }
  125. int main() {
  126.   TAOS *taos = taos_connect("localhost", "root", "taosdata", NULL, 6030);
  127.   if (taos == NULL) {
  128.     printf("failed to connect to server\n");
  129.     exit(EXIT_FAILURE);
  130.   }
  131.   executeSQL(taos, "CREATE DATABASE power");
  132.   executeSQL(taos, "USE power");
  133.   executeSQL(taos, "CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
  134.   insertData(taos);
  135.   taos_close(taos);
  136.   taos_cleanup();
  137. }
  140. // output:
  141. // successfully inserted 2 rows

view source code

Multiple

  1. // compile with
  2. // gcc -o multi_bind_example multi_bind_example.c -ltaos
  3. #include <stdio.h>
  4. #include <stdlib.h>
  5. #include <string.h>
  6. #include "taos.h"
  8. /**
  9.  * @brief execute sql only and ignore result set
  10.  *
  11.  * @param taos
  12.  * @param sql
  13.  */
  14. void executeSQL(TAOS *taos, const char *sql) {
  15.   TAOS_RES *res = taos_query(taos, sql);
  16.   int       code = taos_errno(res);
  17.   if (code != 0) {
  18.     printf("%s\n", taos_errstr(res));
  19.     taos_free_result(res);
  20.     taos_close(taos);
  21.     exit(EXIT_FAILURE);
  22.   }
  23.   taos_free_result(res);
  24. }
  26. /**
  27.  * @brief exit program when error occur.
  28.  *
  29.  * @param stmt
  30.  * @param code
  31.  * @param msg
  32.  */
  33. void checkErrorCode(TAOS_STMT *stmt, int code, const char *msg) {
  34.   if (code != 0) {
  35.     printf("%s. error: %s\n", msg, taos_stmt_errstr(stmt));
  36.     taos_stmt_close(stmt);
  37.     exit(EXIT_FAILURE);
  38.   }
  39. }
  41. /**
  42.  * @brief insert data using stmt API
  43.  *
  44.  * @param taos
  45.  */
  46. void insertData(TAOS *taos) {
  47.   // init
  48.   TAOS_STMT *stmt = taos_stmt_init(taos);
  49.   // prepare
  50.   const char *sql = "INSERT INTO ? USING meters TAGS(?, ?) values(?, ?, ?, ?)";
  51.   int         code = taos_stmt_prepare(stmt, sql, 0);
  52.   checkErrorCode(stmt, code, "failed to execute taos_stmt_prepare");
  53.   // bind table name and tags
  54.   TAOS_BIND tags[2];
  55.   char     *location = "California.SanFrancisco";
  56.   int       groupId = 2;
  57.   tags[0].buffer_type = TSDB_DATA_TYPE_BINARY;
  58.   tags[0].buffer_length = strlen(location);
  59.   tags[0].length = &tags[0].buffer_length;
  60.   tags[0].buffer = location;
  61.   tags[0].is_null = NULL;
  63.   tags[1].buffer_type = TSDB_DATA_TYPE_INT;
  64.   tags[1].buffer_length = sizeof(int);
  65.   tags[1].length = &tags[1].buffer_length;
  66.   tags[1].buffer = &groupId;
  67.   tags[1].is_null = NULL;
  69.   code = taos_stmt_set_tbname_tags(stmt, "d1001", tags);
  70.   checkErrorCode(stmt, code, "failed to execute taos_stmt_set_tbname_tags");
  72.   // insert two rows with multi binds
  73.   TAOS_MULTI_BIND params[4];
  74.   // values to bind
  75.   int64_t ts[] = {1648432611249, 1648432611749};
  76.   float   current[] = {10.3, 12.6};
  77.   int     voltage[] = {219, 218};
  78.   float   phase[] = {0.31, 0.33};
  79.   // is_null array
  80.   char is_null[2] = {0};
  81.   // length array
  82.   int32_t int64Len[2] = {sizeof(int64_t)};
  83.   int32_t floatLen[2] = {sizeof(float)};
  84.   int32_t intLen[2] = {sizeof(int)};
  86.   params[0].buffer_type = TSDB_DATA_TYPE_TIMESTAMP;
  87.   params[0].buffer_length = sizeof(int64_t);
  88.   params[0].buffer = ts;
  89.   params[0].length = int64Len;
  90.   params[0].is_null = is_null;
  91.   params[0].num = 2;
  93.   params[1].buffer_type = TSDB_DATA_TYPE_FLOAT;
  94.   params[1].buffer_length = sizeof(float);
  95.   params[1].buffer = current;
  96.   params[1].length = floatLen;
  97.   params[1].is_null = is_null;
  98.   params[1].num = 2;
  100.   params[2].buffer_type = TSDB_DATA_TYPE_INT;
  101.   params[2].buffer_length = sizeof(int);
  102.   params[2].buffer = voltage;
  103.   params[2].length = intLen;
  104.   params[2].is_null = is_null;
  105.   params[2].num = 2;
  107.   params[3].buffer_type = TSDB_DATA_TYPE_FLOAT;
  108.   params[3].buffer_length = sizeof(float);
  109.   params[3].buffer = phase;
  110.   params[3].length = floatLen;
  111.   params[3].is_null = is_null;
  112.   params[3].num = 2;
  114.   code = taos_stmt_bind_param_batch(stmt, params); // bind batch
  115.   checkErrorCode(stmt, code, "failed to execute taos_stmt_bind_param_batch");
  116.   code = taos_stmt_add_batch(stmt);  // add batch
  117.   checkErrorCode(stmt, code, "failed to execute taos_stmt_add_batch");
  118.   // execute
  119.   code = taos_stmt_execute(stmt);
  120.   checkErrorCode(stmt, code, "failed to execute taos_stmt_execute");
  121.   int affectedRows = taos_stmt_affected_rows(stmt);
  122.   printf("successfully inserted %d rows\n", affectedRows);
  123.   // close
  124.   taos_stmt_close(stmt);
  125. }
  127. int main() {
  128.   TAOS *taos = taos_connect("localhost", "root", "taosdata", NULL, 6030);
  129.   if (taos == NULL) {
  130.     printf("failed to connect to server\n");
  131.     exit(EXIT_FAILURE);
  132.   }
  133.   executeSQL(taos, "DROP DATABASE IF EXISTS power");
  134.   executeSQL(taos, "CREATE DATABASE power");
  135.   executeSQL(taos, "USE power");
  136.   executeSQL(taos,
  137.              "CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), "
  138.              "groupId INT)");
  139.   insertData(taos);
  140.   taos_close(taos);
  141.   taos_cleanup();
  142. }
  144. // output:
  145. // successfully inserted 2 rows

view source code