feedback on connection

[macMikey]

other than not getting an error when calling odbc.connect, is there any way to get feedback and troubleshoot? when i connect to the datasource, i am not getting an error. when i run queries, i don't seem to be getting any results back, but my other custom odbc tools are functioning.

[SL-Pirate]

If there is an error you should be getting the error message because it is handled in the way recommend by the odbc documentation. If you are not getting any results, it is possible that you are pointing to a wrong driver.

For further assistance please provide what driver you are using and what database you are connecting to.

[macMikey]

thanks for the reply. i'm using c:\windows\system32\odbc32.dll

i am connecting to an eloquence sql/r db. like i said, i am not even getting an error when i submit an invalid query.

[macMikey]

by the way, in case i misunderstood the directions, and you meant that i should find the driver in the "drivers" tab of the control panel, i did that.

the path to that driver is C:\Program Files (x86)\Marxmeier\SQLR\bin\sqlro32.dll when i try to use that as the driver path, i get this error:

for the purpose of debugging, i tried both pulling the dsn from the .env file, and hardcoding it. i also tried using forward and backslashes for the directory delimiter.

[SL-Pirate]

Now you are on the right track. Again you are using the wrong driver but now you are closer to finding the right driver. Please refer to the documentation of your specific database vendor. From what I saw, the package for windows comes with odbc driver. Usually the name comes with an indication that it is in fact the odbc driver. The library should work without an issue given that you are using the correct driver. Good luck.

Edit: maybe check C:\Program Files (x86)\Marxmeier\SQLR\bin\ folder. You should find the correct driver here.

[macMikey]

i don't understand what that means. i switched to C:\Program Files (x86)\Marxmeier\SQLR\bin\sqlro32.dll, but i get the argument error, that i showed, above. that file is the one that shows up in the driver tab of the odbc settings panel. here are the contents of that folder:

and here is the odbc data source admin, driver tab:

[SL-Pirate]

Interesting. You are right. The driver manager in fact recognizes the driver to be the odbc driver But it is missing even the basic symbols such as SQLAllocHandle. Can you try reinstalling the odbc driver?

[macMikey]

sure. just an fyi, i tested my other odbc clients, so i could basline, make sure my queries were correct, etc. those other clients worked. also, based on the error, you think that the right file is selected as the driver, correct? why, when i had the wrong file selected, originally, was there no error? (just trying to understand)

[macMikey]

ok, did uninstall/reinstall. then i thought i'd try to run the 64-bit admin to see if maybe the problem was a mismatch between 32 and 64 bit, and, i got a 193 error - interesting, because that's what the flutter app is giving me.

so i did a little reasearch. it seems that there might be some connection between some issue between 32 and 64-bit...something, but i don't understand what.

[macMikey]

next, i tried to switch to using the 64 bit driver, in case that makes any difference. since we don't even make it as far as odbcConnect(), i hoped that the dsn pointing to the 32-bit driver wouldn't matter. now i get a different error (127). this seems to suggest that 32-bit is the only option:

32-bit driver error:

[SL-Pirate]

Interesting indeed. I am sorry for having to go through all this and still not finding a solution. Sadly there's not much I can do. If the library fails to find the method signature that sounds like an issue with the odbc driver.

[macMikey]

any idea on why the other clients might be working, or a way for trying to get to a root cause?

[SL-Pirate]

Are you sure other clients are in fact using odbc driver? They could be using the native driver instead. Or there's something both of us are missing.

[macMikey]

this is the first time i've had to specify the driver. with the other tools, i simply select the system dsn. all of the dsn's point to the marxmeier 32 bit driver.

[SL-Pirate]

The library is still in development. And if I were to implement I would have to implement auto detecting on all platforms which comes with different configurations. But you raise a valid point. I might actually consider implementing this in the future. Thanks for your feedback 😃

[macMikey]

it's not that it's bad, i just don't know why my code isn't working, when the other clients i've written with other dev tools do work. i'd like to get it working. that would mean i could make flutter the default dev tool. unfrortunately, odbc is the only way we have for communcating with our erp server.

[SL-Pirate]

I understand your concern but I am really sorry there's nothing much I can do at this point. :(

[SL-Pirate]

I have reproduced your issue from my end. It is definitely an issue with the driver and not your installation itself. So, if you happen to have support for the product maybe try raising an issue? Because 'SQLAllocHandle' method is a core functionality and is specified in the odbc standard. If dart:ffi doesn't find it, it then it is obviously not there. I have no idea how your other programs work so I just assume they are using native drivers instead (not odbc) 🤷🏽‍♂️.

[SL-Pirate]

I think I have an idea what's going on here. My driver is based on odbc 3.0 specification and apparentlySQLAllocHandle was introduced in odbc 3.0. it is possible that the sql/r driver is using an older version of the spec. I'll see what I can do. 😊

[macMikey]

ooh, that would be cool. crossing fingers

[SL-Pirate]

I have updated the library. You might want to set utfType to UtfType.utf8 in the constructor. Please refer the troubleshoot section of the documentation for further details. I really hope this works out for you. ☺️

[macMikey]

i really appreciate your effort on this. update with the updated library:

• if i just use dsn, i get an error that the driver can't be found:

  

• if i specify the driver, regardless of the utf, i get invalid application: WITH utf specified:

  

  WITHOUT UTF8 specified:

  

[SL-Pirate]

Thank you for the update. The error you are getting is due to erroneous configuration of driver manager. Assuming you installed 64bit version of the odbc driver you should user 64bit odbc driver manager. You can find the difference when you search odbc driver manager in windows search/start.

[macMikey]

