Alaska Software Inc. - How to use SQL direct on DBF and ARRAY ?
Username: Password:
AuthorTopic: How to use SQL direct on DBF and ARRAY ?
Hubert BrandelHow to use SQL direct on DBF and ARRAY ?
on Wed, 31 Aug 2016 15:20:26 +0200
Hi,

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):

use customer alias DB
select * from DB where DB->(name) like "hans"

do while ???
    ? "Name:",x,???->name

or as a resultset with DataObjects in Array ?

...

or direct search an a array

aFiles := directory(...)

select * from (aFiles) where ... ???



And does this is anywhere in the docu yet and i am blind ?

Regards
Hubert
Hubert BrandelRe: How to use SQL direct on DBF and ARRAY ?
on Wed, 31 Aug 2016 15:25:56 +0200
Am 31.08.2016 um 15:20 schrieb Hubert Brandel:

> use customer alias DB
> select * from DB where DB->(name) like "hans"

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

once I want no case sensitive: lower(DB->(name)) like "hans"
but sometime I want only the one I just search for "Hans" or "hans"
Andreas Gehrs-Pahl
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
Hubert BrandelRe: How to use SQL direct on DBF and ARRAY ?
on Fri, 02 Sep 2016 07:40:26 +0200
Thanks a lot for your hints, not so easy as I thought 

Am 01.09.2016 um 16:56 schrieb Andreas Gehrs-Pahl:

 >> oStatement:Select({{1}}):From({{"DB"}})

can you explain what {{1}} means ?

{{"DB"}} this looks like the ALIAS of a open DBF, right ?

Regards
Hubert
Hubert BrandelRe: How to use SQL direct on DBF and ARRAY ?
on Fri, 02 Sep 2016 09:15:56 +0200
Am 02.09.2016 um 07:40 schrieb Hubert Brandel:
> Thanks a lot for your hints, not so easy as I thought 
>
> Am 01.09.2016 um 16:56 schrieb Andreas Gehrs-Pahl:
>
>>> oStatement:Select({{1}}):From({{"DB"}})
>
> can you explain what {{1}} means ?
>
> {{"DB"}} this looks like the ALIAS of a open DBF, right ?
>
> Regards
> Hubert

I got it, with the PPO file:

SELECT unf_nr,meja,lieferbg FROM DB WHERE meja = 2015  INTO ARRAY aData

is translated to ...

USqlStatement():New():Select( {{"unf_nr"}, {"meja"}, {"lieferbg"}} 
):From( {{"DB"}} ):Where( {||meja=2015} ):Build():Query( 2, @aData )

but still I get here a internal data structure

------------------------------------------------------------------------------
FEHLERPROTOKOLL von 
"C:\Users\BRANDELH\Documents\Xbase++\Projects\TestSQL-HB\test.exe" 
Datum: 02.09.2016 09:15:16

Xbase++ Version     : Xbase++ (R) Version 2.00.721
Betriebssystem      : Windows 7 06.01 Build 07601 Service Pack 1
------------------------------------------------------------------------------
oError:args         :
oError:canDefault   : N
oError:canRetry     : N
oError:canSubstitute: N
oError:cargo        : NIL
oError:description  : Interne Datenstrukturen besch„digt
oError:filename     :
oError:genCode      :         41
oError:operation    :
oError:osCode       :          0
oError:severity     :          2
oError:subCode      :          5
oError:subSystem    : BASE
oError:thread       :          1
oError:tries        :          0
------------------------------------------------------------------------------
CALLSTACK:
------------------------------------------------------------------------------
Aufgerufen von USQLSTATEMENT:CREATEVIRTUALTABLEFROMWORKAREA(312)
Aufgerufen von USQLSTATEMENT:REWRITETABLE(358)
Aufgerufen von ABSTRACTSQLSTATEMENTBUILDER:FROM(295)
Aufgerufen von MAIN(81)
Hubert BrandelRe: How to use SQL direct on DBF and ARRAY ?
on Fri, 02 Sep 2016 08:29:44 +0200
Am 01.09.2016 um 16:56 schrieb Andreas Gehrs-Pahl:
> 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

I have tried this:
------------
    use Test1 alias DB
    IF neterr()
       msgbox("DBF konnte nicht geöffnet werden")
       quit
    ENDIF

    DbeInfo(DBFDBE_LOCKOFFSET, 0x7FFFFFFF )

    ? "DBF USED()",used(),"NETERR()",neterr(),"Alias()", ;
      padr(alias(),8),"Select()",ntrim(SELECT()), "LastRec()",lastrec()

    aData := NIL

    ? "Select start"
    nDauer := seconds()
     this is line 70, and the next will cause the problem ***
    SELECT * FROM DB WHERE meja = 2015  INTO ARRAY aData  *** crash
    nDauer := seconds() - nDauer

    ? "Sekunden: ",nDauer,len(aData)
    inkey(10)
    AEval( aData, {|o|QOut(o)} )
    ?
    ? "Treffer:",len(aData)
------------
The error is - internal Data corrupted:
: oError:description  : Interne Datenstrukturen besch„digt
(312): USQLSTATEMENT:CREATEVIRTUALTABLEFROMWORKAREA
(358): USQLSTATEMENT:REWRITETABLE
(295): ABSTRACTSQLSTATEMENTBUILDER:FROM
main.prg(71): MAIN

-----------------

The DBF just have about 110 fields and 700.000 records
size 600 MB.
Adrian WykrotaRe: How to use SQL direct on DBF and ARRAY ?
on Thu, 29 Dec 2016 22:52:04 +0100
It is still not working - from september.....


I think - in Alaska opinion - only Postgress is Sql data base - not MS.... - 
but my client want Ms... DacScheme - not working, DacSqlStatement- not 
working....


Użytkownik "Hubert Brandel" napisał w wiadomości grup 
dyskusyjnych:20357e2a$7496c4a4$17b91d@news.alaska-software.com...

Am 01.09.2016 um 16:56 schrieb Andreas Gehrs-Pahl:
> 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

I have tried this:
------------
    use Test1 alias DB
    IF neterr()
       msgbox("DBF konnte nicht geöffnet werden")
       quit
    ENDIF

    DbeInfo(DBFDBE_LOCKOFFSET, 0x7FFFFFFF )

    ? "DBF USED()",used(),"NETERR()",neterr(),"Alias()", ;
      padr(alias(),8),"Select()",ntrim(SELECT()), "LastRec()",lastrec()

    aData := NIL

    ? "Select start"
    nDauer := seconds()
     this is line 70, and the next will cause the problem ***
    SELECT * FROM DB WHERE meja = 2015  INTO ARRAY aData  *** crash
    nDauer := seconds() - nDauer

    ? "Sekunden: ",nDauer,len(aData)
    inkey(10)
    AEval( aData, {|o|QOut(o)} )
    ?
    ? "Treffer:",len(aData)
------------
The error is - internal Data corrupted:
: oError:description  : Interne Datenstrukturen besch„digt
(312): USQLSTATEMENT:CREATEVIRTUALTABLEFROMWORKAREA
(358): USQLSTATEMENT:REWRITETABLE
(295): ABSTRACTSQLSTATEMENTBUILDER:FROM
main.prg(71): MAIN

-----------------

The DBF just have about 110 fields and 700.000 records
size 600 MB.