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=PostgreSQL3;uid=postgres;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.

[David Henninger]

I am new to postgresql, but not so new to Access. I was having the same problem in a different context, and when I started looking at this, it smelled of Microsoft Access stench. Not so sure anymore, but here goes . . .

I don't know all the details I probably ought to know about your particular situation, but it does sound at least similar to what I was facing. error 3146 from Access when linking a postgresql table with an ostensibly large number of records (in my case, ~775,000). No VB is in use in my case, at least not yet; all I was doing was linking in a table from a postgresql database using Linked Table Manager. Opening the table would stall for about 60 seconds, then give me the stinking 3146 error.

The size of this table turned out to be more of a distraction than anything. The real problem was related to the *width* of the table I was taking in. It is probably a problem inside of either Access/Jet or the ODBC driver, I cannot tell which.

Instead of linking the table, try the following:

Create a new *query* in Access. You'll have to edit SQL directly on this one; there's no other way to do this to accomplish what I had to. Creating a query (as opposed to table linking) gets around Access' tendency to be too-smart-by-half when it refreshes table links.

The query should look thus:

SELECT (fields, but not * !!),
FROM [ODBC;DSN=dsnname;DATABASE=dbname;SERVER=pgsqldbser ver;PORT=5432;CA=d;A6=;A7=100;A8=4096;B0=255;B1=81 90;BI=0;C2=dd_;CX=1c502bb;A1=7.4].[table];

The particular ODBC options you embed here are not particularly important, but they should be borrowed from your ODBC driver's defaults, if you can get at them. You might be able to omit them and do just fine, as long as you include the referential essentials.

The really important part here (at least it was for me!) is to only include those columns from the table you absolutely need. Including all of the available columns (or putting a * for the SELECTed columns)
caused the 3146; if I only included up to a certain number/mass of columns, I could stay under some unknown threshold that would prevent the 3146.

Once you verify that the query works, save it under a convenient name and use this query as your "table link".

If you really need ALL of the columns, you may have to include essential key material in multiple queries, each of which obtains a specific set of columns for you, and inner-join them in parts later.

Hope this helps!


D

[hansv]

I had the same issue. Still today in 2019... there's seems to be no real solution.
I had to add big CLOB-files into a Postgress database using an MS Access-interface. And this was only possible by an odbc-connection, right on the table… But once the number of records raised I got the famous ODBC 3146 error. No way of passing by…
And using a pass-through query didn’t work either because the CLOB-field was to big to use in a SQL-string.
This is how I solved the problem (I know: quite dirty, but it worked).
Create a second table on the Postgress database and call it like TABLE2 (if the original table is called TABLE).
Open this TABLE2 in an odbc-connection.
Check with a pass-through query if what you want to add in TABLE is not yet present.
If not, add it to TABLE 2 with your DAO ODBC-connection (as this table contains no records at all you never will have the ODBC 3146 erro.
Then use a pass through query to copy your record from TABLE2 to TABLE. And use a pass through query to delete the record from TABLE2.
Of course, if you can do it in one pass through query it’s better : insert into TABLE values (1,2,3) WHERE NOT EXIST select * from TABLE WHERE KEY = 1). I needed to do it different because my only pass through query was too big to handle by Access…