PC SOFT

FORUMS PROFESSIONNELS
WINDEVWEBDEV et WINDEV Mobile

Accueil → WEBDEV 2024 → Getting number or rows in MySQL SELECT Query
Getting number or rows in MySQL SELECT Query
Débuté par Luis García, 01 sep. 2017 17:20 - 3 réponses
Membre enregistré
11 messages
Posté le 01 septembre 2017 - 17:20
Hi. I'm trying to get the number of rows returned by a MySQL query. This is my code:

IF connectToDatabase() = 0 THEN
StringDisplay("Error en conexión a Base de Datos:" + CR + oConn.cSetGetErrorMessage)
END

cSelectQuery is string
cSelectQuery = "SELECT nSessionId FROM tblSessions ORDER BY nSessionId DESC LIMIT 1"
lSqlResult is boolean = SQLExec(cSelectQuery,"cSelect")

SQLInfo()
nResults is int = SQL.NbLine
Info("Nº de registros encontrados: " + nResults)


The problem is that nResults is always 0, while my database has actually some records stored in it. What am I doing wrong?

Thank you very much.

EDIT: I should add that I don't want to make a SELECT COUNT, if possible...
Message modifié, 01 septembre 2017 - 17:26
Posté le 01 septembre 2017 - 20:11
Hi

use hexecutequery instead of SQLExec, and in that case, after the
execution of the query, the number of rows is available with the hnbenr
function


Best regards

--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Ready for you: WXShowroom.com, WXReplication (open source) and now WXEDM
(open source)

More information on http://www.fabriceharari.com

Le 9/1/2017 à 9:20 AM, "ÿÿÿÿÿÿÿÿÿ" a écrit :
Hi. I'm trying to get the number of rows returned by a MySQL query. This
is my code:

IF connectToDatabase() = 0 THEN
StringDisplay("Error en conexión a Base de Datos:" + CR +
oConn.cSetGetErrorMessage)
END

cSelectQuery is string cSelectQuery = "SELECT nSessionId FROM
tblSessions ORDER BY nSessionId DESC LIMIT 1"
lSqlResult is boolean = SQLExec(cSelectQuery,"cSelect")

SQLInfo()
nResults is int = SQL.NbLine
Info("Nº de registros encontrados: " + nResults)


The problem is that nResults is always 0, while my database has actually
some records stored in it. What am I doing wrong?

Thank you very much.

EDIT: I should add that I don't want to make a SELECT COUNT, if possible...
Membre enregistré
11 messages
Posté le 04 septembre 2017 - 10:05
Hi Fabrice,

Thanks again for your help.

Following your advice, I've modified my code, so it is like this:

cSelectQuery is string
cSelectQuery = "SELECT nSessionId FROM tblSessions ORDER BY nSessionId DESC LIMIT 1"

HExecuteQuery(cSelectQuery, hQueryDefault)
Info (HNbEnr(cSelectQuery))


And now, the following error is displayed:

The  data source is not initialized. 
- If it is a data file, the file was not found in the  analysis or it was not described by HDeclare/HDeclareExternal.
- If it is a query or a view, the execution may have failed. 
To retrieve the corresponding error, check the result of HExecuteQuery/HExecuteSQLQuery/HCreateView.


On the other hand, why should SQLExec() + SQLInfo() + SQL.NbLine not be working? I took this from the official documentation (https://help.windev.com/en-US/…) , and it seems really simple to use.

Thank you.
Posté le 04 septembre 2017 - 13:03
Hi,

1. You are not testing the result of hexecutequery, and therefore, as it
seems that your query is incorrect, you are getting an error message
later on. See help for how to test the result.

2. I don't know about sqlexec and such, as these are very old (and
nearly forgotten) functions that are used ONLY in very specific cases
these days

3. So, once you added the testing of the result of hexecutequery and the
display if the error message, if it happens that the query is incorrect,
you can try to use the hqueryWithoutCorrection instead of the
hquerydefault, in case your query is correct for the target DB, but not
part of the number of queries accepted by the HF engine.

Best regards

--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Ready for you: WXShowroom.com, WXReplication (open source) and now WXEDM
(open source)

More information on http://www.fabriceharari.com



Le 9/4/2017 à 2:05 AM, "ÿÿÿÿÿÿÿÿÿ" a écrit :
Hi Fabrice,

Thanks again for your help.

Following your advice, I've modified my code, so it is like this:

cSelectQuery is string cSelectQuery = "SELECT nSessionId FROM
tblSessions ORDER BY nSessionId DESC LIMIT 1"

HExecuteQuery(cSelectQuery, hQueryDefault)
Info (HNbEnr(cSelectQuery))


And now, the following error is displayed:

The <SELECT nSessionId FROM tblSessions ORDER BY nSessionId DESC LIMIT
1> data source is not initialized. - If it is a data file, the file was
not found in the <D:\My Sites\SessionsTest\dbSessions.wdd> analysis or
it was not described by HDeclare/HDeclareExternal.
- If it is a query or a view, the execution may have failed. To retrieve
the corresponding error, check the result of
HExecuteQuery/HExecuteSQLQuery/HCreateView.


On the other hand, why should SQLExec() + SQLInfo() + SQL.NbLine not be
working? I took this from the official documentation
(https://help.windev.com/en-US/…) , and it seems
really simple to use.

Thank you.