Author | Topic: 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 |