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

That works! Thanks for all of your help!

On Mon, Jun 15, 2020 at 8:06 AM Inoue, Hiroshi <h-inoue(at)dream(dot)email(dot)ne(dot)jp>
wrote:

> 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>
> 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
>>>>>
>>>>
>>>>
>>>
>>
>