execute method

@override Future<int?> execute(String sqlToExecute, { List? arguments, bool getLastInsertId = false, String? primaryKey, })

Execute an insert, update or delete using sqlToExecute in normal or prepared mode using arguments.

This returns the number of affected rows. Only if getLastInsertId is set to true, the id of the last inserted row is returned. The primaryKey is sometimes necessary to retrieve the last inserted id.

Implementation

@override
Future<int?> execute(String sqlToExecute,
    {List<dynamic>? arguments,
    bool getLastInsertId = false,
    String? primaryKey}) async {
  int count = 1;
  Map<String, dynamic>? paramsMap;
  if (arguments != null) {
    paramsMap = {};
    for (var arg in arguments) {
      String placeHolder = "p$count";
      paramsMap[placeHolder] = arg;
      sqlToExecute = sqlToExecute.replaceFirst("?", "@" + placeHolder);
      count++;
    }
  }

  PostgreSQLResult? sqlResult;
  try {
    if (getLastInsertId) {
      if (sqlToExecute.trimRight().endsWith(";")) {
        sqlToExecute = sqlToExecute.substring(0, sqlToExecute.length - 1);
      }
      var finalSql = "$sqlToExecute RETURNING $primaryKey";
      sqlResult = await _db?.query(finalSql, substitutionValues: paramsMap);
      return sqlResult?.last[0] as int;
      // throw Exception("Not implemented");
      // return _db.lastInsertRowId;

      // TODO this is now supported as
      // final primaryKeyName = 'id';
      // final res = await yourConnection.query('INSERT INTO $tableName ($keys) VALUES ($values) RETURNING $primaryKeyName;');
      // final lastInsertedId = res.last[0] as int;
    } else {
      sqlResult =
          await _db?.query(sqlToExecute, substitutionValues: paramsMap);
      return sqlResult?.affectedRowCount;
    }
  } on Exception catch (e, s) {
    SLogger().e("Execute error.", e, s);
    rethrow;
  }
}