9:19 a.m.
Hi all,
for the first time I am working with gretl's ODBC connector. So far it
seems to work fine for standard queries using the Exasol data base on
linux. However, I stumbled over to 2 issues:
1) I want to write a open_odbc_connection() function. Unfortunately, the
'open' command cannot be executed within a function. For its standard
application loading a data set I fully understand the reason. However,
for just opening a ODBC connection by the '--odbc" option, I don't get
rational for this restriction.
Feature request: Allow making use of the open command in case the --odbc
option is passed.
2) While loading data through the 'data' command, e.g. by executing
<data x query="@statement" --odbc>, this command does not seem to work
in case one wants to send a 'statement' -- like "create table" or
"drop
table" -- to the database which does not return a series.
Feature request: allow sending a sql-statement which does not return a
series or anything.
Maybe these points imply to add a new command or function. What do you
think?
Thanks,
Artur
9:46 a.m.
Am 19.02.2020 um 10:19 schrieb atecon:
1) I want to write a open_odbc_connection() function. Unfortunately,
the 'open' command cannot be executed within a function. For its
standard application loading a data set I fully understand the reason.
However, for just opening a ODBC connection by the '--odbc" option, I
don't get rational for this restriction.
Feature request: Allow making use of the open command in case the
--odbc option is passed.
Wanting this functionality sounds very reasonable, but
I'm noticing that
'open .... --odbc' does not seem to be documented in the command reference.
2) While loading data through the 'data' command, e.g. by executing
<data x query="@statement" --odbc>, this command does not seem to work
in case one wants to send a 'statement' -- like "create table" or
"drop table" -- to the database which does not return a series.
Feature request: allow sending a sql-statement which does not return a
series or anything.
Maybe these points imply to add a new command or function. What do you
think?
Hm, at first sight I don't find it compelling that gretl should become a
odbc management tool. The appendix in the guide is called "Data _import_
via ODBC" for a reason I'd say. Why can't you use other tools for that,
for example Python in a foreign block, or shell commands?
thanks
sven
5:41 p.m.
On Wed, 19 Feb 2020, Sven Schreiber wrote:
Am 19.02.2020 um 10:19 schrieb atecon:
>
> 1) I want to write a open_odbc_connection() function. Unfortunately,
> the 'open' command cannot be executed within a function. For its
> standard application loading a data set I fully understand the reason.
> However, for just opening a ODBC connection by the '--odbc" option, I
> don't get rational for this restriction.
> Feature request: Allow making use of the open command in case the
> --odbc option is passed.
Wanting this functionality sounds very reasonable, but I'm noticing that
'open .... --odbc' does not seem to be documented in the command reference.
As I mentioned previously, Artur's request is now granted. In response
to Sven's observation I've now added references to the ODBC option to
the doc for the "open" and "data" commands.
I've also added a little to the ODBC chapter of the User's Guide, and
changed its status to a regular chapter rather than an appendix. (That
makes it easier to hot-link into the online help.)
Allin
10:04 a.m.
Hi Allin,
Am 23.02.2020 18:41 schrieb Allin Cottrell:
On Wed, 19 Feb 2020, Sven Schreiber wrote:
> Am 19.02.2020 um 10:19 schrieb atecon:
> >
> > 1) I want to write a open_odbc_connection() function. Unfortunately,
> > the 'open' command cannot be executed within a function. For its
> > standard application loading a data set I fully understand the reason.
> > However, for just opening a ODBC connection by the '--odbc" option, I
> > don't get rational for this restriction.
> > Feature request: Allow making use of the open command in case the
> > --odbc option is passed.
>
> Wanting this functionality sounds very reasonable, but I'm noticing that
> 'open .... --odbc' does not seem to be documented in the command reference.
As I mentioned previously, Artur's request is now granted. In response
to Sven's observation I've now added references to the ODBC option to
the doc for the "open" and "data" commands.
I just compiled latest source code from git. Unfortunately, the 'open'
command still returns an error when calling the function:
<hansl>
function void open_odbc_connection (string DSN,
string USER,
string PW)
open dsn=@DSN user=@USER password=@PW --odbc
end function
</hansl>
The error message is: "The "open" command cannot be used in this context
*** error in function open_odbc_connection, line 1"
Outside the function, the "open" command works fine.
I've also added a little to the ODBC chapter of the User's
Guide, and
changed its status to a regular chapter rather than an appendix. (That
makes it easier to hot-link into the online help.)
Thanks for the update!
12:15 p.m.
On Mon, 24 Feb 2020, atecon wrote:
I just compiled latest source code from git. Unfortunately, the
'open'
command still returns an error when calling the function:
<hansl>
function void open_odbc_connection (string DSN,
string USER,
string PW)
open dsn=@DSN user=@USER password=@PW --odbc
end function
</hansl>
The error message is: "The "open" command cannot be used in this context
*** error in function open_odbc_connection, line 1"
It works OK here. Are you sure you're calling gretl built from current
git master?
Allin
1:37 p.m.
On Mon, 24 Feb 2020, Allin Cottrell wrote:
On Mon, 24 Feb 2020, atecon wrote:
> I just compiled latest source code from git. Unfortunately, the 'open'
> command still returns an error when calling the function:
[...]
It works OK here.
OK here too.
-------------------------------------------------------
Riccardo (Jack) Lucchetti
Dipartimento di Scienze Economiche e Sociali (DiSES)
Università Politecnica delle Marche
(formerly known as Università di Ancona)
r.lucchetti(a)univpm.it
http://www2.econ.univpm.it/servizi/hpp/lucchetti
-------------------------------------------------------
1:38 p.m.
Am 24.02.2020 13:15 schrieb Allin Cottrell:
On Mon, 24 Feb 2020, atecon wrote:
> I just compiled latest source code from git. Unfortunately, the 'open'
> command still returns an error when calling the function:
>
> <hansl>
> function void open_odbc_connection (string DSN,
> string USER,
> string PW)
> open dsn=@DSN user=@USER password=@PW --odbc
> end function
> </hansl>
>
> The error message is: "The "open" command cannot be used in this
> context
> *** error in function open_odbc_connection, line 1"
It works OK here. Are you sure you're calling gretl built from current
git master?
Sorry for the noise, "git pull" yielded some error here. Works fine now!
Thanks,
Artur
2:56 p.m.
On Mon, 24 Feb 2020, atecon wrote:
Am 24.02.2020 13:15 schrieb Allin Cottrell:
> On Mon, 24 Feb 2020, atecon wrote:
>
>> I just compiled latest source code from git. Unfortunately, the 'open'
>> command still returns an error when calling the function:
>>
>> <hansl>
>> function void open_odbc_connection (string DSN,
>> string USER,
>> string PW)
>> open dsn=@DSN user=@USER password=@PW --odbc
>> end function
>> </hansl>
>>
>> The error message is: "The "open" command cannot be used in this
context
>> *** error in function open_odbc_connection, line 1"
>
> It works OK here. Are you sure you're calling gretl built from current
> git master?
Sorry for the noise, "git pull" yielded some error here. Works fine now!
While we're on the subject: I need to pull a string-valued series from an
ODBC source, which is not possible at present (if I'm not mistaken). Shall
we leave this for after release?
-------------------------------------------------------
Riccardo (Jack) Lucchetti
Dipartimento di Scienze Economiche e Sociali (DiSES)
Università Politecnica delle Marche
(formerly known as Università di Ancona)
r.lucchetti(a)univpm.it
http://www2.econ.univpm.it/servizi/hpp/lucchetti
-------------------------------------------------------
3:10 p.m.
Am 28.02.20 um 15:56 schrieb Riccardo (Jack) Lucchetti:
On Mon, 24 Feb 2020, atecon wrote:
> Am 24.02.2020 13:15 schrieb Allin Cottrell:
>> On Mon, 24 Feb 2020, atecon wrote:
>>
>>> I just compiled latest source code from git. Unfortunately, the
'open'
>>> command still returns an error when calling the function:
>>>
>>> <hansl>
>>> function void open_odbc_connection (string DSN,
>>> string USER,
>>> string PW)
>>> open dsn=@DSN user=@USER password=@PW --odbc
>>> end function
>>> </hansl>
>>>
>>> The error message is: "The "open" command cannot be used in
this
>>> context
>>> *** error in function open_odbc_connection, line 1"
>>
>> It works OK here. Are you sure you're calling gretl built from current
>> git master?
>
> Sorry for the noise, "git pull" yielded some error here. Works fine now!
While we're on the subject: I need to pull a string-valued series from
an ODBC source, which is not possible at present (if I'm not
mistaken). Shall we leave this for after release?
As you say, currently not
possible. Would definitely be a useful feature!
Artur
4:58 p.m.
Am 28.02.2020 um 16:10 schrieb Artur Tarassow:
Am 28.02.20 um 15:56 schrieb Riccardo (Jack) Lucchetti:
> While we're on the subject: I need to pull a string-valued
series from
> an ODBC source, which is not possible at present (if I'm not
> mistaken). Shall we leave this for after release?
As you say, currently not possible. Would definitely be a useful feature!
I'm not using ODBC and so I could live with new bugs in that area, but
to me this sounds like it should be done without haste after the
release. You guys are running git versions all the time anyway, so what
does a release mean to you?
cheers
sven
5:04 p.m.
On Fri, 28 Feb 2020, Sven Schreiber wrote:
Am 28.02.2020 um 16:10 schrieb Artur Tarassow:
> Am 28.02.20 um 15:56 schrieb Riccardo (Jack) Lucchetti:
>> While we're on the subject: I need to pull a string-valued series from
>> an ODBC source, which is not possible at present (if I'm not
>> mistaken). Shall we leave this for after release?
> As you say, currently not possible. Would definitely be a useful feature!
>
I'm not using ODBC and so I could live with new bugs in that area, but
to me this sounds like it should be done without haste after the
release. You guys are running git versions all the time anyway, so what
does a release mean to you?
Nothing to me personally, but the work I'm doing with the ODBC dataset is
with a colleague whom I'm trying to convince to use gretl for the job
instead of stata. I guess my chances would marginally improve if I didn't
have to ask him to use a snapshot instead of an official release.
-------------------------------------------------------
Riccardo (Jack) Lucchetti
Dipartimento di Scienze Economiche e Sociali (DiSES)
Università Politecnica delle Marche
(formerly known as Università di Ancona)
r.lucchetti(a)univpm.it
http://www2.econ.univpm.it/servizi/hpp/lucchetti
-------------------------------------------------------
5:50 p.m.
Am 28.02.20 um 17:58 schrieb Sven Schreiber:
Am 28.02.2020 um 16:10 schrieb Artur Tarassow:
> Am 28.02.20 um 15:56 schrieb Riccardo (Jack) Lucchetti:
>> While we're on the subject: I need to pull a string-valued series from
>> an ODBC source, which is not possible at present (if I'm not
>> mistaken). Shall we leave this for after release?
> As you say, currently not possible. Would definitely be a useful
> feature!
>
I'm not using ODBC and so I could live with new bugs in that area, but
to me this sounds like it should be done without haste after the
release. You guys are running git versions all the time anyway, so what
does a release mean to you?
Fair enough, Sven. For me this feature does not have to be part of the
next stable release.
Artur
9:06 p.m.
On Fri, 28 Feb 2020, Sven Schreiber wrote:
Am 28.02.2020 um 16:10 schrieb Artur Tarassow:
> Am 28.02.20 um 15:56 schrieb Riccardo (Jack) Lucchetti:
>> While we're on the subject: I need to pull a string-valued series from
>> an ODBC source, which is not possible at present (if I'm not
>> mistaken). Shall we leave this for after release?
> As you say, currently not possible. Would definitely be a useful feature!
I'm not using ODBC and so I could live with new bugs in that area, but
to me this sounds like it should be done without haste after the
release. You guys are running git versions all the time anyway, so what
does a release mean to you?
For me, "Shall we leave this for after release?" means, should we go
ahead with the next release regardless of this wish-list item, or
should we wait until somebody has implemented it? Since I'm quite
likely to be the "somebody" in question, it's meaningful to me even
though I personally am running current git all the time (unless I'm
running a prior version temporarily to check diffs/breakage).
In this instance I'd say let's wait till after release. Yes, it
would be good, but we've accumulated enough bug-fixes to make a
release quite pressing. I've been diddling with dbnomics fixes and
enhancements lately but I'm about done with that and should be ready
for a release early next week.
Allin
9:49 a.m.
Am 28.02.2020 um 22:06 schrieb Allin Cottrell:
On Fri, 28 Feb 2020, Sven Schreiber wrote:
> release. You guys are running git versions all the time anyway,
so what
> does a release mean to you?
the "somebody" in question, it's meaningful to me even
though I
personally am running current git all the time (unless I'm running a
Sure, "you guys" meant Jack and Artur here, sorry.
cheers
sven
7:16 p.m.
On Fri, 28 Feb 2020, Riccardo (Jack) Lucchetti wrote:
While we're on the subject: I need to pull a string-valued series
from an ODBC source, which is not possible at present (if I'm not
mistaken). Shall we leave this for after release?
There's now an initial go at this in git. When the "data" command
identifies a column in a db table that is marked as of one of the
string types (SQL_CHAR, SQL_VARCHAR, SQL_WCHAR, SQL_WVARCHAR) we
import it as a string-valued series.
Please test! I can envisage encoding issues that we might have to
deal with. I can also envisage surprising results if a db table has
been set up wrongly -- numeric data entered, but as strings so far
as SQL is concerned. Up till now we'd automatically convert strings
that represent numeric values to numeric values, but as things stand
in git we'll take the values to be strings if that's what SQL says
they are.
Allin
8:16 a.m.
On Sun, 1 Mar 2020, Allin Cottrell wrote:
On Fri, 28 Feb 2020, Riccardo (Jack) Lucchetti wrote:
> While we're on the subject: I need to pull a string-valued series from an
> ODBC source, which is not possible at present (if I'm not mistaken). Shall
> we leave this for after release?
There's now an initial go at this in git. When the "data" command
identifies
a column in a db table that is marked as of one of the
string types (SQL_CHAR, SQL_VARCHAR, SQL_WCHAR, SQL_WVARCHAR) we import it as
a string-valued series.
Please test! I can envisage encoding issues that we might have to deal with.
I can also envisage surprising results if a db table has been set up wrongly
-- numeric data entered, but as strings so far as SQL is concerned. Up till
now we'd automatically convert strings that represent numeric values to
numeric values, but as things stand in git we'll take the values to be
strings if that's what SQL says they are.
Confirmed working here, thanks a million Allin! :)
Note: some database managers (eg mariaDB/MySQL, which is what I'm using
for testing) provide extra types for strings (eg TEXT), that won't make it
through the ODBC channel. In these cases, some kind of conversion might
help. For example, assuming you have a table called "bar" containing a
TEXT column called "foo", you can trim it to its first 32 characters via
<hansl>
data x query="select left(foo, 32) from bar" --odbc
</hansl>
-------------------------------------------------------
Riccardo (Jack) Lucchetti
Dipartimento di Scienze Economiche e Sociali (DiSES)
Università Politecnica delle Marche
(formerly known as Università di Ancona)
r.lucchetti(a)univpm.it
http://www2.econ.univpm.it/servizi/hpp/lucchetti
-------------------------------------------------------
4:16 p.m.
On Wed, 19 Feb 2020, atecon wrote:
for the first time I am working with gretl's ODBC connector. So
far it seems
to work fine for standard queries using the Exasol data base on linux.
However, I stumbled over to 2 issues:
1) I want to write a open_odbc_connection() function. Unfortunately, the
'open' command cannot be executed within a function. For its standard
application loading a data set I fully understand the reason. However, for
just opening a ODBC connection by the '--odbc" option, I don't get rational
for this restriction.
Feature request: Allow making use of the open command in case the --odbc
option is passed.
OK, that's now in git. We allow within functions "non-destructive"
usage of the "open" command. This includes the --odbc option as well
as "open dbnomics" and "open <db>" where <db> is a native
gretl
database file (e.g. "open fedstl.bin").
2) While loading data through the 'data' command, e.g. by
executing <data x
query="@statement" --odbc>, this command does not seem to work in case one
wants to send a 'statement' -- like "create table" or "drop
table" -- to the
database which does not return a series.
Feature request: allow sending a sql-statement which does not return a series
or anything.
Like Sven, I'm not sure that manipulating a database, as opposed to
simply reading from it, is gretl's business.
Allin
7:52 a.m.
Hi Sven and Allin,
thanks for your responses.
Am 20.02.20 um 17:16 schrieb Allin Cottrell:
On Wed, 19 Feb 2020, atecon wrote:
> for the first time I am working with gretl's ODBC connector. So far it seems
> to work fine for standard queries using the Exasol data base on linux.
> However, I stumbled over to 2 issues:
>
> 1) I want to write a open_odbc_connection() function. Unfortunately, the
> 'open' command cannot be executed within a function. For its standard
> application loading a data set I fully understand the reason. However, for
> just opening a ODBC connection by the '--odbc" option, I don't get
rational
> for this restriction.
> Feature request: Allow making use of the open command in case the --odbc
> option is passed.
OK, that's now in git. We allow within functions "non-destructive"
usage of the "open" command. This includes the --odbc option as well
as "open dbnomics" and "open <db>" where <db> is a native
gretl
database file (e.g. "open fedstl.bin").
Thank you for that -- this is
useful.
One remaining point: Is there a possibility to close a ODBC connection
without closing gretl or executing the "clear" command? The reason is
that institutions/ companies may only support a limit number of open
DB-connections, and thus it's good habit to close the DB-connection once
it is not needed any more. Currently the workaround is: load data from
DB, store it, clear/ close the gretl session, re-open and load again
(the probably large file).
> 2) While loading data through the 'data' command, e.g. by
executing <data x
> query="@statement" --odbc>, this command does not seem to work in case
one
> wants to send a 'statement' -- like "create table" or "drop
table" -- to the
> database which does not return a series.
> Feature request: allow sending a sql-statement which does not return a series
> or anything.
Like Sven, I'm not sure that manipulating a database, as opposed to
simply reading from it, is gretl's business.
Maybe you're right. Of course
writing a wrapper executing some
shell-command using for instance the tool "isql" or executing some
Python-job is doable.
Thank you both,
Artur
8:02 p.m.
On Fri, 21 Feb 2020, Artur Tarassow wrote:
One remaining point: Is there a possibility to close a ODBC
connection without closing gretl or executing the "clear" command?
The reason is that institutions/ companies may only support a
limit number of open DB-connections, and thus it's good habit to
close the DB-connection once it is not needed any more.
Never fear, gretl does not keep an ODBC connection open after a call
to the "data" command. We store the DSN information so we can
re-establish the connection on demand, but we call SQLDisconnect()
after each "data" invocation.
Allin
2073
days inactive
2085
days old
18 comments
5 participants
participants (5)
-
Allin Cottrell -
Artur Tarassow -
atecon
-
Riccardo (Jack) Lucchetti -
Sven Schreiber