[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

PICCORO LENZ MCKAY reported a new bug.

Summary
-------

ODBC driver problem: driver connects but does not exec query

Type             : Bug
Priority         : High
Gambas version   : 3.9.90 (TRUNK)
Product          : ODBC driver


Description
-----------

The following simple code does not execute, when used a driver different rather than Mysql odbc driver module for ODBC type connections:


       Public Sub Main()
                Dim $con As New Connection
                Try $con.Close()          '
                $con.Type = "odbc"        '
                $con.Host = "testdb"    '
                $con.Login = ""        '
                $con.Password = ""     '
                $con.Open()               '
                Try $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 VARCHAR(20), columna2 VARCHAR(20))")
                Print Error.Text
       End

If the ODBC connection tipe are using a ODBC mysql driver the code will work, but with any other type will fails always, i tested in each gambas version since 3.5, tested with 3.4 and 3.8 and 3.9 and trunk

the connection string are tested, the ODBC definition are globally at the /etc/odbc.ini and its:

[testdb]
Description=Mysql3
Driver=SQLite3
Database=/home/systemas/TEST
# optional lock timeout in milliseconds
Timeout=2000


the connection was tested and working sucessfully with UnixODBC isql command line tool:

isql testdb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)
SQLRowCount returns 0
SQL> select * from tabla1
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| columna1                                                                                                                                                                                                                                                                                                    | columna2                                                                                                                                                                                                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 0
SQL> DROP TABLE tabla1
SQLRowCount returns 0
SQL>

the Gambas code due driver implementation does not return why get and error, in second comment i attached image for gambas ide variables in execution


System information
------------------

[System]
Gambas=3.9.1
OperatingSystem=Linux
Kernel=2.6.38-bpo.2-686
Architecture=x86
Distribution=<hostname> 6.1
Desktop=
Theme=Gtk
Language=es_VE.UTF-8
Memory=2958M

[Libraries]
Cairo=libcairo.so.2.11000.2
Curl=libcurl.so.4.2.0
DBus=libdbus-1.so.3.4.0
GStreamer=libgstreamer-0.10.so.0.27.0
GTK+2=libgtk-x11-2.0.so.0.2000.1
Poppler=libpoppler.so.5.0.0
QT4=libQtCore.so.4.7.4
SDL=libSDL-1.2.so.0.11.4
SQLite=libsqlite3.so.0.8.6

[Environment]
DBUS_SESSION_BUS_ADDRESS=unix:abstract=/tmp/dbus-vBmVAKTmAe,guid=ab802896e9e02a1f9130b5430000020a
DESKTOP_SESSION=openbox
DISPLAY=:0.0
GB_GUI=gb.qt4
GDMSESSION=openbox
GDM_LANG=es_VE.UTF-8
GDM_XSERVER_LOCATION=local
HOME=<home>
LANG=es_VE.UTF-8
LOGNAME=<user>
ORBIT_SOCKETDIR=/tmp/orbit-<user>
PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games
PWD=<home>
SHELL=/bin/bash
SSH_AGENT_PID=3169
SSH_AUTH_SOCK=/tmp/ssh-ufENJB3126/agent.3126
TZ=:/etc/localtime
USER=<user>
USERNAME=<user>
WINDOWPATH=7:7:7:7
XAUTHORITY=<home>/.Xauthority
XDG_DATA_DIRS=/usr/local/share/:/usr/share/:/usr/share/gdm/
XDG_MENU_PREFIX=lxde-
XDG_SESSION_COOKIE=e95ad835c51ba625379e246200000423-1494956351.268990-1110310328



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

PICCORO LENZ MCKAY added an attachment:

error-odbc2.png



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #1 by PICCORO LENZ MCKAY:

now tested better, works only with odbc/postgres using lasted and any odbc/mysql, for any odbc/freetds, previous odbc/postgresql or any odbc/sqlite or any odbc/mdbtools does not work



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #2 by PICCORO LENZ MCKAY:

any progress with this bug? i do not see some work in svn



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #3 by zxMarce:

It is unlikely you will see any: If at least one method works, that kind of proves unixODBC and Gambas work with -at least- these combinations.
Just in case, please enable DEBUG in the ODBC component and try again in the Gambas IDE, dumping the console trace here to see if it is of any help.

This can be (let me stress CAN BE) a low-level driver problem; not all ODBC drivers implement all ODBC calls, and not all RDBMS out there may handle all ODBC commands.

Regards,
zxMarce.



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #4 by PICCORO LENZ MCKAY:

i'll made your suggestions, but please,
pardom me, zxmarce, burt if are a low level driver manager problem why are working in console?
with php are working too!
there are other minor problems that php does not have and gambas yes..

