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

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