Flutter使用sql_conn读取以0/-开头的ProductBarcode时出现意外字符错误

表中ProductBarcode字段为nvarchar类型，但读取以0或-开头的条码（如"-1288865"、"-01194070"）时，触发“意外字符”错误，怀疑是JSON解析环节导致。

相关代码 #

read() 函数 #

Future<List?> read(String query) async {
  var result;
  await _readLock.synchronized(() async {
    result = await SqlConn.readData(query);
  });
  
  List list = jsonDecode(result) as List;
  return list;
}

SQL 查询与 FutureBuilder 代码 #

Expanded(
  child: FutureBuilder<List?>(
      future: read("SELECT Id, OrderID, RowID, ProductCode, ProductAdress+' ' ProductAdress, REPLACE(REPLACE(ProductName, '\"', ''''), '\t', ' ') AS ProductName, CAST(NeedCount AS VARCHAR) AS NeedCount, ProductSeriesDescr+' ' ProductSeriesDescr, ScanCount, ScanPost, ProductSeriesCount+' ' ProductSeriesCount, ProductExpirationDate, CAST(ProductBarCode AS VARCHAR) AS ProductBarCode FROM ScanRest WHERE ProductStation = '${widget.nrStatie}' AND BoxID = '$cutieScan' and OrderDate = CONVERT(date, GETDATE()) ORDER BY Id ASC"),
      builder: (context, snapshot) {
        switch (snapshot.connectionState) {
          case ConnectionState.waiting:
            return const Text('Loading....');
          default:
            if (snapshot.hasError) {
              debugPrint("call error ${snapshot.error}"); //"call error = ${snapshot.error}"  
              return Text('Error: ${snapshot.error}');
            }
            debugPrint('${snapshot.data}'); // "call success = ${snapshot.data}"
            List data = snapshot.data ?? [];
            if (snapshot.data!.isEmpty) {
              return Visibility(
                  visible: !vizibil,
                  child: Column(children: [
                    const Text('Barcode gresit sau statie goala'),
                    IconButton(
                        onPressed: () {
                          Navigator.push(context,
                            MaterialPageRoute(
                              builder: (context) => widget));
                        },
                        icon: const Icon(Icons.replay_outlined)),
                  ]));
            }
            
            for (var i = 0; i < data.length; i++) {
              if (data[i]['ProductBarCode'] is String) {
                data[i]['ProductBarCode'] = data[i]['ProductBarCode'].replaceAll('0', 'A');
              }
            }


            int index = 0;
            for (var i = 0; i < data.length; i++) {
              if (int.parse(data[i]['NeedCount'].toString()) - int.parse(data[i]['ScanCount'].toString()) == 0 || data[i]['ScanPost'] == 1) {
                if (index == i) {
                  index++;
                }
              }
              if (index == data.length) {
                WidgetsBinding.instance.addPostFrameCallback((_) {
                  if(_counter != 0){
                    notificationAlert(context, 'Scanat: $_counter', const Color.fromARGB(255, 255, 218, 185), Colors.black);
                  }
                  Navigator.push(
                    context,
                    MaterialPageRoute(
                      builder: (context) => dita_lista(
                        nrStatie: widget.nrStatie,
                        cutieScan: cutieScan,
                        userName: widget.userName,
                      ),
                    ),
                  );
                });
              }
            }

问题排查与解决方案 #

核心原因 #

问题出在sql_conn的JSON序列化逻辑：当ProductBarCode字段值以-开头时，sql_conn可能未将其作为字符串包裹引号，直接输出为-1288865格式；而-01194070这种带前导0的“数字”违反JSON标准（JSON不允许数字有前导0），直接触发jsonDecode解析错误。

另外，SQL查询中CAST(ProductBarCode AS VARCHAR)属于多余操作（原字段已是nvarchar），反而可能让sql_conn误判字段类型，加剧序列化问题。

解决方案 #

1. 强制SQL返回字符串标识
   修改SQL查询，给ProductBarCode添加固定前缀，确保sql_conn将其识别为字符串：

   SELECT 
     -- 保留其他字段...
     'STR:' + ProductBarCode AS ProductBarCode 
   FROM ScanRest 
   -- 条件不变
   

   之后在Flutter代码中去除前缀：

   data[i]['ProductBarCode'] = data[i]['ProductBarCode'].toString().replaceFirst('STR:', '');
   

2. 预处理JSON字符串
   在jsonDecode前，用正则表达式给ProductBarCode值强制添加引号：

   Future<List?> read(String query) async {
     var result;
     await _readLock.synchronized(() async {
       result = await SqlConn.readData(query);
     });
     // 匹配无引号的ProductBarCode值，添加引号
     String processed = result.replaceAll(RegExp(r'"ProductBarCode":(-?\d+)'), r'"ProductBarCode":"$1"');
     List list = jsonDecode(processed) as List;
     return list;
   }
   

3. 移除多余的SQL转换
   删除查询中的CAST(ProductBarCode AS VARCHAR)，直接返回原始nvarchar类型字段，避免SQL对字符串的额外处理。

内容的提问来源于stack exchange，提问作者Dima C