next messager i'll provide the dump



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #5 by zxMarce:

Piccoro,

Just installed SQLite3 on my home laptop. After some twiddling I got the right connection string and the test program I once sent you connected OK.
You're correct that there's something wrong when creating tables in SQLite3, and it does not matter if the "IF NOT EXISTS" clause is present or not.
I'll investigate further on the matter.

zxMarce.



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #6 by zxMarce:

Piccoro,

Found out the statement IS executed, this is, in this case the table IS created if it did not exist.
You may want to change the bug title, which is misleading.

Anyway, for some reason not yet fully known, the component does fail with an internal error when it should not.
Don't know where or why yet though.

zxMarce.



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #7 by zxMarce:

Piccoro,

Got more details. This problem would occur with any statement/query that does not return data.
For example a CREATE TABLE, DROP TABLE, etc. kind of query.

This occurs when the ODBC SQLExecDirect() call returns code SQL_NO_DATA (decimal 100).
My patch attempt only made the problem worse, because now the interpreter SEGFAULTs when I take SQL_NO_DATA into account as non-error value.

But the SEGFAULT occurs well after the query is actually run, same as the error you encountered.
Still digging...

zxMarce.



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #8 by PICCORO LENZ MCKAY:

hi, zxMarce, thanks for take a shot.. i hope u can doit more due i now very busy.. i was thinking.. why odbc to sqlite if there's native sqlite..
well the problem its that must be a standar... odbc makes gambas able to connect to any DBMS source

maybe the odbc sqlite driver does not returns the right data? using mysql-odbc the SELECT statement returns the amount of rows.. the rest of odbc does not return amount of rows... so we have here a controversy.. due maybe we dont know where resides the problem

for now i'll make some "notes" about current state in the new odbc wiki of gambas (i split the odbc DSN explanation respect gambas specific info),

and also now, depend on odbc driver module using, there's no current way to fill a gridview using the Data event with odbc... due mayority of odbc module drivers does not return any info about amount of rows when a SELECT statement are used like mysql-odbc does..



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #9 by zxMarce:

Piccoro,

As I already told you more than once, ODBC will return the row count thanks to the patch I already made given a couple of conditions that depend on the low-level driver being correctly configured.

The conditions are:
A- The driver supports ODBC's SQLFetchScroll() call (this is not a driver config, but a driver feature), and
B- The driver is configured in such a way that it supports the SQL_ATTR_CURSOR_SCROLLABLE flag.

My patch uses condition B to use three times the call in point A in this way:
1- Remember the current row for later getting back to it.
2- Seek up to the first row in the rowset (using SQLFetchScroll)
3- Get the first row's index (firstRecNo)
4- Seek down to the last row in the rowset (using SQLFetchScroll)
5- Get the last row's index (lastRecNo)
6- Seek back to wherever we were at in step 1 (using SQLFetchScroll)
7- Return (lastRecNo - firstRecNo + 1), AKA "Record Count".

For some combinations of driver protocol and MSSQL versions (speaking FreeTDS against MSSQL here), I found out that condition B was not met, so I could not get a record count.
But for some other -documented- protocol and server combinations the call succeeded. The same happens with Firebird, for example.
I never tested it with SQLite3 yet. But I explained this point to you several times now. Will not do it again, and this contaminates this particular bug report.
zxMare.



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #10 by PICCORO LENZ MCKAY:

ok, i understand sorry for the noise, its just that i tested many odbc module drivers and tested that..

in other words, i tested the condition with sqlite and i confirmed does not work..



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #11 by PICCORO LENZ MCKAY:

hi, any progress on this?



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #12 by zxMarce:

I'm on it, already fixed, but the way I fixed it not only is not elegant, but also plain ugly.

I would like to get Benoît's opinion on this as well, because the patch consists in changing a return code from an ODBC subsystem call so the Gambas call does not fail (as I said already, the query is run, but ODBC returns a code that was not being taken into account).

When the query is run by ODBC and returns rows, the return code is SQL_SUCCESS. When the query runs but there's no data to retrieve, the same call responds SQL_NO_DATA. These constants' values are obviously different. The patch simply forces SQL_NO_DATA to be SQL_SUCCESS, but I simply tried a coupla queries, I don't really know if this "solution" covers all cases.

zxMarce.



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #13 by PICCORO LENZ MCKAY:

i think your patch are not so "ugly" due relies on the bad behaviour of the ODBC and SQL standar, i mean umm jajaja its very confusing that the ODBC paper said after a "susessfull sql ddl" return SQL_NO_DATA event SQL_SUCCESS, but with M$ behind scenes.. no surprises..

