odbc call failed

[sayamishiraj]

i am using vb 6 and postgresql as the back end. when running the function in the vb , it shows the error message of odbc call failed. but when i run the same function in the postgresql then it runs easily. can anyone suggest how can i increase the execution time in the odbc since the database is too huge,

[michaelb]

There's not enough information here to understand the nature of your problem.
Please post the relevant excerpt from your code and the error message it generates.

[coolminded]

i am using the function

 
CREATE OR REPLACE FUNCTION fxn_name("varchar", "varchar", "varchar")
  RETURNS "varchar" AS
$BODY$
        DECLARE
          i RECORD;
        bal numeric(12,2);
        BEGIN
          INSERT INTO table1
        VALUES(
          1,
          current_date,
          current_date,
          current_date,
                  current_date,
          $3,
          'narration',
          '',
          current_date,
          $2,
          '',
          'N',
          'J');
        FOR i IN SELECT sth FROM table2  WHERE level_no = 3                             
        LOOP
        IF bal > 0 THEN
        INSERT INTO table3
        VALUES(
          1,
          current_date,
          i.acct_cd,
          'narration',
          $2,
          'D',
          '',
          bal);
                    ELSE
         IF bal <0 THEN
                 INSERT INTO table3
        VALUES(
          1,
          current_date,
          i.acct_cd,
          'narration',
          $2,
          'C',
          '',
          bal*-1);
        ELSE
        END IF;
        END IF;
        END LOOP;
        RETURN 'a';
        END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
 

when i call this function in vb it shows the error message of
run time error '3146', odbc call failed since it has huge database
but when i run the same code in the postgres it executes quite easily

is there any suggestions of this problem???

[michaelb]

I don't use neither VB or ODBC, but a Google searh on "run time error '3146', odbc call failed"
returned quite a few references.
It appears this problem may be related to your ODBC driver, did you check if a newer version is available?
Some other cases I saw were related to query not returning the number of rows affected, apparently the driver was expecting this.

I would consider these steps:

1. I think you were concerned with performance and possible timeout error due to the long running query.
Create some test function similar to yours, but make sure it does little and runs fast.
See if you can execute it from your app.

2. Modify this function to return a number of affected rows, perhaps some dummy integer.

3. Instead of the function, make your app. to run SQL query performing a similar operation, see what happens.

4. Check for updates on the ODBC driver you are using, or maybe find a different driver altogether.

5. Spend some time searching net with Google

Here are couple links that could be relevant.

http://www.pcreview.co.uk/forums/thread-1865044.php
http://www.vbforums.com/archive/index.php/t-338252.html

[coolminded]

I don't use neither VB or ODBC, but a Google searh on "run time error '3146', odbc call failed"
returned quite a few references.
It appears this problem may be related to your ODBC driver, did you check if a newer version is available?
Some other cases I saw were related to query not returning the number of rows affected, apparently the driver was expecting this.

I would consider these steps:

1. I think you were concerned with performance and possible timeout error due to the long running query.
Create some test function similar to yours, but make sure it does little and runs fast.
See if you can execute it from your app.

2. Modify this function to return a number of affected rows, perhaps some dummy integer.

3. Instead of the function, make your app. to run SQL query performing a similar operation, see what happens.

4. Check for updates on the ODBC driver you are using, or maybe find a different driver altogether.

5. Spend some time searching net with Google

Here are couple links that could be relevant.

http://www.pcreview.co.uk/forums/thread-1865044.php
http://www.vbforums.com/archive/index.php/t-338252.html

thanx for the suggestion
i just run the sql query in the application, but another error (run time error '3669' execution canceled) arises.
if the data base is small, the function runs quite easily,
plz help
thanx in advance

[michaelb]

From what you've said it appears that problem is not related to the function, but has to do with a long running query.
I think you need to look at two areas:

1. Increase a connection timeout.
You may be able to set a connection timeout either in the definition of the ODBC
datasource, or in the connection string in your application.

2. Optimize database and/or your code so your queries will run faster.

Do you have an idea how long this query runs when executed directly in the database?

[sayamishiraj]

From what you've said it appears that problem is not related to the function, but has to do with a long running query.
I think you need to look at two areas:

1. Increase a connection timeout.
You may be able to set a connection timeout either in the definition of the ODBC
datasource, or in the connection string in your application.

2. Optimize database and/or your code so your queries will run faster.

Do you have an idea how long this query runs when executed directly in the database?

once again thanx for the suggestion
but i still have some problem

i use the following syntax

Public Sub getconnect()
Set ws = CreateWorkspace ("", "abc", "", dbUseODBC)
Set dbaccount = ws.OpenDatabase ("", , False, "ODBC;dsn=Postg reSQL3;uid=post gres;pwd=''")

End Sub

where should i set the connection timeout in the connection string and i didn't see any place in the odbc data source to increase the connection timeout.

and the time to run the query directly in the data base is nearly 8 minutes

thanx in advance

[michaelb]

You need to read the documentation on your ODBC driver to find out how to tune the timeout. You can probably get some help if you post this question on a VB forum, or you can browse Microsoft web-site. Start at http://support.microsoft.com/ and search the Knowledge Base.

All this may be helpful, but considering that your query takes about 8 minutes I would really take a hard look at performance... meaning database schema, indexes, the query itself etc.
This may take some time, because a bad performance can be caused by a number of things.
But you have to start somewhere, so let's do this:

- Print the table definition (along with indexes) on all tables involved in the query.
- If you have triggers, or rules ON INSERT or ON UPDATE this can matter as well.
- Open the psql session and run "explain analyze <query>"
where <query> is the actual SQL in question.

This will give you some helpful statistics on execution plan and time.

What version of Postgres are you using?
What OS is it running on?
Do you ever run "vacuum analyze" on the database?

[coolminded]

You need to read the documentation on your ODBC driver to find out how to tune the timeout. You can probably get some help if you post this question on a VB forum, or you can browse Microsoft web-site. Start at http://support.microsoft.com/ and search the Knowledge Base.

All this may be helpful, but considering that your query takes about 8 minutes I would really take a hard look at performance... meaning database schema, indexes, the query itself etc.
This may take some time, because a bad performance can be caused by a number of things.
But you have to start somewhere, so let's do this:

- Print the table definition (along with indexes) on all tables involved in the query.
- If you have triggers, or rules ON INSERT or ON UPDATE this can matter as well.
- Open the psql session and run "explain analyze <query>"
where <query> is the actual SQL in question.

This will give you some helpful statistics on execution plan and time.

What version of Postgres are you using?
What OS is it running on?
Do you ever run "vacuum analyze" on the database?

thanx for the suggestion once again.
by the way
1. i am using the postgres 8.0.0-rc1
2.it is the Win XP with P III
3. i do use quite frequently the vacuum analyze on the database.
thanx

[michaelb]

Perhaps it makes sense to review your configuration settings,
check out this doc
I would also use the Windows Task Manager to check on memory and CPU consumption while this long query is running.

But reviewing the database schema and indexes, and also query optimization,
as I suggested earlier, should be still at the top of your list.