Re: [PATCH] Allow field set from a joined query - Mailing list pgsql-odbc

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@dream.email.ne.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@dream.email.ne.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@gmail.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