analizing, if the SQL running was successfully and its no a SQL DML must retrieve as response SQL_SUCCESS, the problem maybe are on that cases:

UPDATE, DELETE and INSERT does not retrieve any rows, only notifies was afected rows.. so return a SQL_NO_DATA, but are DML, so the only case that return data its the SELECT case... so we can assume that any other statement will no return never some data.. only "affected rows" so for any SQL query made, we can assumed SQL_SUCCESS if no problem was happened.. the only exception will be SELECT and for those are not usefully due we not have proper CURSOR, only a FORWARD ONLY cursor...

due that explanation, i think the only you can do its to assume that behaviour of the "ugly patch", so or SQL_SUCCESS or not...


as a informative for others, SQL querys can be divided into two parts: The Data Manipulation Language (DML) querys and the Data Definition Language (DDL) querys

CAUTION: in the stupid mysql and sqlite, the ALTER query has a "afected rows" behavior due some info are stored on tables...

EXAMPLES OF SQL DML:

SELECT – this retriebve data always or not
UPDATE – not retrieve data, only "affected rows"
DELETE – not retrieve data, only "affected rows"
INSERT INTO – not retrieve data, only "affected rows"

EXAMPLES OF SQL DDL:

CREATE DATABASE – no any data, only "succesfull or not"
ALTER DATABASE – no any data, only "succesfull or not"
CREATE TABLE – no any data, only "succesfull or not"
ALTER TABLE – no any data, only "succesfull or not"
DROP TABLE – no any data, only "succesfull or not"
CREATE INDEX – crea un índice.
DROP INDEX – borra un índice.

PICCORO LENZ MCKAY changed the state of the bug to: Accepted.



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

Cristiano Guadagnino
In reply to this post by bugtracker
This is in no way "bad behavior of the ODBC and SQL standard", nor a
problem of Microsoft. It is standard practice (I have encountered it in ALL
the dbms with which I have been working through the years) that when a SQL
query returns no data the engine return a SQLCODE 100.
Negative SQLCODES always mean some kind of error has happened. Positive
SQLCODES are warnings.
SQLCODE 100 is one such warning, and rightly so. It means: your query has
returned no data. It there was no such warning, who could know if the query
returned no data because there's no data to be returned or because, for
example, you lost connectivity mid-way through the query?
So, SQLCODE 100 is a perfectly legal return code and should be treated as
success, at least if the program logic admits that there could be no data
to return.

Cris



On Wed, Jun 21, 2017 at 4:36 AM, <[hidden email]> wrote:

> http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-
>
> Comment #13 by PICCORO LENZ MCKAY:
>
> i think your patch are not so "ugly" due relies on the bad behaviour of
> the ODBC and SQL standar, i mean umm jajaja its very confusing that the
> ODBC paper said after a "susessfull sql ddl" return SQL_NO_DATA event
> SQL_SUCCESS, but with M$ behind scenes.. no surprises..
>
> analizing, if the SQL running was successfully and its no a SQL DML must
> retrieve as response SQL_SUCCESS, the problem maybe are on that cases:
>
> UPDATE, DELETE and INSERT does not retrieve any rows, only notifies was
> afected rows.. so return a SQL_NO_DATA, but are DML, so the only case that
> return data its the SELECT case... so we can assume that any other
> statement will no return never some data.. only "affected rows" so for any
> SQL query made, we can assumed SQL_SUCCESS if no problem was happened.. the
> only exception will be SELECT and for those are not usefully due we not
> have proper CURSOR, only a FORWARD ONLY cursor...
>
> due that explanation, i think the only you can do its to assume that
> behaviour of the "ugly patch", so or SQL_SUCCESS or not...
>
>
> as a informative for others, SQL querys can be divided into two parts: The
> Data Manipulation Language (DML) querys and the Data Definition Language
> (DDL) querys
>
> CAUTION: in the stupid mysql and sqlite, the ALTER query has a "afected
> rows" behavior due some info are stored on tables...
>
> EXAMPLES OF SQL DML:
>
> SELECT – this retriebve data always or not
> UPDATE – not retrieve data, only "affected rows"
> DELETE – not retrieve data, only "affected rows"
> INSERT INTO – not retrieve data, only "affected rows"
>
> EXAMPLES OF SQL DDL:
>
> CREATE DATABASE – no any data, only "succesfull or not"
> ALTER DATABASE – no any data, only "succesfull or not"
> CREATE TABLE – no any data, only "succesfull or not"
> ALTER TABLE – no any data, only "succesfull or not"
> DROP TABLE – no any data, only "succesfull or not"
> CREATE INDEX – crea un índice.
> DROP INDEX – borra un índice.
>
> PICCORO LENZ MCKAY changed the state of the bug to: Accepted.
>
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Gambas-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

