Re: Bug? {? = CALL insert_page_segment (?, ?)} - Mailing list pgsql-odbc

Hi Nils,

On 2014/11/12 2:08, Nils Gösche wrote:
> Hi!
>
> I have a problem with newer versions of the ODBC driver. I made a small
> testing example to reproduce the problem:
>
> The following code works fine with version 09.02.0100 of the driver. With
> 09.03.0210, it crashes. With 09.03.0400, I get a strange exception and error
> message.

Please try to add ;parse=1 to your connection string or turn on
the *Parse statements* option of your DSN.

regards,
Hiroshi Inoue

> This is all on Windows, 32-Bit. I tried Windows 7 and Windows 8.1; I also
> tried Postgres versions 9.2.4, 9.2.9, 9.3.4 and 9.3.5, all 32-Bit.
>
> Here is the create script for the database:
>
> CREATE TABLE page_segments (
>     task_id uuid,
>     id uuid,
>     PRIMARY KEY (task_id, id)
> );
>
> CREATE FUNCTION insert_page_segment(theTaskId uuid, theId uuid) RETURNS int
> AS $$
> DECLARE
>     ret int NOT NULL := 0;
> BEGIN
>     BEGIN
>         INSERT INTO page_segments (task_id, id) VALUES (theTaskId,
> theId);
>         ret := 1;
>     EXCEPTION WHEN unique_violation THEN
>         -- ignore
>     END;
>     RETURN ret;
> END
> $$ LANGUAGE plpgsql;
>
> And here is some C# code that calls the insert_page_segment function:
>
> using System;
> using System.Collections.Generic;
> using System.Linq;
> using System.Text;
> using System.Threading.Tasks;
> using System.Data;
> using System.Data.Odbc;
>
> namespace ODBCTest
> {
>      class Program
>      {
>          const string ConnString = @"Driver={PostgreSQL
> Unicode};server=localhost;port=5432;database=stringtest;uid=cartan;pwd=...";
>          static void Main(string[] args)
>          {
>              using (var conn = new OdbcConnection(ConnString))
>              {
>                  conn.Open();
>                  Guid g1 = Guid.NewGuid();
>                  Guid g2 = Guid.NewGuid();
>                  Console.WriteLine("First call returns {0}",
> CallTheFunc(conn, g1, g2));
>              }
>              Console.WriteLine("Press any key to exit...");
>              Console.ReadKey(true);
>          }
>
>          static int CallTheFunc(OdbcConnection conn, Guid taskId, Guid id)
>          {
>              using (var trans = conn.BeginTransaction())
>              {
>                  try
>                  {
>                      const string insertCmdText = @"{? = CALL
> insert_page_segment (?, ?)}";
>                      using (var insertCmd = new OdbcCommand(insertCmdText,
> conn, trans))
>                      {
>                          var retParam = new OdbcParameter();
>                          retParam.OdbcType = OdbcType.Int;
>                          retParam.Direction = ParameterDirection.ReturnValue;
>                          insertCmd.Parameters.Add(retParam);
>
>                          var taskIdParam = new OdbcParameter();
>                          taskIdParam.OdbcType = OdbcType.UniqueIdentifier;
>                          taskIdParam.Value = taskId;
>                          insertCmd.Parameters.Add(taskIdParam);
>
>                          var idParam = new OdbcParameter();
>                          idParam.OdbcType = OdbcType.UniqueIdentifier;
>                          idParam.Value = id;
>                          insertCmd.Parameters.Add(idParam);
>
>                          insertCmd.ExecuteNonQuery();
>                          int ret = (int) retParam.Value;
>
>                          trans.Commit();
>                          return ret;
>                      }
>                  }
>                  catch
>                  {
>                      trans.Rollback();
>                      throw;
>                  }
>              }
>          }
>      }
> }
>
> With the old 9.2.1 driver, the function just returns 1 as expected. With
> 9.3.4, I get an unusual InvalidOperationException in ExecuteNonQuery(),
> saying
>
>    "This OdbcTransaction has completed; it is no longer usable."
>
> In the Postgres log file, I find this message:
>
> 2014-11-11 17:26:04 CET ERROR:  function insert_page_segment(unknown) does
> not exist at character 8
> 2014-11-11 17:26:04 CET HINT:  No function matches the given name and
> argument types. You might need to add explicit type casts.
> 2014-11-11 17:26:04 CET STATEMENT:  SELECT insert_page_segment ($1, $2)
> 2014-11-11 17:26:04 CET FATAL:  invalid frontend message type 0
>
> Can anybody help me with this?
>
> Regards,
> --
> Nils Gösche
> Don’t ask for whom the <Ctrl-G> tolls.