Alaska Software Inc. - ADS Problem width AdsGetField
Username: Password:
AuthorTopic: ADS Problem width AdsGetField
Jorge L BorlandoADS Problem width AdsGetField
on Tue, 16 Aug 2022 12:50:39 -0300
good morning colleagues

I am testing with ADS 10 using the API,
the tables are DBFCDX defined in a dictionary ( don´t work with free tables)

when I make complex queries such as:

SELECT SUBSTRING( d_c.resume, 1, 5 ) AS codpro,
    IIF( LENGTH( TRIM( SUBSTRING( a_f.coment, 21, 12 ) ) ) = 0, SUBSTRING( 
art.coment, 21, 12 ), SUBSTRING( a_f.coment,  1, 12 ) ) Is there any good 
practice to handle the results in this type of search in several tables and 
handling of aliases in the expressions AS f_coment
FROM stock_ AS art
        LEFT JOIN artfor AS a_f ON (art.codigo=a_f.codigo)
        LEFT JOIN detcom AS d_c ON (art.codigo=d_c.codigo)
        LEFT JOIN provee AS pro ON (SUBSTRING( d_c.resume, 1, 5 ) = 
pro.codigo)
WHERE art.codigo IN (345,4896,1090)
GROUP BY codpro,f_coment

the result return 2 fields, codpro (CHAR,5) and f_coment (CHAR,12)

if I run this query in ARC32 utility I get the result correctly which is 5 
records.

When I run the query in my app using the folow sentences

      @ace32:AdsCreateSQLStatement( nHandle, @nStat )
      @ace32:AdsStmtSetTableType( nStat, ADS_CDX )
      @ace32:AdsStmtSetTableLockType( nStat, ADS_COMPATIBLE_LOCKING )
      @ace32:AdsExecuteSQLDirect( nStat, cSQL, @nCursor )
...................
      @ace32:AdsGetRecordCount( ::nCursor, ADS_IGNOREFILTERS, @nRecord ) => 
nRecord = 5 too.

When I open the cursor with dbUseArea( .F., 'ADSDBE', ("<cursor>" + L2Bin( 
nCursor ) + "<\cursor>"),, .F. )
dbStruct() = {} and I can´t read de fields QUERY->codpro and I can't read 
the fields with GetField()  Scatter  etc..
now, when I use API funcions like AdsGetRecordNum(),  AdsGetFieldName (), 
AdsGetFieldType(), etc... work fine
but
AdsGetField(), AdsGetFieldW(), AdsGetString(), AdsGet.....(), the values 
*pucBuf and *pulLen return empties

with this statement I try to read each field of each record
nLen := 5
xRet := Space( nLen )
@ace32:AdsGetField( nCursor, 'codpro', @xRet, @nLen, ADS_NONE )
this command return
xRet = '' and nLen = 0

Is there any good practice to handle the results in this type of search in 
several tables and handling of aliases in the expressions?

I would really appreciate your help
best regards!

Jorge
Jorge L BorlandoRe: ADS Problem width AdsGetField
on Thu, 18 Aug 2022 10:53:34 -0300
Hi to all

the clause AS don´t work in xbase


Best Regards

"Jorge L Borlando"  escribió en el mensaje de 
noticias:136e265e$4a6d3fc1$67cd0@news.alaska-software.com...

good morning colleagues

I am testing with ADS 10 using the API,
the tables are DBFCDX defined in a dictionary ( don´t work with free tables)

when I make complex queries such as:

SELECT SUBSTRING( d_c.resume, 1, 5 ) AS codpro,
    IIF( LENGTH( TRIM( SUBSTRING( a_f.coment, 21, 12 ) ) ) = 0, SUBSTRING(
art.coment, 21, 12 ), SUBSTRING( a_f.coment,  1, 12 ) ) Is there any good
practice to handle the results in this type of search in several tables and
handling of aliases in the expressions AS f_coment
FROM stock_ AS art
        LEFT JOIN artfor AS a_f ON (art.codigo=a_f.codigo)
        LEFT JOIN detcom AS d_c ON (art.codigo=d_c.codigo)
        LEFT JOIN provee AS pro ON (SUBSTRING( d_c.resume, 1, 5 ) =
pro.codigo)
WHERE art.codigo IN (345,4896,1090)
GROUP BY codpro,f_coment

the result return 2 fields, codpro (CHAR,5) and f_coment (CHAR,12)

if I run this query in ARC32 utility I get the result correctly which is 5
records.

