Re: [PATCH] Allow field set from a joined query

Hi,

Sorry for the late reply.

Seems I've misunderstood the problem.
I modified your patch a little.
Could you please try the attached patch?

regards,
Hiroshi Inoue

On 2020/06/06 0:27, Russell F wrote:
> Is this based on the latest from the official repo? If so, then it
> will not fix the issue since it doesn't incorporate the changes that
> fix the multi-table checks. The changes added to the repo where the
> more cosmetic ones, since there do not appear to be any necessary
> inner join checks.
>
> Also, we probably need to redo or remove the SC_has_join/SC_has_X_join
> checks, since they give a false sense that the code can
> actually identify these, when in fact the parser does not
> properly flag joins like "SELECT table_a, table_b", and (maybe) in the
> case of check_join(), "SELECT table_a JOIN table_b" (no INNER, OUTER,
> etc.).
>
> thanks,
> Russell
>
> On Mon, Jun 1, 2020 at 3:08 AM Inoue, Hiroshi
> <h-inoue(at)dream(dot)email(dot)ne(dot)jp <mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp>> wrote:
>
> Hi,
>
> Could you please try the test drivers on Windows 12.02.0100 at
> https://winpg.jp/~inoue/psqlodbc/index.html
> ?
>
> regards,
> Hiroshi Inoue
>
> On 2020/05/29 21:20, Russell F wrote:
>>> myQuery = "SELECT * FROM testa LEFT JOIN testb ON testb.testaid
>>> = testa.id <http://testa.id/> WHERE testa.id
>>> <http://testa.id/> = 1 ORDER BY testa.id <http://testa.id/>"
>>
>> On Fri, May 29, 2020 at 8:08 AM Inoue, Hiroshi
>> <h-inoue(at)dream(dot)email(dot)ne(dot)jp <mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp>> wrote:
>>
>> Hi Russell,
>>
>> On 2020/05/28 22:48, Russell F wrote:
>>> SQL:
>>> DROP TABLE IF EXISTS testa;
>>> DROP TABLE IF EXISTS testb;
>>>
>>> CREATE TABLE testa
>>> (
>>>    id integer PRIMARY KEY,
>>>    at text,
>>>    ai integer
>>> );
>>>
>>> CREATE TABLE testb
>>> (
>>>    id integer PRIMARY KEY,
>>>    bt text,
>>>    bi integer,
>>>    testaid integer REFERENCES testb(id)
>>> );
>>>
>>> INSERT INTO testa VALUES
>>>     (1, 'a', 1),
>>>     (2, 'b', 2);
>>> INSERT INTO testb VALUES
>>>     (1, 'a', 1, 1),
>>>     (2, 'a', 2, 2);
>>>
>>> VB.NET <http://VB.NET>:
>>> [THIS WORKS]
>>>     Sub Test()
>>>         Dim myConnection As New ADODB.Connection
>>>         Dim myRs As New ADODB.Recordset
>>>         Dim myQuery As String
>>>
>>>         On Error GoTo myError
>>>         myConnection.CursorLocation =
>>> ADODB.CursorLocationEnum.adUseClient
>>>         myConnection.ConnectionString = "Driver={PostgreSQL
>>> Unicode};Data
>>> Source=cortex;Server=localhost;Port=5432;Database=cortex;UID=postgres;pwd=postgres;"
>>>         myConnection.Open()
>>
>>>
>>>         myRs.CursorLocation =
>>> ADODB.CursorLocationEnum.adUseClient
>>>
>>>         myQuery = "SELECT testa.* FROM testa JOIN testb ON
>>> testb.testaid = testa.id <http://testa.id> WHERE testa.id
>>> <http://testa.id> = 1 ORDER BY testa.id <http://testa.id>"
>>
>> Hmm, CursorLocation is adUseClient.
>> Are there any cases where the target-list of a query contain
>> fields of more than one table?
>>
>> regards,
>> Hiroshi
>>
>>>
>>>         myRs.Open(myQuery, myConnection,
>>> ADODB.CursorTypeEnum.adOpenKeyset,
>>> ADODB.LockTypeEnum.adLockOptimistic)
>>>
>>>         Dim value = "Modified string_" & Date.UtcNow.Ticks
>>>
>>>         MsgBox(myRs.Fields("at").Value)
>>>
>>>         myRs.Fields("at").Value = value
>>>         myRs.Update()
>>>         MsgBox(myRs("at").Value)
>>>
>>>         myRs.Close()
>>>         myConnection.Close()
>>>
>>>         Exit Sub
>>>
>>> myError:
>>>         MsgBox("Error [" & Err.Number & "][" &
>>> Err.Description & "]")
>>>     End Sub
>>>
>>> CHANGE:
>>> myQuery = "SELECT testa.* FROM testa JOIN testb ON
>>> testb.testaid = testa.id <http://testa.id> WHERE testa.id
>>> <http://testa.id> = 1 ORDER BY testa.id <http://testa.id>"
>>> TO:
>>> myQuery = "SELECT testa.* FROM testa INNER JOIN testb ON
>>> testb.testaid = testa.id <http://testa.id> WHERE testa.id
>>> <http://testa.id> = 1 ORDER BY testa.id <http://testa.id>"
>>> OR
>>> myQuery = "SELECT testa.* FROM testa LEFT JOIN testb ON
>>> testb.testaid = testa.id <http://testa.id> WHERE testa.id
>>> <http://testa.id> = 1 ORDER BY testa.id <http://testa.id>"
>>>
>>> And you get the error:
>>> Multi-step operator generated errors.
>>>
>>> Now after patch 1, this will work, but the following:
>>>
>>> myQuery = "SELECT * FROM testa LEFT JOIN testb ON
>>> testb.testaid = testa.id <http://testa.id> WHERE testa.id
>>> <http://testa.id> = 1 ORDER BY testa.id <http://testa.id>"
>>>
>>> Gives the error: Insufficient key column information for
>>> updating.
>>>
>>> Path 2-3 take care of this.
>>>
>>> Question: If this operation is unsupported, then why did it
>>> work in v7.1? I noticed this by going to the version that
>>> the unix driver is based on, thinking it must be an
>>> operation people use. I myself use Npgsql, but the team
>>> working on another project needs an odbc driver for windows,
>>> and they are porting code from SQL Server. Are there any
>>> other limitations (feature or performance-wise) I should be
>>> concerned about?
>>>
>>> Thanks,
>>> Russell Foster
>>>
>>> On Thu, May 28, 2020 at 7:53 AM Inoue, Hiroshi
>>> <h-inoue(at)dream(dot)email(dot)ne(dot)jp
>>> <mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp>> wrote:
>>>
>>> Hi Russell,
>>>
>>> Thanks.
>>> I would take care of your patches.
>>>
>>> The current driver is not expected to update/delete
>>> result sets
>>> with multiple tables. Please show me the examples.
>>>
>>> regards,
>>> Hiroshi Inoue
>>>
>>> On 2020/05/28 7:31, Russell F wrote:
>>>> Expanded this patch some to fix some other parsing
>>>> issues with joins.
>>>>
>>>> Also available here:
>>>>
>>>> https://github.com/worldleaderpretend/psqlodbc.git
>>>>
>>>> Russell
>>>>
>>>> On Tue, May 26, 2020 at 5:53 PM Russell F
>>>> <russman7474(at)gmail(dot)com <mailto:russman7474(at)gmail(dot)com>>
>>>> wrote:
>>>>
>>>> Hi All,
>>>>
>>>> I made this quick change to allow setting fields
>>>> when a query has an inner or outer join in it. I'm
>>>> not exactly sure why the check disallowed joins
>>>> containing the "X JOIN", such as "LEFT JOIN" or
>>>> "INNER JOIN", but allowed the variant "JOIN".
>>>> Unless I missed something. Maybe an artifact from
>>>> old code?
>>>>
>>>> Also, I added an option in the build to specify a
>>>> postgres installation directory, since some may
>>>> just have the postgres bins built but not installed.
>>>>
>>>> thanks,
>>>> Russell
>>>>
>>>
>>
>