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

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"

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