Problems with function call

[BenFurino]

I'm using the following code to call a function in a PostgreSQL database. FYI, I've been able to get this running with a competitor's product, but if we get it running with yours, and you run it faster, you'll get a sale. The code:
string eservVideoCommand = "select ev_file from eserv_video('{0}');";
string cmdstring = String.Format(eservVideoCommand, splitFileName.FixedPiece);

PgSqlCommand cmd = new PgSqlCommand(cmdstring, conn);

cmd.CommandType = CommandType.Text;
PgSqlParameter videoId = new PgSqlParameter();
videoId.Value = splitFileName.FixedPiece;
videoId.PgSqlType = PgSqlType.Text;

DataSet ds = new DataSet();
PgSqlDataAdapter da = new PgSqlDataAdapter(cmd);

da.Fill(ds);

In this code, splitFileName.FixedPiece is a string variable. When I run it, I get the following message:
function eserv_video("unknown") does not exist.

Note that when I run using another vendor's ODBC library, the function executes correctly and data is returned.

Thanks in advance for your help.

[BenFurino]

To make this easier to debug, I've dropped the parameters and am using straight sql. The following code still throws an error "function eserv_video("unknown") does not exist


string eservVideoCommand = "select ev_file from eserv_video('{0}');";
string cmdstring = String.Format(eservVideoCommand, "F0105009230A02F5_487");

PgSqlCommand cmd = new PgSqlCommand(cmdstring, conn);

DataSet ds = new DataSet();
PgSqlDataAdapter da = new PgSqlDataAdapter(cmd);

da.Fill(ds);

Can someone please help me with this. Thanks in advance.
Ben

[Shalex]

1. I have tested dotConnect for PostgreSQL v 5.0.69 against PostgreSQL Server v 9.0 using the following code successfully.
DDL

Code: Select all

/*drop table dept cascade;*/
CREATE TABLE public.DEPT (
  DEPTNO INT PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);

/*drop function select_dept(text);*/
CREATE FUNCTION public.select_dept(a text) RETURNS dept AS $$
    SELECT ROW(13, 'mydname', 'myloc')::dept;
$$ LANGUAGE SQL;

C#

Code: Select all

            using (PgSqlConnection conn = new PgSqlConnection()) {
                conn.ConnectionString = "server=db;port=5438;database=postgres;uid=postgres;pwd=postgres;schema=public;";
                conn.Open();
                string eservVideoCommand = "select dname from select_dept('{0}')";
                string cmdstring = String.Format(eservVideoCommand, "F0105009230A02F5_487");

                PgSqlCommand cmd = new PgSqlCommand(cmdstring, conn);

                DataSet ds = new DataSet();
                PgSqlDataAdapter da = new PgSqlDataAdapter(cmd);

                da.Fill(ds);
            }

Please try this sample in your environment. Does it work?
2. Also please give us the following information:
a) the version of your dotConnect for PostgreSQL (x.xx.xxx). You can find it in the Tools > PostgreSQL > About menu of Visual Studio;
b) the version of your PostgreSQL Server;
c) if possible, send us a small test project with the corresponding DDL/DML script to reproduce the problem in our environment.

Tips:

• Make sure that the function exists in the schema you are connecting to.
• Try to debug your application by using the dbMonitor tool that performs per-component tracing of database events such as commit, rollback, SQL statement execute etc.
  Download link: http://www.devart.com/dbmonitor/dbmon3.exe
  Documentation: http://www.devart.com/dotconnect/postgr ... nitor.html