Alaska Software Inc. - Re: How to use SQL direct on DBF and ARRAY ?
Username: Password:
AuthorTopic: Re: How to use SQL direct on DBF and ARRAY ?
Andreas Gehrs-Pahl

View the complete thread for this message in:

Re: How to use SQL direct on DBF and ARRAY ?
on Thu, 01 Sep 2016 10:56:41 -0400
Hubert,

>I try to use the SQL commands on DBFs and Arrays, but just only get 
>errors from compiler or runtime.

>Does someone have easy examples for this (syntax like DB->name in 
>dblocate):

You can use USQL on dbfs and Arrays (of DataObjects), and the result set 
can be an Array, an Array of DataObjects, or a single Value -- at least for 
now. The target clauses: "Into Cursor" and "Into Table" don't work at all, 
and "Eval" seems to only process a single (DataObject) result value, rather 
than all result values.

You also can't use non-Xbase++ operands and functions in your Where clause 
and the Where clause can't be enclosed in "(" and ")" as this would be 
interpreted as being another sub-Select by the pre-processor. 

Additionally, the Where clause must always be in the form: 

<FieldName> <Xbase++_Comparison_Operator> <Expression>

So, "like" can't be used, as it isn't part of the Xbase++ language and you 
can't use the "$" operand, as the <FieldName> needs to come first (and it 
isn't part of the SQL language). Also using a codeblock or a function is not 
possible, either. Finally, the "=" operand is treated the same as "==", no 
matter what the Set Exact setting is, so doing any kind of partial or 
inexact comparisons isn't possible at all. 

It seems to be a Catch 22: "like" can't be used as the Xbase++ compiler 
won't accept it, and "$" can't be used, because SQLLite doesn't understand 
it.

Instead, you might be able to use: "Name = 'hans'" as a substitute, even 
though that isn't quite the same as using "like". You could also use: 
"Name = 'hans' .or. Name = 'Hans' .or. Name = 'HANS'", but I don't know how 
practical any of this would be in the real world.

So the following should (kinda) work:

#include 'DAC.ch'         Needed for Universal SQL

Use Customer Alias DB
Select * from DB Where Name = "Hans" Into Array aHans

You can then use the resulting Array aHans and process it any way you like:

AEval(aHans, {|aItem| QOut(aItem[1])})

or

nItems := len(aHans)
for nItem := 1 to nItems
   QOut(aHans[nItem, 1])
next nItem

>or as a resultset with DataObjects in Array ?

Select * from DB Where Name = "Hans" Into Objects aHans

You can then evaluate this Array of DataObjects any way you like, and you 
will be able to access members (fields) by their (field) name:

AEval(aHans, {|oCustomer| QOut(alltrim(oCustomer:Name))})

You can also use an Array of DataObjects as your source:

Select * from DB Into Objects aCustomers   Creates Array of DataObjects
Select Name, ... from (aCustomers) Where Name = "Hans" Into Array aHans

>or direct search an a array

This (currently) ONLY works IF you don't use a Where clause (to filter on 
the content of the Source Array). The reason for this is that Arrays have no 
(Field/Column) Names, and there is (currently) no way to specify an Index 
Number to access a particular Array (sub-)element in either the Where clause 
(codeblock) or the Select clause. So, the following works (but might not be 
very useful):

aFiles := directory(...)

Select * from (aFiles) into aCopy

To create a Where clause that works with (simple) Arrays as the source, or 
to Select only particular array elements from a multi-dimensional array, the 
pre-processor would have to be able to access individual Array (sub-)Items 
via an Index ID. The way that the pre-processor creates the USqlStatement() 
statement, and the fact that neither the :Select(), :From(), :Where(), and 
similar methods are documented anywhere, makes it unlikely that you can 
figure out a way to do this by yourself.

>and how to handle the upper lower case searches ...

You might be able to work around those by not using the Select command 
syntax, but the (not very pretty and undocumented) function/class syntax:

oStatement := USqlStatement():New()
oStatement:Select({{1}}):From({{"DB"}})
oStatement:Where({|| lower(Name) = 'hans'})       Here you can use lower()
oStatement:Build():Query(USQL_RESULT_ARRAY, @aHans)

Hope that helps, at least somewhat.

Andreas

Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[F]:   https://www.facebook.com/AbsoluteSoftwareLLC