/*Initialize a MySQL database * *Localhost: hostname, or ip address *Type: the type of database. Currently, it supports two types: mysql and sqlite3 *Username: database user name *Password: database user password *Database: The name of the database to be accessed. If not set, the default database will be automatically connected * *If you specify a port reversely, the incoming port can be displayed: * " sql://localhost:3306/?type=mysql&username=xxxx&password=xxxx&database=xxxx " * *The database access url of sqlite3 is: * " sql:///home/file.sqlitedb?type=sqlite3 " * *Or directly transfer the file path: * "/home/file.sqlite3" * " file:///home/file.sqlitedb " * "C://home/file.sqlite3" */ tb_database_sql_ref_t database = tb_database_sql_init(" sql://localhost/?type=mysql&username=xxxx&password=xxxx&database=test "); if (database) { //Open Database if (tb_database_sql_open(database)) { //Execute SQL statement and query if (tb_database_sql_done(database, "select * from test")) { /*Load result set * *If insert, update and other non select statements are executed, tb_null is returned at this time, indicating that there is no result set * *This result set completely adopts the iterator pattern, which is convenient for fast iterative access. * *The following parameter tb_true indicates that all results should be loaded into memory as soon as possible. If successful *You can get the actual number of result set rows through tb_iterator_size (result). * *If one-time loading fails or tb_false is passed in, it means that you can only fetch rows of rollback data *In this way, the resources that occupy memory are less, but the actual number of rows cannot be obtained in advance. At this time, tb_iterator_size (result) *A super value is returned */ tb_iterator_ref_t result = tb_database_sql_result_load(database, tb_true); if (result) { //If the one-time loading is successful, the actual number of result lines will be returned tb_trace_i("row: size: %lu", tb_iterator_size(result)); //Traverse all rows tb_for_all_if (tb_iterator_ref_t, row, result, row) { //Display the number of columns in the row tb_trace_i("[row: %lu, col: size: %lu]: ", row_itor, tb_iterator_size(row)); //Traverse all values in this row tb_for_all_if (tb_database_sql_value_t*, value, row, value) { /*Tb_database_sql_value_name (value): get the column name corresponding to the value *Tb_database_sql_value_text (value): Get the text data of the value, if it is of text type *Tb_database_sql_value_type (value): Gets the type of the value * * ... */ tb_tracet_i("[%s:%s] ", tb_database_sql_value_name(value), tb_database_sql_value_text(value)); } } //Release result set data tb_database_sql_result_exit(database, result); } } else { //Execution failed, displaying failure status and reason tb_trace_e("done %s failed, error: %s", sql, tb_state_cstr(tb_database_sql_state(database))); } } else { //Opening failed, displaying the failure status and reason tb_trace_e("open failed: %s", tb_state_cstr(tb_database_sql_state(database))); } //Exit Database tb_database_sql_exit(database); }
// done tb_database_sql_statement_ref_t stmt = tb_null; tb_stream_ref_t stream = tb_null; do { //To initialize an insert statement, you need to customize the parameters passed in with the? Replace and pre compile if (! (stmt = tb_database_sql_statement_init(database, "insert into table2 values(?, ?, ?, ?, ?, ?, ?)"))) { // trace tb_trace_e("stmt: init %s failed, error: %s", sql, tb_state_cstr(tb_database_sql_state(database))); break ; } //Initialize a large image data stream stream = tb_stream_init_from_url("/tmp/large_image.png"); tb_assert_and_check_break(stream); //Open Stream if (! tb_stream_open(stream)) break; //Parameter list tb_database_sql_value_t list[7]; //Bind a parameter of type text, and the sql corresponds to: text tb_database_sql_value_set_text(&list[0], "hello", 0); //Bind a uint16 blob data parameter. Since it is a small piece of data, stream is not used. The SQL statement corresponds to: blob tb_database_sql_value_set_blob16(&list[1], data, size); //Bind an ultra small data block of uint8 size to save storage space. In SQL, it corresponds to: tinyblob tb_database_sql_value_set_blob8(&list[2], data, size); //Bind a huge uint32 data block. In SQL, the corresponding: longblob tb_database_sql_value_set_blob32(&list[3], data, size, tb_null); //Bind a huge uint32 data stream to save memory. Data will not be loaded into memory for processing at one time. The corresponding SQL statement is: longblob tb_database_sql_value_set_blob32(&list[4], tb_null, 0, stream); //Bind an int32 integer, which corresponds to int in SQL tb_database_sql_value_set_int32(&list[5], number); //Bind an int16 integer, which corresponds to smallint in SQL tb_database_sql_value_set_int16(&list[6], snumber); //Binding parameter list if (! tb_database_sql_statement_bind(database, stmt, list, tb_arrayn(list))) { // trace tb_trace_e("stmt: bind %s failed, error: %s", sql, tb_state_cstr(tb_database_sql_state(database))); break ; } //Execute pre compiled sql statements if (! tb_database_sql_statement_done(database, stmt)) { // trace tb_trace_e("stmt: done %s failed, error: %s", sql, tb_state_cstr(tb_database_sql_state(database))); break ; } } while (0); //Delete sql statement object if (stmt) tb_database_sql_statement_exit(database, stmt); //Exit Data Flow if (stream) tb_stream_exit(stream);
//Load result set in rollback mode tb_iterator_ref_t result = tb_database_sql_result_load(database, tb_false); if (result) { //Traverse rows tb_for_all_if (tb_iterator_ref_t, row, result, row) { //Get text data tb_database_sql_value_t const* name = tb_iterator_item(row, 0); tb_assert_and_check_break(name); tb_tracet_i("[%s:%s] ", tb_database_sql_value_name(name), tb_database_sql_value_text(name)); //Get blob16 data tb_database_sql_value_t const* data = tb_iterator_item(row, 1); tb_assert_and_check_break(data); tb_tracet_i("[%s:%s] ", tb_database_sql_value_name(data), tb_database_sql_value_blob(data)); //Get blob32 data tb_database_sql_value_t const* ldata = tb_iterator_item(row, 4); tb_assert_and_check_break(ldata); { /*Try to get the data buffer first, if it exists * *Optimized for small pieces of data, even the blob32 data, if the data size *It is not big, and will be directly stored in the memory buffer for fast reading */ tb_stream_ref_t stream = tb_null; if (tb_database_sql_value_blob(ldata)) { //Get data and size tb_byte_t const* data = tb_database_sql_value_blob(ldata); tb_size size = tb_database_sql_value_size(ldata); // ... } //If the data buffer cannot be obtained, it indicates that the data volume is large. Try to obtain the data stream for processing else if ((stream = tb_database_sql_value_blob_stream(ldata))) { //Read the data from the stream. Note: This stream does not support write operations and can only be used for reading // ... } } //Get int32 data. If it is int8, int16 or float type data, it will be forced automatically tb_database_sql_value_t const* number = tb_iterator_item(row, 5); tb_assert_and_check_break(number); tb_tracet_i("[%s:%d] ", tb_database_sql_value_name(number), tb_database_sql_value_int32(number)); } //Exit Result Set tb_database_sql_result_exit(database, result); } 
