|
| Iniciado por guest, 28,sep. 2015 04:06 - 15 respuestas |
| |
| | | |
|
| |
| Publicado el 28,septiembre 2015 - 04:06 |
Hello,
Windev 20 66r
Planning to develop a generic application with no predefined Database or Table (File). A GUI to choose the following
1. Connection type (OLE DB / ODBC etc.)
2. Server , Database , Userid, Password
3. Select Table
4. Field selection (I need 6 fields, but the name of the field(s) need not be predefined)
by the user need to be presented and based on the user selection, further processing need to be done.
Need to show the tables and fields for selection by the user. ie, after connection, the GUI is to list database and the user will select one. After selecting the database, the table has to be chosen from the list.
Problem(s) faced.
1. Problem in preparing Analysis. Since no predefined table, how to proceed. Not sure whether I can proceed without any analysis.
2. To show the list of fields from a table (file) not from analysis but from the user chosen table (file).
3. Not sure how to construct SQL to select, insert, update, delete data.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 08:38 |
Hello,
HListFile(Connection, hLstFile)
list all Tables found in a database, whether it's in analysis or not.
Is there any function to retrieve fields found in a table, like
HListFields(Connection, FileName) or something similar ?
HListItem lists fields found in a table found in the analysis. If the file is not in the analysis then an error is triggered.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 08:47 |
Hello Sivaprakash
First you need to get the Windev terminology correct. In Windev "tables" are NOT files inside a database, they are display grids in windows and are gnerally used to display data from data files.
A Windev database has data files inside it and they are NOT referred to as tables.
These are important distinctions when you are seeking information in Windev.
The analysis is at the heart of most Windev projects so I think you are facing a hard and possibly pointless task in trying to create an application that uses data in the abstract way you are proposing.
You may get more replies if you can explain the purpose of the application and why it needs to function as you have described.
Regards
Al |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 11:53 |
Hello,
Thanks Al for your clarification. Yes, I'm new to Wx products and terminologies.
I'm trying to develop one generic application to send SMS from the data stored in a database. As a generic application, user can use any database they wish. The only requirement is few must fields to be there in file of the selected database, like
Mobilenumber, Message, DateToSend, Status (Sent / Pending), ErrorMessage
Even for these fields, fields names need not be the same as mentioned above and user can have their own field names from where I can draw the above messages.
So a interface to select database first, then file next and finally corresponding required fields is need to be developed. The need for an analysis is not there in this case, I think.
After selection of fields, a SQL is to be constructed to select data to send SMS and result need to be updated back to the file. Facility to construct SQL queries, to select and to update, at run time is also required.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 12:05 |
You don't need any analysis for this just use the HExecuteSQLQuery function with hQueryWithoutCorrection.
To build the query (SELECT/UPDATE/DELETE/INSERT) just compose one string with complete SQL text and run it using HExecuteSQLQuery function. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 12:17 |
Hi
what you are describing is possible, but will be quite complex to achieve.
You can use a generic analysis in which you do have predefined fiels for your file.
One of them will be a text memo, and in it, you cna store any type of fields using the NAME/VALUE pair technology. This system is used by example in the medical field, when they will use different symptoms/facts in each record and don't want to have hundreds of empty fields in each record.
I have an example of that technology available in WXReplication (the wxreplication file stores data this way).
You would get something like:
MOBILENUMBER=1234567899
MESSAGE=Hello man, this is an example
DATETOSEND=20150929
STATUS=0
...
All in ONE field, with anything you want in it.
Of course, the settings for these fields can themselves be described in a settings file (in my wxreplication example, it's FH_Param) and the above example could be stored this way:
NUMBEROFFIELDS=4
FIELD1NAME=MobileNumber
FIELD1CAPTION=Mobile Number
FIELD1TYPE=1
FIELD2NAME=Message
FIELD2CAPTION=Text of message
FIELD2TYPE=2
...
With the control clone and control create, you can pretty much do anyting you want with the UI, so that part is easy, if complex.
After that, the problem becomes the queries. as long as you don't need a key on any of the custom fields, you are golden. If you do need keys, then you can create an index file (or several)
In it, you'll have fields like so:
- FIleName
- FieldName
- Value
And of course a composite key on these 3 values. This allows you to create an index on ANY custom field. You can even create an index on several fields at once by using FIELDNAME=Field1Name+Field2Name and building the composte key value by code.
All that is doable (I would know, I did my first version of that in 2001, with windev 5.5)
As for the query design, you can create them by offering a query designer like the pcsoft one.
So now, the only remaining thing is to decide if all that is really worth it for your project.
Best regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 12:20 |
Thanks Paulo Oliveira.
I could get list of files in a database with HListFile, without any analysis.
Is there any function get list of fields from a file, without any analysis ? HListItem is not working without an analysis.
Yet to try with HExecuteSQLQuery functions.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 12:35 |
Thanks Fabrice Harari for your reply.
But my system is not that complex. Here I'm just going to send SMS from the data available in a database and update the success/ failure of that action. That's all. Creation of such SMS messages is not the responsibility of this application, but my some other application(s) that users may be having.
That's why this generic application to fetch those required details (database, file, fields ) here. Once we have these details, this application will read those SMS messages, send it, change the status (success / failure), error messages, if any back to the same file.
Creation of table, index(s) and its maintenance will not be part of this application.
I'm struggling now to get list of items (fields) from a file without an analysis.
Happiness Always
BKR Sivaprakash
I'm going through your WXReplication project, which I'm planning to use in my next project. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 13:35 |
Hello,
Is there any command to get list of field names after executing a query like,
asFile = CMB_DBFiles..DisplayedValue //user selected file
asQuery = "Select * From " + asFile
bQueryResult = HExecuteSQLQuery(sdQuery, cnxSQLConnection, hQueryWithoutCorrection, asQuery)
IF bQueryResult THEN
// This gives me the no. of items / columns found in the file
Info("Query Run success" + sdQuery..NbItem)
END
Now I need to read those items/columns from the sdQuery. Any command / solution available ?
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 13:45 |
If you don't use any analysis to list the files/fields may be different depending on the DB type.
Which DB type are you planning to support? HF Classic, HFCS, MSSQL, MYSQL,ORACLE,...?
For HFCS you can use HListDatabase/Hdeclareexternal/HListItem
For HF Classic you can use fListFile/Hdeclareexternal/HListItem
For almost all the other DB types you can have this information from system view like information_schema.tables and information_schema.columns |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 13:48 |
| you can list the fields from the query using HListItem |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 14:33 |
Thanks Paulo Oliveira,
HListItem on the query did the trick. I got it.
Think I made the things more complex by trying out with HDeclareExternal commands for a SQL Server database..... Thanks a lot, a simple command did the trick, which was evading so far.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 15:02 |
HDeclareExternal works fine with SQL Server, but if select * is OK for you (be carefull with the performance problems if your have a lot of rows in the DB) use it.
For SQL Server my advice is to query the information_schema views, but as always the performance issues only matter when you have a lot of row in your tables. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 15:47 |
Yes Paulo Oliveira, you are right. Performance problem is an issue.
Is there any generic SQL query to fetch the top 1 record ? Since this is a generic application, I can't code DB specific queries.
Also any other way to fetch the list of fields ?
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,septiembre 2015 - 16:34 |
I'm not sure if it work for every db engine but normally if you only need to get the list of fields something like this can do the trick:
asQuery = "Select * From " + asFile +" where 1=2" |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,septiembre 2015 - 07:29 |
Paulo Oliveira,
With empty records I could fetch all column names, so that I thought of querying like
asQuery = " Select * from " + asFile + " Where Id = 0 "
Sure, I need to fix on column name as ID, which is to be a auto generated one and won't have 0 as data.
I'll try your query first as this makes me more generic.
Happiness Always
BKR Sivaprakash |
| |
| |
| | | |
|
| | | | |
| | |
|