Hi,

I have been "playing" with the ODBCDBE SQL command to do UPDATEs & 
SELECTs using the Northwind database setup locally on my laptop with MS 
SQL Server.  Using DBInfo() with various options I'm trying to figure 
out what is going on.  It almost seems like a new work area is created 
after each SQL UPDATE & SELECT is run.

On the first pass if I execute the following SQL Command...

UPDATE Customers SET Region = 'Updt          1' WHERE CustomerId = 'ALFKI'

DBInfo() report the following:

DBE: ODBCDBE
Alias: 
UPDATE_CUSTOMERS_SET_REGION____UPDT__________1__WHERE_CUSTOMERID____ALFKI__
File: UPDATE Customers SET Region = 'Updt          1' WHERE CustomerId = 
'ALFKI'

IF I the do a SELECT...

SELECT CustomerId,Region FROM Customers WHERE CustomerId = 'ALFKI'

DBInfo() reports...

DBE: ODBCDBE
Alias: CUSTOMERS
File: SELECT CustomerId,Region FROM Customers WHERE CustomerId = 'ALFKI'

On the second pass the UPDATE gives similar results and the following...

SELECT CustomerId,Region FROM Customers WHERE CustomerId = 'ANATR'

Causes DBInfo() to report...

DBE: ODBCDBE
Alias: CUSTOMERS_4
File: SELECT CustomerId,Region FROM Customers WHERE CustomerId = 'ANATR'

With subsequent passes causing the Customer Alias to increment to 
CUSTOMER_6, CUSTOMER_8 and so on.

At the end of the process after cycling through all the records it 
appears that I have many of these work areas open.  I have created a 
function that will allow me to close each of these work areas after the 
SQL statement so I'm OK in that regard.

What I would be interested in knowing is where these work areas are 
being created... is this some sort of virtual file within ODBC or is it 
a physical file on my drive?

I'm OK with what is happening with the SQL SELECT and the alias makes 
sense but the SQL UPDATE Alias & File name are "peculiar".

I guess I'm just wondering what is really going on "under the hood".

If any one has any insight or can point me to where I can find out more 
that would be great.

Thanks... Jonathan

jonathan.leeming@familycentre.org
Edmonton, Alberta, Canada

Jonathan,

>I have been "playing" with the ODBCDBE SQL command to do UPDATEs & 
>SELECTs using the Northwind database setup locally on my laptop with MS 
>SQL Server. Using DBInfo() with various options I'm trying to figure 
>out what is going on. It almost seems like a new work area is created 
>after each SQL UPDATE & SELECT is run.

The (ODBCDBE) SQL command is preprocessed into the SqlStmtExec() function 
(unless you use the INTO or CMD modifiers). 

The SqlStmtExec() functions works similar to the USE command or DbUseArea() 
function, in that it selects a new work area for its result. It is pretty 
much the same as "USE cSelect_Statement NEW" (or its functional equivalent).

>On the first pass if I execute the following SQL Command...
>UPDATE Customers SET Region = 'Updt          1' WHERE CustomerId = 'ALFKI'
>DBInfo() report the following:

>DBE: ODBCDBE
>Alias: UPDATE_CUSTOMERS_SET_REGION____UPDT__________1__WHERE_CUSTOMERID____ALFKI__
>File: UPDATE Customers SET Region = 'Updt          1' WHERE CustomerId = 'ALFKI'

Because DbInfo() must work with any DBE, it has to return a value for the 
DBO_ALIAS and DBO_FILENAME constants, even though they might not be quite 
applicable. The Alias() function should return the same as DbInfo(DBO_ALIAS) 
and if you use the USE statement -- or DbUseArea() function -- the rules for 
Alias name creation are actually documented under:

+ "Database Engines"
+-> "Open Database Connectivity (ODBCDBE)"
+--> "Specific Aspects for Database Programming"
+---> "Naming conventions for fields and aliases"

You might also be able to use DbInfo(DBO_SERVER) to determine if a file name 
is actually applicable or not.

>At the end of the process after cycling through all the records it 
>appears that I have many of these work areas open. I have created a 
>function that will allow me to close each of these work areas after the 
>SQL statement so I'm OK in that regard.

I don't think that cycling through records using the SQL Select statement 
is a very sensible thing to do. Keep in mind that the SQL statement is very 
similar (or equivalent) to the USE statement.

Instead, open a cursor with all the records you need (by eliminating or 
modifying the WHERE clause) and then Skip through the returned records, as 
you would with any other work area.

>What I would be interested in knowing is where these work areas are 
>being created... is this some sort of virtual file within ODBC or is it 
>a physical file on my drive?

The work area paradigm is used to abstract the database or cursor access 
away from the underlying physical representation (like files, tables, views, 
databases, etc.) With the ODBCDBE you can use most of the standard database 
commands and functions to skip, seek, replace, filter etc. Which functions 
(and their corresponding commands) are supported, see here:

+ "Database Engines"
+-> "Open Database Connectivity (ODBCDBE)"
+--> "Xbase++ database function support overview"

>I'm OK with what is happening with the SQL SELECT and the alias makes 
>sense but the SQL UPDATE Alias & File name are "peculiar".

If you want to use the SQL Update command to update multiple records -- 
instead of simply updating records using the following formats:

Field := Value
or
replace Field with Value

then you might want to use the "SQL cCommand INTO nVar" format -- or the 
corresponding SqlGetResult() function -- as the return value is a single 
(numerical) value: the number of affected rows (or records) on the server. 
So, there is even less use for an Alias, Work Area or File Name, in this 
particular case.

>I guess I'm just wondering what is really going on "under the hood".
>If any one has any insight or can point me to where I can find out more 
>that would be great.

The ODBCDBE is designed to allow you access to SQL RDBMS (and other types of 
data sources) using ODBC, while giving you access to work areas and all the 
other xBase/dBase commands and functions.

That is one of its main differences to SQLExpress -- which is class-based 
and doesn't support work areas -- besides the fact that SQLExpress is much 
more sophisticated, crashes much less often and can handle things that the 
ODBCDBE just can't.

So, if you do use the ODBCDBE, try to take advantage of the work area 
paradigm when ever you can, as that is basically its only advantage over 
SQLExpress (if you consider that an advantage).

Hope that helps,

Andreas

Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[L]:   https://www.LinkedIn.com/in/AndreasGehrsPahl
[F]:   https://www.FaceBook.com/AbsoluteSoftwareLLC

On 6/24/2020 6:30 PM, Andreas Gehrs-Pahl wrote:
> Jonathan,
> 
>> I have been "playing" with the ODBCDBE SQL command to do UPDATEs &
>> SELECTs using the Northwind database setup locally on my laptop with MS
>> SQL Server. Using DBInfo() with various options I'm trying to figure
>> out what is going on. It almost seems like a new work area is created
>> after each SQL UPDATE & SELECT is run.
> 
> The (ODBCDBE) SQL command is preprocessed into the SqlStmtExec() function
> (unless you use the INTO or CMD modifiers).
> 
> The SqlStmtExec() functions works similar to the USE command or DbUseArea()
> function, in that it selects a new work area for its result. It is pretty
> much the same as "USE cSelect_Statement NEW" (or its functional equivalent).
> 
>> On the first pass if I execute the following SQL Command...
>> UPDATE Customers SET Region = 'Updt          1' WHERE CustomerId = 'ALFKI'
>> DBInfo() report the following:
> 
>> DBE: ODBCDBE
>> Alias: UPDATE_CUSTOMERS_SET_REGION____UPDT__________1__WHERE_CUSTOMERID____ALFKI__
>> File: UPDATE Customers SET Region = 'Updt          1' WHERE CustomerId = 'ALFKI'
> 
> Because DbInfo() must work with any DBE, it has to return a value for the
> DBO_ALIAS and DBO_FILENAME constants, even though they might not be quite
> applicable. The Alias() function should return the same as DbInfo(DBO_ALIAS)
> and if you use the USE statement -- or DbUseArea() function -- the rules for
> Alias name creation are actually documented under:
> 
> + "Database Engines"
> +-> "Open Database Connectivity (ODBCDBE)"
> +--> "Specific Aspects for Database Programming"
> +---> "Naming conventions for fields and aliases"
> 
> You might also be able to use DbInfo(DBO_SERVER) to determine if a file name
> is actually applicable or not.
> 
>> At the end of the process after cycling through all the records it
>> appears that I have many of these work areas open. I have created a
>> function that will allow me to close each of these work areas after the
>> SQL statement so I'm OK in that regard.
> 
> I don't think that cycling through records using the SQL Select statement
> is a very sensible thing to do. Keep in mind that the SQL statement is very
> similar (or equivalent) to the USE statement.
> 
> Instead, open a cursor with all the records you need (by eliminating or
> modifying the WHERE clause) and then Skip through the returned records, as
> you would with any other work area.
> 
>> What I would be interested in knowing is where these work areas are
>> being created... is this some sort of virtual file within ODBC or is it
>> a physical file on my drive?
> 
> The work area paradigm is used to abstract the database or cursor access
> away from the underlying physical representation (like files, tables, views,
> databases, etc.) With the ODBCDBE you can use most of the standard database
> commands and functions to skip, seek, replace, filter etc. Which functions
> (and their corresponding commands) are supported, see here:
> 
> + "Database Engines"
> +-> "Open Database Connectivity (ODBCDBE)"
> +--> "Xbase++ database function support overview"
> 
>> I'm OK with what is happening with the SQL SELECT and the alias makes
>> sense but the SQL UPDATE Alias & File name are "peculiar".
> 
> If you want to use the SQL Update command to update multiple records --
> instead of simply updating records using the following formats:
> 
> Field := Value
> or
> replace Field with Value
> 
> then you might want to use the "SQL cCommand INTO nVar" format -- or the
> corresponding SqlGetResult() function -- as the return value is a single
> (numerical) value: the number of affected rows (or records) on the server.
> So, there is even less use for an Alias, Work Area or File Name, in this
> particular case.
> 
>> I guess I'm just wondering what is really going on "under the hood".
>> If any one has any insight or can point me to where I can find out more
>> that would be great.
> 
> The ODBCDBE is designed to allow you access to SQL RDBMS (and other types of
> data sources) using ODBC, while giving you access to work areas and all the
> other xBase/dBase commands and functions.
> 
> That is one of its main differences to SQLExpress -- which is class-based
> and doesn't support work areas -- besides the fact that SQLExpress is much
> more sophisticated, crashes much less often and can handle things that the
> ODBCDBE just can't.
> 
> So, if you do use the ODBCDBE, try to take advantage of the work area
> paradigm when ever you can, as that is basically its only advantage over
> SQLExpress (if you consider that an advantage).
> 
> Hope that helps,
> 
> Andreas
> 
Thanks Andreas,

My application uses ADS but we have other systems that use SQL Server so 
I was exploring options to do occasional data extracts from these other 
databases.  I have also successfully updated them but this would be a 
very low volume and irregular activity.

I do have a current license for SQLExpress but was exploring options for 
someone who was looking for a more economical solution to a simple query 
without further investment.

Given your explanations I will continue my explorations.

Thanks yet again... Jonathan

jonathan.leeming@familycentre.org
Edmonton, Alberta, Canada