PC SOFT

FORUMS PROFESSIONNELS
WINDEV, WEBDEV et WINDEV Mobile

|
|
|
FR
Accueil → WINDEV 2024 → Not able to execute SQL statements after opening a connection
Not able to execute SQL statements after opening a connection
Débuté par guest, 18 déc. 2017 12:31 - 12 réponses
#1
Posté le 18 décembre 2017 - 12:31
We have got an HFSQL server where we created a small database with couple of tables.

IP: 192.168.xx.xx
Port :4900
Database :MyDB
username :admin
password:admin
I need to create an application which executes custom SQL queries on this database. We don't want to create any analysis because the database can be configured.

We opened a connection from the windev using the following code block :

cntDatabase is Connection
cntDatabase..Provider = hAccessHFClientServer
cntDatabase..Server = IP + ":" + Port
cntDatabase..Database = MyDB
cntDatabase..User = username
cntDatabase..Password =password
// Connection to the database
IF HOpenConnection(cntDatabase) = False THEN
Error(HErrorInfo())
RETURN
END


We are able to open the connection as HOpenConnection returns true.

Following the opening of the connection we are not able to execute the SQL statements .


IF HExecuteSQLQuery("SQLResult",cntDatabase,hQueryWithoutHFCorrection,"SELECT * from Persons" ) = False THEN
Error(HErrorInfo())
RETURN
ELSE
SQLInfo("QUERY" )
END


We are getting the following error :

Error returned by <192.168.xx.xx :4900> server:
No analysis opened: <Persons> file not described.

We are not sure what exactly is the problem. We are just trying to query a HFSQL database remotely using the windev application without analysis being used.

Not sure whether there are more settings or whether we are doing something wrong.

It would be helpful if someone can share some information on this
Signaler
0
0
#2
Posté le 18 décembre 2017 - 12:41
Did you try with hQueryWithoutCorrection instead of hQueryWithoutHFCorrection?
Signaler
0
0
#3
Posté le 18 décembre 2017 - 12:48
Thanks for replying.
Yes, we even tried with hQueryWithoutCorrection but it gives the same error.

Our requirement is to develop a tool which can read a file which has multiple sql statements and run it against an HFSQL database which is in another machine.
So there cannot be any defined analysis set for this. The database can vary, the server can vary.

We are able to connect to the database server using the HOpenConnection but even the simple select queries are not getting executed using any of the HExecute statements.

Not sure whether this requirement is possible or are we missing something
Signaler
0
0
#4
Posté le 18 décembre 2017 - 12:52
Hello Rahulmr

Are you sure that "Persons" table have been created before in your db??

HTH

Gianni
Signaler
0
0
#5
Posté le 18 décembre 2017 - 12:54
hey,

Yes, the persons table is there and from the HFSQL Control Center we are able query against this table using the New Query option
Signaler
0
0
#6
Posté le 18 décembre 2017 - 13:32
If you can't do it with hQueryWithoutCorrection / hQueryWithoutHFCorrection you can use HDeclareExternal before your query and use your files as if they are declared in the analysis.


https://doc.windev.com/en-US/…
https://doc.windev.com/en-US/…
Signaler
0
0
#7
Posté le 18 décembre 2017 - 13:39
How much feasible this requirement implementation is ?

-A database,"MyDB" with a table "MyTable" in a remote HFSQL server
-A windev application to connect to the this database in the HFSQL server using HOpenConnection

- Windev application to execute some random SQL queries on this MyTable.

I don't think I can point to any data files as such because my current requirement is is only to point a database and execute queries on that
Signaler
0
0
#8
Posté le 18 décembre 2017 - 14:42
Try something like this:
Persons is datasource
SQLResult is datasource
cntDatabase is Connection
cntDatabase..Provider = hAccessHFClientServer
cntDatabase..Server = IP + ":" + Port
cntDatabase..Database = MyDB
cntDatabase..User = username
cntDatabase..Password =password
// Connection to the database
IF HOpenConnection(cntDatabase) = False THEN
Error(HErrorInfo())
RETURN
END


IF NOT HDeclareExternal(".\Persons.fic","Persons",cntDatabase) THEN
Error("Error on the external declaration: ", HErrorInfo())
RETURN
END
IF HExecuteSQLQuery(SQLResult,cntDatabase,hQueryWithoutHFCorrection,"SELECT * from Persons" ) = False THEN
Error(HErrorInfo())
RETURN
END
Signaler
0
0
#9
Posté le 18 décembre 2017 - 21:21
Yes Paulo is correct if you don't use OpenAnalysis("yourDB.wdd") to let
hfSql know the catalogue/tableInfo or all you have to do
to make an explicit HDeclareExternal("persons.fic", "person", sConnDesc) and
it gives errors indeed.

HTH

King
Signaler
0
0
#10
Posté le 19 décembre 2017 - 04:31
Thanks for replying Paulo and Kingdr .

Since the sample query is just for "Persons" table we can fire on Persons.fic.

By the way ,if there are queries which we don't have prior idea about the tables used or if there are joins with other tables then how do we approach this problem .

Something like SELECT * FROM A INNER JOIN B or SELECT * FROM X

In these cases are we supposed to parse the table name from the query and do the HDeclareExternal ?

So is it something like if I use a HFSQL database then I need to mapping for Table.fic always and there is no other way to query against that database just after opening a connection?

In ADO.net and

I thought there would be no need of mapping/declaring the Table.fic files to execute a query.
something like in .net

using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
}
Signaler
0
0
#11
Posté le 19 décembre 2017 - 09:34
You could use the OLEDB driver to connect to the HFSL database. This driver then takes care of getting the table info and so on (i.e. the the HDeclareExternal(). You could even do that from .NET, since you mentioned it as one of your skills ;)

You can also do it yourself with the following few lines

sFileList is string = HListFile(cHFSQL,hLstNormal) FOR EACH STRING sFile OF sFileList SEPARATED BY CR HDeclareExternal(sFile, ExtractString(sFile,1,"."), cHFSQL) END

There is also HListItem() if you also want to get the item names (aka fields or columns)

You can even use HListDatabase to get all available databases first and let the user choose one for example.
Signaler
0
0
#12
Posté le 21 décembre 2017 - 12:05
Thank you Paulo Oliveira ,Arie,kingdr for helping us out. The HDeclare finally worked !!!
Signaler
0
0
#13
Posté le 09 avril 2025 - 21:50
HExecuteSQL for a HFSQL CS is a joke!
It should behave as a regular query without having to use HDeclare! It's an utter joke!
I use that command to query other external databases, and I repeat, it should behave the same way!

Having said that, this is the work around after dealing with this sad joke of implementation
Since WDSQL works without declaring external files I decided to implement old functions

IF SQLConnect("Server","User","Password","NameDatabase,"HFSQLCS") = 0 THEN
SQLInfo()
Error(SQL.MesError))
return
END

IF SQLExec(sSQL,"MyQuery") = False THEN
SQLInfo()
Error(SQL.MesError))
return
END
sColumns is string = SQLColumn("MyQuery",False)
WHILE SQLFetch("MyQuery") = 0
iColumn is int = 1
FOR EACH STRING sColumn OF sColumns SEPARATED BY CR
sRes is string = SQLGetCol("MyQuery",iColumn)
iColumn += 1
END
END
SQLDisconnect()
Signaler
0
0
→ Revenir à WINDEV 2024