[PATCH] Allow field set from a joined query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[PATCH] Allow field set from a joined query

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

0001-Allow-updates-from-joined-tables.-This-was-already-b.patch (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

The Dude
Expanded this patch some to fix some other parsing issues with joins.

Also available here:


Russell

On Tue, May 26, 2020 at 5:53 PM Russell F <[hidden email]> 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

0002-Fixed-the-way-joins-are-identified-in-the-parser.patch (12K) Download Attachment
0003-Remove-the-single-table-restriction-in-SC_set_SS_col.patch (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Inoue, Hiroshi-2
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:


Russell

On Tue, May 26, 2020 at 5:53 PM Russell F <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

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

The Dude
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);

[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 <[hidden email]> 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:


Russell

On Tue, May 26, 2020 at 5:53 PM Russell F <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

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

Inoue, Hiroshi-2
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);

[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 <[hidden email]> 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:


Russell

On Tue, May 26, 2020 at 5:53 PM Russell F <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

The Dude
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 <[hidden email]> 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);

[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 <[hidden email]> 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:


Russell

On Tue, May 26, 2020 at 5:53 PM Russell F <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Inoue, Hiroshi-2
Hi,

Could you please try the test drivers on Windows 12.02.0100 at ?

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 <[hidden email]> 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);

[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 <[hidden email]> 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:


Russell

On Tue, May 26, 2020 at 5:53 PM Russell F <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

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

The Dude
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 <[hidden email]> wrote:
Hi,

Could you please try the test drivers on Windows 12.02.0100 at ?

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 <[hidden email]> 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);

[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 <[hidden email]> 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:


Russell

On Tue, May 26, 2020 at 5:53 PM Russell F <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

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

Inoue, Hiroshi-2
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 <[hidden email]> wrote:
Hi,

Could you please try the test drivers on Windows 12.02.0100 at ?

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 <[hidden email]> 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);

[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 <[hidden email]> 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:


Russell

On Tue, May 26, 2020 at 5:53 PM Russell F <[hidden email]> 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





Remove-the-single-table-restriction-in-SC_set_SS_col.patch (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

The Dude
That works!  Thanks for all of your help!  

On Mon, Jun 15, 2020 at 8:06 AM Inoue, Hiroshi <[hidden email]> 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 <[hidden email]> wrote:
Hi,

Could you please try the test drivers on Windows 12.02.0100 at ?

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 <[hidden email]> 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);

[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 <[hidden email]> 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:


Russell

On Tue, May 26, 2020 at 5:53 PM Russell F <[hidden email]> 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