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

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 <http://testa.id/> WHERE testa.id <http://testa.id/> = 1
>> ORDER BY testa.id <http://testa.id/>"
>
> On Fri, May 29, 2020 at 8:08 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,
>
> 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
>>>
>>
>