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

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 <http://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 <http://testa.id> WHERE testa.id
> <http://testa.id> = 1 ORDER BY testa.id <http://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 <http://testa.id> WHERE testa.id <http://testa.id> = 1 ORDER
> BY testa.id <http://testa.id>"
> TO:
> myQuery = "SELECT testa.* FROM testa INNER JOIN testb ON testb.testaid
> = testa.id <http://testa.id> WHERE testa.id <http://testa.id> = 1
> ORDER BY testa.id <http://testa.id>"
> OR
> myQuery = "SELECT testa.* FROM testa LEFT JOIN testb ON testb.testaid
> = testa.id <http://testa.id> WHERE testa.id <http://testa.id> = 1
> ORDER BY testa.id <http://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 <http://testa.id> WHERE testa.id <http://testa.id> = 1 ORDER
> BY testa.id <http://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 <mailto: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
>> <mailto: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
>>
>