is anyone able to use fileDSN for ODBC connections in LC?

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

is anyone able to use fileDSN for ODBC connections in LC?

Geoff Canyon via use-livecode
Hi,
i am not able to use fileDSN for ODBC connections to a SQL server in LC Windows. Under Mac i did not test it already.

With fileDSN you define all the odbc settings/parameters in a file with the suffix .dsn.
When connecting using ODBC you can then use that dsn file. No need to setup an ODBC configuration.

Is anyone using it with success?

I´ve setup the fileDSN correctly in ODBC Manager in Windows to use the folder "c:\users\matthias\documents" as fileDSN folder
The folder contains a file test.dsn
In LC Windows i tried to connect with a connection string, but getting different errors.

with
get revOpenDatabase("ODBC","FILEDSN=c:\users\matthias\documents\test.dsn",,,)

i am getting this error:
[Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge
In English something like this: Invalid string or buffer length

with
get revOpenDatabase("ODBC","FILEDSN=test.dsn",,,)

i am getting this error:
[Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben
In english something like this:
The data source could not be found and there was not default driver defined


and using the normal way using a DSN, where the dsn is the name of the fileDSN
get revopendatabase("ODBC","test",,"matthias","mypassword")

Error:
[Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben
In english something like this:
The data source could not be found and there was not default driver defined


Before i file a bug report about it i want to make sure, that no one is successfully using fileDSN.

Regards

Matthias Rebbe

free tools for Livecoders:
https://instamaker.dermattes.de
https://winsignhelper.dermattes.de

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: is anyone able to use fileDSN for ODBC connections in LC?

Geoff Canyon via use-livecode
On 10/24/2018 11:09 AM, Matthias Rebbe via use-livecode wrote:
> Hi,
> i am not able to use fileDSN for ODBC connections to a SQL server in LC Windows. Under Mac i did not test it already.
>
> With fileDSN you define all the odbc settings/parameters in a file with the suffix .dsn.
> When connecting using ODBC you can then use that dsn file. No need to setup an ODBC configuration.
>
> Is anyone using it with success?

IIRC fileDSNs have always been a pain in Rev/LC.
But here's a hint from David Simpson from 2013 that might help:

put "DBQ=" & tAccessDBFilename & ";" & quote into tAccessPathname

put revOpenDatabase("ODBC", tAccessPathname, , tDestDBUsername,
tDestDBPassword) into tDestinationDBConnection

--
  Mark Wieder
  [hidden email]

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: is anyone able to use fileDSN for ODBC connections in LC?

Geoff Canyon via use-livecode
That cannot be right. The result would be something like,

DBQ=myFileName;"

Eh?

Bob S


> On Oct 25, 2018, at 12:47 , Mark Wieder via use-livecode <[hidden email]> wrote:
>
> put "DBQ=" & tAccessDBFilename & ";" & quote into tAccessPathname


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: is anyone able to use fileDSN for ODBC connections in LC?

Geoff Canyon via use-livecode
In reply to this post by Geoff Canyon via use-livecode
Thanks Mark,

isn´t the DBQ parameter used when you want to access a file based Database like Access .mdb files?

I tried it anyway, but added a &quote before tAccessDBFilename, as it seems that was missing in your example.
Still getting the buffer error

[Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge
In English something like this: Invalid string or buffer length


Regards,

Matthias







Matthias Rebbe

free tools for Livecoders:
https://instamaker.dermattes.de
https://winsignhelper.dermattes.de

> Am 25.10.2018 um 21:47 schrieb Mark Wieder via use-livecode <[hidden email]>:
>
> On 10/24/2018 11:09 AM, Matthias Rebbe via use-livecode wrote:
>> Hi,
>> i am not able to use fileDSN for ODBC connections to a SQL server in LC Windows. Under Mac i did not test it already.
>> With fileDSN you define all the odbc settings/parameters in a file with the suffix .dsn.
>> When connecting using ODBC you can then use that dsn file. No need to setup an ODBC configuration.
>> Is anyone using it with success?
>
> IIRC fileDSNs have always been a pain in Rev/LC.
> But here's a hint from David Simpson from 2013 that might help:
>
> put "DBQ=" & tAccessDBFilename & ";" & quote into tAccessPathname
>
> put revOpenDatabase("ODBC", tAccessPathname, , tDestDBUsername, tDestDBPassword) into tDestinationDBConnection
>
> --
> Mark Wieder
> [hidden email]
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: is anyone able to use fileDSN for ODBC connections in LC?

Geoff Canyon via use-livecode
Re: [resolved] Microsoft - Driver Manager ODBC - Invalid string or buffer length



Hi,
Are you using 64 bit sql server 2014? Please take a look at an article about:http://stackoverflow.com/questions/26745417/invalid-string-or-buffer-length.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.



> On Oct 25, 2018, at 15:04 , Matthias Rebbe via use-livecode <[hidden email]> wrote:
>
> Thanks Mark,
>
> isn´t the DBQ parameter used when you want to access a file based Database like Access .mdb files?
>
> I tried it anyway, but added a &quote before tAccessDBFilename, as it seems that was missing in your example.
> Still getting the buffer error
>
> [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge
> In English something like this: Invalid string or buffer length
>
>
> Regards,
>
> Matthias

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: is anyone able to use fileDSN for ODBC connections in LC?

Geoff Canyon via use-livecode
As the same ODBC driver (Windows 10 SQL Server) works w/o a problem when using a user or system DSN connection, the driver should not be the problem.

Regards,


Matthias Rebbe

free tools for Livecoders:
https://instamaker.dermattes.de
https://winsignhelper.dermattes.de

> Am 26.10.2018 um 00:21 schrieb Bob Sneidar via use-livecode <[hidden email]>:
>
> Re: [resolved] Microsoft - Driver Manager ODBC - Invalid string or buffer length
>
>
>
> Hi,
> Are you using 64 bit sql server 2014? Please take a look at an article about:http://stackoverflow.com/questions/26745417/invalid-string-or-buffer-length.
> Best regards
> Sabrina
> --
> Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
>
>
>
>> On Oct 25, 2018, at 15:04 , Matthias Rebbe via use-livecode <[hidden email]> wrote:
>>
>> Thanks Mark,
>>
>> isn´t the DBQ parameter used when you want to access a file based Database like Access .mdb files?
>>
>> I tried it anyway, but added a &quote before tAccessDBFilename, as it seems that was missing in your example.
>> Still getting the buffer error
>>
>> [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge
>> In English something like this: Invalid string or buffer length
>>
>>
>> Regards,
>>
>> Matthias
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: is anyone able to use fileDSN for ODBC connections in LC?

Geoff Canyon via use-livecode
I´ve filed a bug now. #21658 <https://quality.livecode.com/show_bug.cgi?id=21658>

If anyone has additional comments please add them to the bug.

Regards,


Matthias Rebbe

free tools for Livecoders:
https://instamaker.dermattes.de
https://winsignhelper.dermattes.de

> Am 26.10.2018 um 00:33 schrieb Matthias Rebbe via use-livecode <[hidden email]>:
>
> As the same ODBC driver (Windows 10 SQL Server) works w/o a problem when using a user or system DSN connection, the driver should not be the problem.
>
> Regards,
>
>
> Matthias Rebbe
>
> free tools for Livecoders:
> https://instamaker.dermattes.de
> https://winsignhelper.dermattes.de
>
>> Am 26.10.2018 um 00:21 schrieb Bob Sneidar via use-livecode <[hidden email]>:
>>
>> Re: [resolved] Microsoft - Driver Manager ODBC - Invalid string or buffer length
>>
>>
>>
>> Hi,
>> Are you using 64 bit sql server 2014? Please take a look at an article about:http://stackoverflow.com/questions/26745417/invalid-string-or-buffer-length.
>> Best regards
>> Sabrina
>> --
>> Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
>>
>>
>>
>>> On Oct 25, 2018, at 15:04 , Matthias Rebbe via use-livecode <[hidden email]> wrote:
>>>
>>> Thanks Mark,
>>>
>>> isn´t the DBQ parameter used when you want to access a file based Database like Access .mdb files?
>>>
>>> I tried it anyway, but added a &quote before tAccessDBFilename, as it seems that was missing in your example.
>>> Still getting the buffer error
>>>
>>> [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge
>>> In English something like this: Invalid string or buffer length
>>>
>>>
>>> Regards,
>>>
>>> Matthias
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: is anyone able to use fileDSN for ODBC connections in LC?

Geoff Canyon via use-livecode
Hi all,

it seems that my syntax was not correct. LC first confirmed that my syntax looked correct,
but Panos found out that it didn´t.

The correct syntax for fileDSN is
revOpenDatabase("odbc","<PATH to the dsnFile;uid=<DB username>;pwd=<DB password>",,,)
Example for Windows
revOpenDatabase("odbc","filedsn=\Users\matthias\ODBC\test2.dsn;uid=matthias;pwd=test123",,,)

@Panos Thanks again.


Matthias Rebbe

free tools for Livecoders:
https://instamaker.dermattes.de
https://winsignhelper.dermattes.de

> Am 27.10.2018 um 18:53 schrieb Matthias Rebbe via use-livecode <[hidden email]>:
>
> I´ve filed a bug now. #21658 <https://quality.livecode.com/show_bug.cgi?id=21658>
>
> If anyone has additional comments please add them to the bug.
>
> Regards,
>
>
> Matthias Rebbe
>
> free tools for Livecoders:
> https://instamaker.dermattes.de
> https://winsignhelper.dermattes.de
>
>> Am 26.10.2018 um 00:33 schrieb Matthias Rebbe via use-livecode <[hidden email]>:
>>
>> As the same ODBC driver (Windows 10 SQL Server) works w/o a problem when using a user or system DSN connection, the driver should not be the problem.
>>
>> Regards,
>>
>>
>> Matthias Rebbe
>>
>> free tools for Livecoders:
>> https://instamaker.dermattes.de
>> https://winsignhelper.dermattes.de
>>
>>> Am 26.10.2018 um 00:21 schrieb Bob Sneidar via use-livecode <[hidden email]>:
>>>
>>> Re: [resolved] Microsoft - Driver Manager ODBC - Invalid string or buffer length
>>>
>>>
>>>
>>> Hi,
>>> Are you using 64 bit sql server 2014? Please take a look at an article about:http://stackoverflow.com/questions/26745417/invalid-string-or-buffer-length.
>>> Best regards
>>> Sabrina
>>> --
>>> Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
>>>
>>>
>>>
>>>> On Oct 25, 2018, at 15:04 , Matthias Rebbe via use-livecode <[hidden email]> wrote:
>>>>
>>>> Thanks Mark,
>>>>
>>>> isn´t the DBQ parameter used when you want to access a file based Database like Access .mdb files?
>>>>
>>>> I tried it anyway, but added a &quote before tAccessDBFilename, as it seems that was missing in your example.
>>>> Still getting the buffer error
>>>>
>>>> [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge
>>>> In English something like this: Invalid string or buffer length
>>>>
>>>>
>>>> Regards,
>>>>
>>>> Matthias
>>>
>>> _______________________________________________
>>> use-livecode mailing list
>>> [hidden email]
>>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: is anyone able to use fileDSN for ODBC connections in LC?

Geoff Canyon via use-livecode
On 10/30/18 5:52 AM, Matthias Rebbe via use-livecode wrote:
> Hi all,
>
> it seems that my syntax was not correct. LC first confirmed that my syntax looked correct,
> but Panos found out that it didn´t.
>
> The correct syntax for fileDSN is
> revOpenDatabase("odbc","<PATH to the dsnFile;uid=<DB username>;pwd=<DB password>",,,)
> Example for Windows
> revOpenDatabase("odbc","filedsn=\Users\matthias\ODBC\test2.dsn;uid=matthias;pwd=test123",,,)

Panos also filed a dictionary fix for this syntax.

https://github.com/livecode/livecode/pull/6767/commits/6644e7ced22f8bb623d9f63a42ddf8a1c15d84b9

--
  Mark Wieder
  [hidden email]

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode