SQL GET FIELD (SQLGTF)
|
SQL GET FIELD (SQLGTF) |
|
| |
Standard Verb SQL Operation |
|
Gets a field of a PostgreSQL table after doing an SQL query.
SQL GET FIELD row FIELD ROW column number or name FIELD COLUMN sql query SQL QUERY result to noun FIELD NOUN
FIELD ROW is a number or a noun containing the row number of the field one wants to retrieve. Alter the verb has run it will contain
FIELD COLUMN is a number, an expression or a noun containing the column number or the column name of the field one wants to retrieve. In other words, you can enter either the column number or the column name.
SQL QUERY is an expression or a noun containing the SQL query that fetches the table with all the fields in the rows and columns.
FIELD NOUN is a noun that will have the expression of the requested field.
To get the field with the name in an "address" table, assuming that "name" is the first field in the table, you write the following statement
SQL GET FIELD row 1 column number or name 1 sql query "SELECT * addresses WHERE..." result to noun FIELD NOUN
or
SQL GET FIELD row 1 column number or name "name" sql query "SELECT * addresses WHERE..." result to noun FIELD NOUN
FIELD NOUN will contain, for instance " Dow Joe"
All the rows and columns fetched by the query will be accessible with this verb.
 |
After every SQL GET FIELD you must look at the FILE STATUS or EXTERNAL ECHO, just like you would after a GET of a Pragma file to see whether you have the right answer or not. |
After you have verified that the SQL GET FIELD returned a valid answer, you can then do a:
SET ELEMENT PROPERTY element EDIT NAME property PROPERTY TEXT to the value FIELD NOUN
or anything else with the returned FIELD NOUN.
EXTERNAL ECHO (XE), FILE STATUS (FST)
All the SELECT queries will result in one or more rows, with one or an X number of columns.
If your query results in 100 rows, you can get all the retrieved data of a column by doing a loop of one hundred SQL GET FIELD and copy the resulting data of the field nouns to a list control, a grid, a combo box or whatever.
We strongly recommend against saving the data into a file. This data is meant to be used immediately and if you need it again, repeat the query and get the various fields again, otherwise you may get into trouble with corrupted data.
Remember that SQL is different from other databases. When you do a query it is up to your knowledge of SQL to get back as few or as many rows as possible. For instance, when you look for all the companies that you invoiced in the month of October from Fort Lauderdale you will not get more than say 5 to 10 companies. If you get too many, you must refine your query and narrow down your search also by zip number.
If you look for a nonexistent row or column you will get back in the target noun EXTERNAL ECHO "record not found" and in FILE STATUS a 1. If the verb was successful you will get back in EXTERNAL ECHO "OK" and in FILE STATUS 0. Consult the reference for these two target nouns for other responses, but anything except an "OK" or a 0 means that something unexpected happened. It is therefore extremely important to always look at the EXTERNAL ECHO or FILE STATUS after every SQL GET FIELD, just like you would never do a GET of a Pragma file and then not check the EXTERNAL ECHO or FILE STATUS.
Usually, before you do one or many SQL GET FIELD you would like to know how many rows and how many columns you got. To get the answer do a SQL GET PARAMETERS (SQLGTP). The number of rows and columns retrieved will help you program a loop to display all the data obtained with your query.
When you do the first SQL GET FIELD Pragma 6 will get the answer from the PostgreSQL file manager and store the complete answer in memory. The storing in memory is of course very fast, compared to the time it gets to access an SQL database. When you do your next SQL GET FIELD with the same SQL query Pragma 6 will not query the database again but will look at the answer stored in memory. This memory storage is valid only until you do a different query. To force Pragma 6 to make the same query again you simply do a NULL "" query, which will clear the Pragma 6 memory.
There is a potential incompatibility between Pragma 6 and PostgreSQL. In PostgresQL you can have an underline in the name of a column while in Pragma 6 an underline is part of a control character. There is nothing that we can do about this except to warn you not to use underlines in the column names of PostgreSQL.
The form FORM SQL ADDRESSES DS 1 of the Pragma 6 tutorial vocab shows how to display a table row with the verb SQL GET FIELD and also uses the verb SQL GET PARAMETERS.
SQLGF standard # 429, verb # 366, msg # 771, 772, 773, 774