When I run the query in my app using the folow sentences

      @ace32:AdsCreateSQLStatement( nHandle, @nStat )
      @ace32:AdsStmtSetTableType( nStat, ADS_CDX )
      @ace32:AdsStmtSetTableLockType( nStat, ADS_COMPATIBLE_LOCKING )
      @ace32:AdsExecuteSQLDirect( nStat, cSQL, @nCursor )
...................
      @ace32:AdsGetRecordCount( ::nCursor, ADS_IGNOREFILTERS, @nRecord ) =>
nRecord = 5 too.

When I open the cursor with dbUseArea( .F., 'ADSDBE', ("<cursor>" + L2Bin(
nCursor ) + "<\cursor>"),, .F. )
dbStruct() = {} and I can´t read de fields QUERY->codpro and I can't read
the fields with GetField()  Scatter  etc..
now, when I use API funcions like AdsGetRecordNum(),  AdsGetFieldName (),
AdsGetFieldType(), etc... work fine
but
AdsGetField(), AdsGetFieldW(), AdsGetString(), AdsGet.....(), the values
*pucBuf and *pulLen return empties

with this statement I try to read each field of each record
nLen := 5
xRet := Space( nLen )
@ace32:AdsGetField( nCursor, 'codpro', @xRet, @nLen, ADS_NONE )
this command return
xRet = '' and nLen = 0

Is there any good practice to handle the results in this type of search in
several tables and handling of aliases in the expressions?

I would really appreciate your help
best regards!

Jorge
Matej JuracRe: ADS Problem width AdsGetField
on Mon, 29 Aug 2022 08:04:57 +0200
Might be far shot with DBF/CDX data, but:

Does ADS supports creating views and using it as data
or create temporary table from select on your data?

Both could solve problem you have.


Jorge L Borlando je 18.8.2022 ob 15:53 napisal:
> Hi to all
> 
> the clause AS don´t work in xbase
> 
> 
> Best Regards
> 
> "Jorge L Borlando"  escribió en el mensaje de 
> noticias:136e265e$4a6d3fc1$67cd0@news.alaska-software.com...
> 
> good morning colleagues
> 
> I am testing with ADS 10 using the API,
> the tables are DBFCDX defined in a dictionary ( don´t work with free 
> tables)
> 
> when I make complex queries such as:
> 
> SELECT SUBSTRING( d_c.resume, 1, 5 ) AS codpro,
>     IIF( LENGTH( TRIM( SUBSTRING( a_f.coment, 21, 12 ) ) ) = 0, SUBSTRING(
> art.coment, 21, 12 ), SUBSTRING( a_f.coment,  1, 12 ) ) Is there any good
> practice to handle the results in this type of search in several tables and
> handling of aliases in the expressions AS f_coment
> FROM stock_ AS art
>         LEFT JOIN artfor AS a_f ON (art.codigo=a_f.codigo)
>         LEFT JOIN detcom AS d_c ON (art.codigo=d_c.codigo)
>         LEFT JOIN provee AS pro ON (SUBSTRING( d_c.resume, 1, 5 ) =
> pro.codigo)
> WHERE art.codigo IN (345,4896,1090)
> GROUP BY codpro,f_coment
> 
> the result return 2 fields, codpro (CHAR,5) and f_coment (CHAR,12)
> 
> if I run this query in ARC32 utility I get the result correctly which is 5
> records.
> 
> When I run the query in my app using the folow sentences
> 
>       @ace32:AdsCreateSQLStatement( nHandle, @nStat )
>       @ace32:AdsStmtSetTableType( nStat, ADS_CDX )
>       @ace32:AdsStmtSetTableLockType( nStat, ADS_COMPATIBLE_LOCKING )
>       @ace32:AdsExecuteSQLDirect( nStat, cSQL, @nCursor )
> ...................
>       @ace32:AdsGetRecordCount( ::nCursor, ADS_IGNOREFILTERS, @nRecord ) =>
> nRecord = 5 too.
> 
> When I open the cursor with dbUseArea( .F., 'ADSDBE', ("<cursor>" + L2Bin(
> nCursor ) + "<\cursor>"),, .F. )
> dbStruct() = {} and I can´t read de fields QUERY->codpro and I can't read
> the fields with GetField()  Scatter  etc..
> now, when I use API funcions like AdsGetRecordNum(),  AdsGetFieldName (),
> AdsGetFieldType(), etc... work fine
> but
> AdsGetField(), AdsGetFieldW(), AdsGetString(), AdsGet.....(), the values
> *pucBuf and *pulLen return empties
> 
> with this statement I try to read each field of each record
> nLen := 5
> xRet := Space( nLen )
> @ace32:AdsGetField( nCursor, 'codpro', @xRet, @nLen, ADS_NONE )
> this command return
> xRet = '' and nLen = 0
> 
> Is there any good practice to handle the results in this type of search in
> several tables and handling of aliases in the expressions?
> 
> I would really appreciate your help
> best regards!
> 
> Jorge
Jorge L BorlandoRe: ADS Problem width AdsGetField
on Mon, 29 Aug 2022 20:21:22 -0300
Hi Matej

