Author | Topic: ADS Problem width AdsGetField | |
---|---|---|
![]() | Jorge L Borlando | ADS 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 Borlando | Re: 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 Jurac | Re: 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 Borlando | Re: 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 |