Having issues with SELECT statements

[fredlorenz]

I'm using dart_odbc to create a Flutter/Dart app that connects to a Microsoft SQL Server database. I've been able to connect to the database just fine, and I can run UPDATEs, DELETEs and INSERTs without issue. But I have been having a lot of trouble with SELECT statements.

For instance, running

query = "SELECT * FROM FPRICE_BAT WHERE (F01 = '$upc') AND (F902 = '$batchNumberString')"; print('QUERY: $query'); results = mssqlConnection.execute(query);

will result is a memory allocation error. In fact, almost any SELECT statement I run that uses an * results in a memory allocation error being generated. Any particular table might have 20 to 30 fields, and I'd rather not use columnConfig to define all of them, if possible.

Also, I've been running into issues where the fields within the mapping that is returned are inaccessible. For instance, when I execute the following code

String query = "SELECT F902 FROM SYS_TAB";
print('QUERY: $query');
List<Map<String, dynamic>> results = mssqlConnection.execute(query);

if (results.length == 0) {
     // ...
}

// Print results[0]
print(
    'RESULTS: $results\nRESULTS[0]: ${results[0]}\nRESULTS[0] type: ${results[0].runtimeType}\nRESULTS[0]["F902"]: ${results[0]["F902"]}');

I get the following

flutter: RESULTS: [{F902: 36}] RESULTS[0]: {F902: 36} RESULTS[0] type: _Map<String, dynamic> RESULTS[0]["F902"]: null

I'm guessing I get a null in the last one because the result should have been [{'F902': 36}], with the F902 in single quotes. Without it being so, I cannot access the value using results[0]["902"] (it gives me a null value).

When I execute the following code (it only executes when I add the columnConfig, and gives a memory allocation error otherwise)

  query = "SELECT F30, F31 FROM PRICE_TAB WHERE F01 = '$upc'";
  print('QUERY: $query');
  results = mssqlConnection.execute(
    query,
    columnConfig: {
      'F30': ColumnType(type: SQL_C_CHAR, size: 6),
      'F31': ColumnType(type: SQL_C_CHAR, size: 3),
    },
  );
  // Display all key value pairs for results[0]
  results[0].forEach((key, value) {
    print('Key: $key, Value: $value');
  });

I get

flutter: Key: F30, Value: ⸱㤹〰肁彨Ǻ flutter: Key: F31, Value: ⸱0⠥巐

F30 and F31 are supposed to be monetary values (doubles) and both look fine in the SQL database. I did change the type to SQL_C_DOUBLE and other data types, and nothing seems to give me anything besides random garbage. I still don't feel super comfortable using the types. If the value F30 is a double, I suspect I should use SQL_C_DOUBLE. But what about the size? Is the size 1 for one DWORD or 4 for bytes or something like that?

What exactly am I doing wrong? Using SELECT statements should be easy, but I am very confused right now. I have not had any luck with them using dart_odbc. Like I said, the other kinds of SQL statements that don't return values seem to work fine. Any help from anyone on this would be much appreciated.

[SL-Pirate]

will result is a memory allocation error. In fact, almost any SELECT statement I run that uses an * results in a memory allocation error being generated.

For this issue can be fixed with the column config

Any particular table might have 20 to 30 fields, and I'd rather not use columnConfig to define all of them, if possible.

That's the neat part. You don't (have to). All you have to do is define only the columns that has a potential to cause a memory error. The default size allocated for each column is 255. In other words, You have to define column size for every column that has a size bigger than 255.

columnConfig: { 'F30': ColumnType(type: SQL_C_CHAR, size: 6), 'F31': ColumnType(type: SQL_C_CHAR, size: 3), },

Again, you don't have to define size for values lesser than 255 unless you are running thin on memory in the host machine and want to perform any optimizations.

RESULTS[0]["F902"]: null

This is a verified issue and I will see what I can do. If I explain what's going on, the key that is returned by the odbc driver contains some unicode characters that are rather invisible. I'm not sure why this is happening but could be an issue with dart:ffi. So as a workaround, there should be a method on String that is removeUnicodeWhitespaces. So as a quick short term solution, you can iterate through the result map and check each key by removing whitespace on the key, and the key you are expecting to find

Eg:

final target = "F902";
for (final entry in results.entries)  {
  if (entry.key.removeUnicodeWhitespaces() == target.removeUnicodeWhitespaces() {
    // Target found
  }
} 

flutter: Key: F30, Value: ⸱㤹〰肁彨Ǻ flutter: Key: F31, Value: ⸱0⠥巐

I'm actually not sure what to do about this. You might have to try different configurations on the columnConfig. Also try giving a little bit higher value for the size.

PS: My computer is broken and I am replying to you on the phone. And I just realised some commits are missing from the repo as well. I hope the repair person doesn't screw with my data. And as you guessed I probably won't be able to do the fixes from my side until I get my computer. I am very sorry about thay. Thank you for your understanding.

[SL-Pirate]

The issue has been fixed in version 2.1.0. Methods such as String.removeUnicodeWhitespaces which are supposed to be quick fixes has been depricated as I have addressed the root cause of this issue. Sorry for taking this long to resolve the issue. Closing as completed