i have the 32 bit version installed and configured (and all the DSN's are configured with it), because the 64 bit odbc data administrator does not seem to work. i think i remember this might be an arm compatibility issue (my dev/normal-use machine has an arm processor). if i select the 64 bit odbc driver in the 64 bit odbc admin, i get this

note that on intel pc's, the 64 bit admin works, and the 64-bit driver works. i have a server with a core i7 with a 64-bit processor that uses the 64-bit drivers.

on my arm-based pc (the one i'm using for dev/normal workflows), if i go into the 32 bit administrator, and choose the 32 bit driver, i can set up new DSN's all day.

[SL-Pirate]

I see. I got the driver to work on my windows vm. At some point I got the error you are getting and I fixed it by using 64bit driver manager. Also I think I know why it says driver not found. I will push another update and see if that fixes the driver not found issue

[macMikey]

using 4.1.1+1, still getting driver not found using dsn only

[macMikey]

did you get a chance to mess with this, any more?

[SL-Pirate]

I'm sorry I've been busy with work and uni

[macMikey]

no worries! just checking.

[SL-Pirate]

Hello @macMikey. I'm really sorry for taking this long to give an update. I have applied some new fixes. This should be a major fix. You can I have published 4.1.2+1. Please do not provide path to driver this time and hopefully it will detect the driver automatically. If it is still unable to find the path please update me.

[macMikey]

yay! thanks for all your effort on this. i appreciate it. it'll be great if we can get this working, and start using dart + flutter for our corporate apps.

[macMikey]

seems like we're getting a little closer.

Connecting to VM Service at ws://127.0.0.1:50924/Mq0lAKkNYfk=/ws
Connected to the VM Service.
flutter: Error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
flutter: ErrorCode: -1
Lost connection to device.

here's the code:

class EloqOdbc {
  String? username;
  String? password;
  DartOdbc? odbc;

  Future<void> eloqOdbc({
    required String username,
    required String password,
  }) async {
    this.username = username;
    this.password = password;

    await dotenv.load(fileName: ".env");
    final dsn = dotenv.env['DSN'];

    try {
      odbc = DartOdbc(
        dsn: dsn,
      );
      await odbc!.connect(username: username, password: password);
    } catch (e) {
      debugPrint('Error: $e');
      rethrow;
    }
  }

[SL-Pirate]

Like I said this is not an issue with the library nor the connection. You are in fact using an incompatible driver. You said 64bit odbc driver manager does no work. Try downloading the 32bit driver and setting it up with the 32bit driver manager.

PS: you said it was working fine with other software. So you once had the correct configuration. I'm the one who suggested you to try reinstalling the driver. I should apologise for breaking your already working setup. I'm sorry about that.

[macMikey]

i don't understand. on this machine, things are working, with other tools. i'm pretty sure that those tools are using the 32 bit driver. i don't know why, but if i was guessing, i would guess that on arm, odbc is running in a 32 bit compatibility layer (even though a 64 bit version of windows is installed).

[SL-Pirate]

As you can see the issue is directly thrown from the driver manager itself. So there's no doubt it's an issue with the architecture mismatch of the driver

[macMikey]

i don't know what your code is trying to do. is it possible that you are trying to use the wrong driver? why would the other dev tools be working on this box?

[SL-Pirate]

That is a very good question. Up until recently I was finding the driver myself, or asking the user for the path of the driver. But zi'm not doing that anymore. Now I am directly communicating with the driver manager as a middleware. So it is very unlikely that the driver is wrong.

On second thoughts, it is possible that the flutter app is running on arm or x86_64 architecture and trying to access the 32bit driver? Sorry but there's nothing I can do about it. I suggest you try it on a 64bit pc and I bet it would work fine.

[macMikey]

it's strange that the other tools (including excel) are working.

[SL-Pirate]

What do you mean

Including excel

How is it even related to this?

[macMikey]

it's strange that even using excel as an odbc client, auto-selecting the driver from the dsn, works. excel is running x64.

[SL-Pirate]

That is to be expected. Like I said it's an issue with the configuration the specific driver. Now that you mention it, the application is running as a 64bit application right? But your driver is configured to be a 32 bit driver. Maybe that is the mismatch. Can you try downloading and installing the 64bit driver and see?

[macMikey]

we already tried that, here and here. the other dev tools all seem to use the 32 bit driver, even if they are 64 bit apps.

[SL-Pirate]

https://github.com/SL-Pirate/dart_odbc/issues/8#issuecomment-2408305226

Can you try the driver you used here? Give the same path to the driver path argument. If it doesn't tell you it is not s valid win32 application, then your driver installation is fine and the problem is with your driver manager configuration (which is the most probable case). Else, you have installed the wrong driver.

[macMikey]

you can't set up 64 bit dsn's on this setup. in the 64 bit control panel, attempting to add a dsn gives this:

[SL-Pirate]

next, i tried to switch to using the 64 bit driver, in case that makes any difference. since we don't even make it as far as odbcConnect(), i hoped that the dsn pointing to the 32-bit driver wouldn't matter. now i get a different error (127). this seems to suggest that 32-bit is the only option:

32-bit driver error:

I see the issue now. The library was able to load the driver when you were using the 64bit driver. But unfortunately your setup cannot set dsn for 64bit drivers. So I can confirm the issue is with your setup. Please try this on a 64bit machine. On the other hand flutter does not support running as a 32bit application either. So your only hope is to get your driver manager fixed.

[macMikey]

it is a 64 bit ARM machine, not x86. it's running 64 bit windows for ARM. my flutter apps run fine. i will see if i can get onto an x86, some time.