如何使用参数插入多行？ [英] How to insert multiple rows with a parameter?

问题描述

我正在尝试使用离子框架在SQLite中插入多行。单排插入工作正常。

I am trying to insert multiple rows in SQLite using ionic framework. Single row insert in working fine.

即使我运行

 INSERT INTO categories (category_id, category_name,category_type) VALUES (1,"test",1),(2,"test again", 2); 

这也正常。
但是当我尝试创建一个动态字符串时，它给出了错误无法准备语句（1接近？：语法错误）。

this is also working fine. but when i try to create a dynamic string it gives me error "could not prepare statement (1 near "?": syntax error)".

.success((function (result) {
                 var query = "INSERT INTO categories (category_id, category_name,category_type) VALUES ?";
                 var data = [];
                 result.forEach(function (category) {
                         data.push([category.id, category.category_name, category.category_type]);
                     });
    $cordovaSQLite.execute(db, query,[data]).then(function (res) {
         console.log("inserted");
     }, function (err) {
   console.dir(err);
    });

推荐答案

添加多个插入的参数，就像你在测试查询中所做的那样（你提到的第一个），然后将所有参数作为一维数组传递：

Add multiple parameters to your insert, just like you do in your test query (the first you mentioned), then pass all arguments as a one dimensional array:

.success((function (result) {
                 var query = "INSERT INTO categories (category_id, category_name,category_type) VALUES ";
                 var data = [];
                 var rowArgs = [];
                 result.forEach(function (category) {
                         rowArgs.push("(?, ?, ?)");
                         data.push(category.id);
                         data.push(category.category_name);
                         data.push(category.category_type);
                     });
                 query += rowArgs.join(", ");
    $cordovaSQLite.execute(db, query,[data]).then(function (res) {
         console.log("inserted");
     }, function (err) {
   console.dir(err);
    });

此代码将生成以下查询：

This code will produce query like:

INSERT INTO categories (category_id, category_name,category_type) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?);

并且您的数据数组大小为12，其中数组中的每3个条目将是一行要插入的数据。

and your data array will be of size 12, where every 3 entries in array will be one row of data to be inserted.

这些数字只是示例，它们取决于结果的大小。

Those numbers are just example and they depend on the size of result.

这篇关于如何使用参数插入多行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！