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

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(at)dream(dot)email(dot)ne(dot)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(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
>>>
>>
>>
>