in my opinion it is a problem of the ace32.dll library that when I evaluate 
expressions in a SELECT and the AS clause
to name the column, I can't pull up the result

Also a similar and more serious problem when I use subqueries within a 
SELECT, or GROUP BY clause,
in these cases I can no longer directly read any column.

With the creation of intermediate views to obtain the final result instead 
of using a single statement
It has allowed me to solve the problem in addition to improving the 
performance of the execution of the query

in these cases I use the ordinal of the column and the function 
AdsGetFieldName() to get the name of the column
resulting

An ADS dictionary is an extraordinary solution that allows you to share 
alaska DBFCDX and ADS in read only (even with alaska indexes) with very 
surprising results when making queries



"Matej Jurac" escribió en el mensaje de 
noticias:5f50442d$744edf25$86d56@news.alaska-software.com...


Might be far shot with DBF/CDX data, but:

Does ADS supports creating views and using it as data
or create temporary table from select on your data?

Both could solve problem you have.


Jorge L Borlando je 18.8.2022 ob 15:53 napisal:
> Hi to all
>
> the clause AS don´t work in xbase
>
>
> Best Regards
>
> "Jorge L Borlando"  escribió en el mensaje de 
> noticias:136e265e$4a6d3fc1$67cd0@news.alaska-software.com...
>
> good morning colleagues
>
> I am testing with ADS 10 using the API,
> the tables are DBFCDX defined in a dictionary ( don´t work with free 
> tables)
>
> when I make complex queries such as:
>
> SELECT SUBSTRING( d_c.resume, 1, 5 ) AS codpro,
>     IIF( LENGTH( TRIM( SUBSTRING( a_f.coment, 21, 12 ) ) ) = 0, SUBSTRING(
> art.coment, 21, 12 ), SUBSTRING( a_f.coment,  1, 12 ) ) Is there any good
> practice to handle the results in this type of search in several tables 
> and
> handling of aliases in the expressions AS f_coment
> FROM stock_ AS art
>         LEFT JOIN artfor AS a_f ON (art.codigo=a_f.codigo)
>         LEFT JOIN detcom AS d_c ON (art.codigo=d_c.codigo)
>         LEFT JOIN provee AS pro ON (SUBSTRING( d_c.resume, 1, 5 ) =
> pro.codigo)
> WHERE art.codigo IN (345,4896,1090)
> GROUP BY codpro,f_coment
>
> the result return 2 fields, codpro (CHAR,5) and f_coment (CHAR,12)
>
> if I run this query in ARC32 utility I get the result correctly which is 5
> records.
>
> When I run the query in my app using the folow sentences
>
>       @ace32:AdsCreateSQLStatement( nHandle, @nStat )
>       @ace32:AdsStmtSetTableType( nStat, ADS_CDX )
>       @ace32:AdsStmtSetTableLockType( nStat, ADS_COMPATIBLE_LOCKING )
>       @ace32:AdsExecuteSQLDirect( nStat, cSQL, @nCursor )
> ...................
>       @ace32:AdsGetRecordCount( ::nCursor, ADS_IGNOREFILTERS, @nRecord ) 
> =>
> nRecord = 5 too.
>
> When I open the cursor with dbUseArea( .F., 'ADSDBE', ("<cursor>" + L2Bin(
> nCursor ) + "<\cursor>"),, .F. )
> dbStruct() = {} and I can´t read de fields QUERY->codpro and I can't read
> the fields with GetField()  Scatter  etc..
> now, when I use API funcions like AdsGetRecordNum(),  AdsGetFieldName (),
> AdsGetFieldType(), etc... work fine
> but
> AdsGetField(), AdsGetFieldW(), AdsGetString(), AdsGet.....(), the values
> *pucBuf and *pulLen return empties
>
> with this statement I try to read each field of each record
> nLen := 5
> xRet := Space( nLen )
> @ace32:AdsGetField( nCursor, 'codpro', @xRet, @nLen, ADS_NONE )
> this command return
> xRet = '' and nLen = 0
>
> Is there any good practice to handle the results in this type of search in
> several tables and handling of aliases in the expressions?
>
> I would really appreciate your help
> best regards!
>
> Jorge