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

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>
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
> WHERE testa.id = 1 ORDER BY testa.id"
>
>
> On Fri, May 29, 2020 at 8:08 AM Inoue, Hiroshi <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:
>> [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 WHERE testa.id = 1 ORDER BY 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 WHERE testa.id = 1 ORDER BY testa.id"
>> TO:
>> myQuery = "SELECT testa.* FROM testa INNER JOIN testb ON testb.testaid =
>> testa.id WHERE testa.id = 1 ORDER BY testa.id"
>> OR
>> myQuery = "SELECT testa.* FROM testa LEFT JOIN testb ON testb.testaid =
>> testa.id WHERE testa.id = 1 ORDER BY 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 WHERE testa.id = 1 ORDER BY 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>
>> 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> 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
>>>>
>>>
>>>
>>
>