PICCORO McKAY Lenz
Cris, the mail was generated from bugtracker, so you must answer here to
better follow up of..

and basically u are agree that the patch its "not so ugly", due acepted
that if no sql negative return was, can be treated as success and the patch
for me are very good way to manage that..

2017-06-21 9:11 GMT-04:30 Cristiano Guadagnino <[hidden email]>:

> This is in no way "bad behavior of the ODBC and SQL standard", nor a
> problem of Microsoft. It is standard practice (I have encountered it in ALL
> the dbms with which I have been working through the years) that when a SQL
> query returns no data the engine return a SQLCODE 100.
> Negative SQLCODES always mean some kind of error has happened. Positive
> SQLCODES are warnings.
> SQLCODE 100 is one such warning, and rightly so. It means: your query has
> returned no data. It there was no such warning, who could know if the query
> returned no data because there's no data to be returned or because, for
> example, you lost connectivity mid-way through the query?
> So, SQLCODE 100 is a perfectly legal return code and should be treated as
> success, at least if the program logic admits that there could be no data
> to return.
>
> Cris
>
>
>
> On Wed, Jun 21, 2017 at 4:36 AM, <[hidden email]> wrote:
>
> > http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-
> >
> > Comment #13 by PICCORO LENZ MCKAY:
> >
> > i think your patch are not so "ugly" due relies on the bad behaviour of
> > the ODBC and SQL standar, i mean umm jajaja its very confusing that the
> > ODBC paper said after a "susessfull sql ddl" return SQL_NO_DATA event
> > SQL_SUCCESS, but with M$ behind scenes.. no surprises..
> >
> > analizing, if the SQL running was successfully and its no a SQL DML must
> > retrieve as response SQL_SUCCESS, the problem maybe are on that cases:
> >
> > UPDATE, DELETE and INSERT does not retrieve any rows, only notifies was
> > afected rows.. so return a SQL_NO_DATA, but are DML, so the only case
> that
> > return data its the SELECT case... so we can assume that any other
> > statement will no return never some data.. only "affected rows" so for
> any
> > SQL query made, we can assumed SQL_SUCCESS if no problem was happened..
> the
> > only exception will be SELECT and for those are not usefully due we not
> > have proper CURSOR, only a FORWARD ONLY cursor...
> >
> > due that explanation, i think the only you can do its to assume that
> > behaviour of the "ugly patch", so or SQL_SUCCESS or not...
> >
> >
> > as a informative for others, SQL querys can be divided into two parts:
> The
> > Data Manipulation Language (DML) querys and the Data Definition Language
> > (DDL) querys
> >
> > CAUTION: in the stupid mysql and sqlite, the ALTER query has a "afected
> > rows" behavior due some info are stored on tables...
> >
> > EXAMPLES OF SQL DML:
> >
> > SELECT – this retriebve data always or not
> > UPDATE – not retrieve data, only "affected rows"
> > DELETE – not retrieve data, only "affected rows"
> > INSERT INTO – not retrieve data, only "affected rows"
> >
> > EXAMPLES OF SQL DDL:
> >
> > CREATE DATABASE – no any data, only "succesfull or not"
> > ALTER DATABASE – no any data, only "succesfull or not"
> > CREATE TABLE – no any data, only "succesfull or not"
> > ALTER TABLE – no any data, only "succesfull or not"
> > DROP TABLE – no any data, only "succesfull or not"
> > CREATE INDEX – crea un índice.
> > DROP INDEX – borra un índice.
> >
> > PICCORO LENZ MCKAY changed the state of the bug to: Accepted.
> >
> >
> >
> > ------------------------------------------------------------
> > ------------------
> > Check out the vibrant tech community on one of the world's most
> > engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> > _______________________________________________
> > Gambas-user mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/gambas-user
> >
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Gambas-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #14 by PICCORO LENZ MCKAY:

hi, how its the progress on this?, gambas 3.10 i think could be released soon i guess/suspect i'm very interesting that of all the reported issues of odbc this about inmediate query could be resolved for next release...



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Gambas Bug Tracker] Bug #1113: ODBC driver problem: driver connects but does not exec query

bugtracker
In reply to this post by bugtracker
http://gambaswiki.org/bugtracker/edit?object=BUG.1113&from=L21haW4-

Comment #15 by PICCORO LENZ MCKAY:

some info from freetds git repo: https://github.com/FreeTDS/freetds/issues/131#issuecomment-313166673
are that info usefully? i cannot understant that freddy77 said!



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Loading...