Thread: get number of rows selected
Hi,
I dont know much about databases, and I have a question
I've just installed PostgreSLQ.
Now I would like to get the number of rows selected via a ODBC SQL statement in C++. I would like to know the amount before I do Fetch.
This is a little example of what I do in c++:
selStr="select * from layer where visible = 1");
rc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &StmtHandle);
if(rc == SQL_SUCCESS)
{
SQLSetStmtOption(StmtHandle, SQL_CONCURRENCY, SQL_CONCUR_READ_ONLY);
SQLSetStmtOption(StmtHandle, SQL_CURSOR_TYPE, SQL_CURSOR_KEYSET_DRIVEN);
SQLSetStmtOption(StmtHandle, SQL_ROWSET_SIZE, m_rows);
rc = SQLExecDirect(StmtHandle, (SQLCHAR *)selStr, SQL_NTS);
...
...
...
How do I know how many rows that was found??
Kind regards,
Paul Malm
Hey,
I have a problem with connection of postgres.
I am using postgres 8.03.01.
I have a telecom program which have 248 channels and each channel hold a connection to postgres database, mean i have
248opening connections.
When a channel is run, it means it begin to excute some queries to postgres, i got the error : SQL Error State:
08S01
Native Error Code: 1A
ODBC Error: Could not send
Query(connection dead);
Could not send Query(connection dead)
Could you help me on this ?
Thanks you very much
Lam
This email may contain material that is confidential, privileged, and/or attorney work product for the sole use of the
intendedrecipient. Any review, reliance, or distribution by others or forwarding without express permission is
strictlyprohibited. If you are not the intended recipient, please contact the sender and delete all copies.
Generally you would do that with a count function instead of relying on something else. "select * from layer where visible = 1" so I guess "SELECT COUNT(*) FROM layer WHERE visible=1" would give you the answer. - Adam
Hello, Does it happen after some idle period? Nguyen, Lam Thuy wrote: > Hey, > > I have a problem with connection of postgres. > I am using postgres 8.03.01. > > I have a telecom program which have 248 channels and each channel hold a connection to postgres database, mean i have 248opening connections. > > When a channel is run, it means it begin to excute some queries to postgres, i got the error : SQL Error State: 08S01 > > Native Error Code: 1A > > ODBC Error: Could not send > Query(connection dead); > > Could not send Query(connection dead) > > > Could you help me on this ? > > > Thanks you very much > Lam > > > > > > > This email may contain material that is confidential, privileged, and/or attorney work product for the sole use of theintended recipient. Any review, reliance, or distribution by others or forwarding without express permission is strictlyprohibited. If you are not the intended recipient, please contact the sender and delete all copies. > > -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
yes, it is right. ________________________________ From: Andrei Kovalevski [mailto:andyk@commandprompt.com] Sent: Sun 4/6/2008 9:35 PM To: Nguyen, Lam Thuy Cc: Malm Paul; pgsql-odbc@postgresql.org Subject: Re: [ODBC] Connection of postgres Hello, Does it happen after some idle period? Nguyen, Lam Thuy wrote: > Hey, > > I have a problem with connection of postgres. > I am using postgres 8.03.01. > > I have a telecom program which have 248 channels and each channel hold a connection to postgres database, mean i have 248opening connections. > > When a channel is run, it means it begin to excute some queries to postgres, i got the error : SQL Error State: 08S01 > > Native Error Code: 1A > > ODBC Error: Could not send > Query(connection dead); > > Could not send Query(connection dead) > > > Could you help me on this ? > > > Thanks you very much > Lam > > > > > > > This email may contain material that is confidential, privileged, and/or attorney work product for the sole use of theintended recipient. Any review, reliance, or distribution by others or forwarding without express permission is strictlyprohibited. If you are not the intended recipient, please contact the sender and delete all copies. > > -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ This email may contain material that is confidential, privileged, and/or attorney work product for the sole use of the intendedrecipient. Any review, reliance, or distribution by others or forwarding without express permission is strictlyprohibited. If you are not the intended recipient, please contact the sender and delete all copies.
Hello,
Take a look at SQLRowCount( http://msdn2.microsoft.com/en-us/library//ms711835.aspx ). You may note:
For other statements and functions, the driver may define the value returned in *RowCountPtr. For example, some data sources may be able to return the number of rows returned by a SELECT statement or a catalog function before fetching the rows.
You may rely on this when using psqlODBC or ODBCng with buffering mode ON (default).
Adam M wrote:
Take a look at SQLRowCount( http://msdn2.microsoft.com/en-us/library//ms711835.aspx ). You may note:
For other statements and functions, the driver may define the value returned in *RowCountPtr. For example, some data sources may be able to return the number of rows returned by a SELECT statement or a catalog function before fetching the rows.
You may rely on this when using psqlODBC or ODBCng with buffering mode ON (default).
Adam M wrote:
Generally you would do that with a count function instead of relying on something else. "select * from layer where visible = 1" so I guess "SELECT COUNT(*) FROM layer WHERE visible=1" would give you the answer. - Adam
-- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
I'm figuring similar behaviour with Linux PostgreSQL server and Win32 client, connection over Internet (not LAN). Not sure what is the source of this problem but it could be something network relative.
Nguyen, Lam Thuy wrote:
Nguyen, Lam Thuy wrote:
yes, it is right. ________________________________ From: Andrei Kovalevski [mailto:andyk@commandprompt.com] Sent: Sun 4/6/2008 9:35 PM To: Nguyen, Lam Thuy Cc: Malm Paul; pgsql-odbc@postgresql.org Subject: Re: [ODBC] Connection of postgres Hello, Does it happen after some idle period? Nguyen, Lam Thuy wrote:Hey, I have a problem with connection of postgres. I am using postgres 8.03.01. I have a telecom program which have 248 channels and each channel hold a connection to postgres database, mean i have 248 opening connections. When a channel is run, it means it begin to excute some queries to postgres, i got the error : SQL Error State: 08S01 Native Error Code: 1A ODBC Error: Could not send Query(connection dead); Could not send Query(connection dead) Could you help me on this ? Thanks you very much Lam This email may contain material that is confidential, privileged, and/or attorney work product for the sole use of the intended recipient. Any review, reliance, or distribution by others or forwarding without express permission is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies.-- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ This email may contain material that is confidential, privileged, and/or attorney work product for the sole use of the intended recipient. Any review, reliance, or distribution by others or forwarding without express permission is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies.
-- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Kovalevski ,
I am sure there was no prolem with network. I guess maybe postgres database can not handle if there are many
connectionswork concurrenly.
Lam
________________________________
From: Andrei Kovalevski [mailto:andyk@commandprompt.com]
Sent: Sun 4/6/2008 10:26 PM
To: Nguyen, Lam Thuy
Cc: Malm Paul; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Connection of postgres
I'm figuring similar behaviour with Linux PostgreSQL server and Win32 client, connection over Internet (not LAN). Not
surewhat is the source of this problem but it could be something network relative.
Nguyen, Lam Thuy wrote:
yes, it is right.
________________________________
From: Andrei Kovalevski [mailto:andyk@commandprompt.com]
Sent: Sun 4/6/2008 9:35 PM
To: Nguyen, Lam Thuy
Cc: Malm Paul; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Connection of postgres
Hello,
Does it happen after some idle period?
Nguyen, Lam Thuy wrote:
Hey,
I have a problem with connection of postgres.
I am using postgres 8.03.01.
I have a telecom program which have 248 channels and each channel hold a connection to postgres database, mean
ihave 248 opening connections.
When a channel is run, it means it begin to excute some queries to postgres, i got the error : SQL Error
State: 08S01
Native Error Code: 1A
ODBC Error: Could not send
Query(connection dead);
Could not send Query(connection dead)
Could you help me on this ?
Thanks you very much
Lam
This email may contain material that is confidential, privileged, and/or attorney work product for the sole use
ofthe intended recipient. Any review, reliance, or distribution by others or forwarding without express permission is
strictlyprohibited. If you are not the intended recipient, please contact the sender and delete all copies.
--
Andrei Kovalevski
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
This email may contain material that is confidential, privileged, and/or attorney work product for the sole use of
theintended recipient. Any review, reliance, or distribution by others or forwarding without express permission is
strictlyprohibited. If you are not the intended recipient, please contact the sender and delete all copies.
--
Andrei Kovalevski
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
This email may contain material that is confidential, privileged, and/or attorney work product for the sole use of the
intendedrecipient. Any review, reliance, or distribution by others or forwarding without express permission is
strictlyprohibited. If you are not the intended recipient, please contact the sender and delete all copies.
On 07/04/2008 03:40, Nguyen, Lam Thuy wrote: > I am sure there was no prolem with network. I guess maybe postgres > database can not handle if there are many connections work > concurrenly. That's not so - if you've configured it correctly, if your hardware can handle it, PostgreSQL scales *very* well. What have you got max_connections set to in postgresql.conf? Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
I set it up to 300. Do you have any solution for me? Lam ________________________________ From: Raymond O'Donnell [mailto:rod@iol.ie] Sent: Mon 4/7/2008 11:05 AM To: Nguyen, Lam Thuy Cc: Andrei Kovalevski; Malm Paul; pgsql-odbc@postgresql.org Subject: Re: [ODBC] Connection of postgres On 07/04/2008 03:40, Nguyen, Lam Thuy wrote: > I am sure there was no prolem with network. I guess maybe postgres > database can not handle if there are many connections work > concurrenly. That's not so - if you've configured it correctly, if your hardware can handle it, PostgreSQL scales *very* well. What have you got max_connections set to in postgresql.conf? Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------ This email may contain material that is confidential, privileged, and/or attorney work product for the sole use of the intendedrecipient. Any review, reliance, or distribution by others or forwarding without express permission is strictlyprohibited. If you are not the intended recipient, please contact the sender and delete all copies.
I've had a similar "problem". The connection times out and get closed after a few hours. The solution was to do a query to keep the database alive, for example "SELECT 1" and that's all. That is done every few minutes. The real test is to open 250 connections, and issue queries on each without idle time. Does connection drop occur on active connections?
i open connection, and leave it for few hour, and then excute query, and get error. ________________________________ From: pgsql-odbc-owner@postgresql.org on behalf of Adam M Sent: Wed 4/9/2008 2:40 PM Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Connection of postgres I've had a similar "problem". The connection times out and get closed after a few hours. The solution was to do a query to keep the database alive, for example "SELECT 1" and that's all. That is done every few minutes. The real test is to open 250 connections, and issue queries on each without idle time. Does connection drop occur on active connections? -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc This email may contain material that is confidential, privileged, and/or attorney work product for the sole use of the intendedrecipient. Any review, reliance, or distribution by others or forwarding without express permission is strictlyprohibited. If you are not the intended recipient, please contact the sender and delete all copies.
paul malm wrote:
>
>
> Hi,
> I dont know much about databases, and I have a question
>
> I've just installed PostgreSLQ.
> Now I would like to get the number of rows selected via a ODBC SQL
> statement in C++. I would like to know the amount before I do Fetch.
>
> This is a little example of what I do in c++:
>
> selStr="select * from layer where visible = 1");
> rc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &StmtHandle);
> if(rc == SQL_SUCCESS)
> {
> SQLSetStmtOption(StmtHandle, SQL_CONCURRENCY,
> SQL_CONCUR_READ_ONLY);
> SQLSetStmtOption(StmtHandle, SQL_CURSOR_TYPE,
> SQL_CURSOR_KEYSET_DRIVEN);
> SQLSetStmtOption(StmtHandle, SQL_ROWSET_SIZE, m_rows);
> rc = SQLExecDirect(StmtHandle, (SQLCHAR *)selStr,
> SQL_NTS);
> ...
> ...
> ...
> How do I know how many rows that was found??
>
>
> Kind regards,
>
> Paul Malm
>
>
>
Use SQLRowCount ODBC function
--
View this message in context: http://old.nabble.com/get-number-of-rows-selected-tp16475169p27851040.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.