Hi,

We were using ADSDBE to connect to use ADS. Version 11.10. xBase
Version 1.9. using ADT tables with data dictionary.

Now we are planning to shit to ODBCDBE.

Please find the Sample below


#pragma Library( "Adac20b.lib" )
#include "sqlcmd.ch"

PROCEDURE DbeSys
   IF (! DbeLoad( "ODBCDBE" ))
      ? "Unable to load ODBCDBE !"
      Wait
      Quit
   ENDIF
   DbeSetDefault( "ODBCDBE" )
RETURN

PROCEDURE Main
   LOCAL cConnect, oSession

   cConnect := "DBE=ODBCDBE"
   cConnect += ";DSN=bkcb9"
   cConnect += ";UID=AdsSys"
   cConnect += ";PWD="

   oSession := DacSession():new( cConnect )

   IF .NOT. oSession:isConnected()
      ? "Unable to connect to server !"
      ? oSession:getLastMessage()
      Wait
      QUIT
   ENDIF
   
   USE REMARKB9 ALIAS EM
   
   EM->(DbSetOrder(1))
   EM->(DbSetScope("SH","SH")) ; EM->(DbGoTop())
   *
   SQL "SELECT * FROM REMARKB9 AS EM "
   *
   EM->(Browse())
   *
   CLOSE ALL
   oSession:disconnect()
RETURN


We are getting an error "Parameter has a wrong data type" when
executing DbSetScope(). When removing DbSetScope() line it works fine.

In the ODBC documentation, it says data type mapping. I didnt
understand much. 

Is this error because of Data Type Mapping ?

If so anybody can correct the code ?

A screenshot of DSN configuration and Table properties attached.


Regards
Kiron

Kiron,

>EM->(DbSetOrder(1))

You aren't opening any Index files, so DbSetOrder() won't do anything.

But your error is simply caused by a wrong parameter for your DbSetScope() 
function.

>EM->(DbSetScope("SH","SH")) ; EM->(DbGoTop())

The DbSetScope() function has two parameters. From the documentation:

	DbSetScope( <nScope>, <xValue> )

You should therefore change your code to:

EM->(DbSetScope(SCOPE_BOTH, "SH")) ; EM->(DbGoTop())

But keep in mind, without a corresponding open Index, DbSetScope() won't 
work as you expect, as it requires a Record Number, if no Index is 
available!

Hope that helps,

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

On Wed, 05 Jun 2019 09:57:10 -0400, Andreas Gehrs-Pahl wrote:

Andreas,

Thanks. It worked when added following configuration. 

oSession:setProperty(ODBCSSN_INDEX_AUTOOPEN,.T.)

But cursor moved down from the last record, again it shows an error

"Parameter has a wrong data type"

Any idea ?

Regards
Kiron


>Kiron,
>
>>EM->(DbSetOrder(1))
>
>You aren't opening any Index files, so DbSetOrder() won't do anything.
>
>But your error is simply caused by a wrong parameter for your DbSetScope() 
>function.
>
>>EM->(DbSetScope("SH","SH")) ; EM->(DbGoTop())
>
>The DbSetScope() function has two parameters. From the documentation:
>
>	DbSetScope( <nScope>, <xValue> )
>
>You should therefore change your code to:
>
>EM->(DbSetScope(SCOPE_BOTH, "SH")) ; EM->(DbGoTop())
>
>But keep in mind, without a corresponding open Index, DbSetScope() won't 
>work as you expect, as it requires a Record Number, if no Index is 
>available!
>
>Hope that helps,
>
>Andreas

hi Jacob
I've been trying to make only queries using the advantage API, and keep 
using my DBFCDX driver, try this generic example ....

   oConn := DacSession():New( "DBE=ADSDBE;SERVER=\\SERVIDOR\Datos\Sistema" )
   If !oConn:isConnected()
      return
   endif
   nHandle := oConn:getConnectionHandle()
   nCursor := 0
   nStat   := 0
   @ace32:AdsCreateSQLStatement( nHandle, @nStat )
   @ace32:AdsStmtSetTableType( nStat, ADS_CDX )                       
salida DBFCDX
   @ace32:AdsStmtSetTableReadOnly( nStat, ADS_CURSOR_READONLY )
   @ace32:AdsStmtSetTableLockType( nStat,ADS_COMPATIBLE_LOCKING )
   @ace32:AdsPrepareSQL( nStat, "SELECT * FROM detcom AS DC " +;
                                         "INNER JOIN stock_ AS art ON 
art.codigo = dc.codigo " )

*   @ace32:AdsPrepareSQL( nStat, "SELECT * FROM detcom " )  +;
*                                                 "FROM articu " 
+;
*                                                 "INNER JOIN provedor ON 
articu.marca_ = provedor.codigopro " +;
*                                                 "INNER JOIN stkcod ON 
articu.codint = stkcod.codint AND stkcod.ubicac = '1' "        +;
*                                                 "WHERE articu.descri LIKE 
'%EYE%' "                          +;
*                                                 "ORDER BY articu.descri 
" )
   b1 := Seconds()
   @ace32:AdsExecuteSQL( nStat, @nCursor )
   b1 := Seconds() - b1
   b1 := 0
   b2 := Space( 1000 )
   b3 := 1000
   @ace32:AdsGetLastError( @b1, @b2, @b3 )
   USE ( "<CURSOR>"+L2BIN( nCursor )+"<\CURSOR>") NEW
   @ace32:AdsCloseSQLStatement( nStat )
   @ace32:AdsDisconnect( nHandle )
   inkey(0)


"Jacob John" wrote in message 
news:539ffel4k8t47u39l5e1afgfua9lgm9au8@4ax.com...
> Hi,
>
> We were using ADSDBE to connect to use ADS. Version 11.10. xBase
> Version 1.9. using ADT tables with data dictionary.
>
> Now we are planning to shit to ODBCDBE.
>
> Please find the Sample below
>
>
> #pragma Library( "Adac20b.lib" )
> #include "sqlcmd.ch"
>
> PROCEDURE DbeSys
>   IF (! DbeLoad( "ODBCDBE" ))
>      ? "Unable to load ODBCDBE !"
>      Wait
>      Quit
>   ENDIF
>   DbeSetDefault( "ODBCDBE" )
> RETURN
>
> PROCEDURE Main
>   LOCAL cConnect, oSession
>
>   cConnect := "DBE=ODBCDBE"
>   cConnect += ";DSN=bkcb9"
>   cConnect += ";UID=AdsSys"
>   cConnect += ";PWD="
>
>   oSession := DacSession():new( cConnect )
>
>   IF .NOT. oSession:isConnected()
>      ? "Unable to connect to server !"
>      ? oSession:getLastMessage()
>      Wait
>      QUIT
>   ENDIF
>   *
>   USE REMARKB9 ALIAS EM
>   *
>   EM->(DbSetOrder(1))
>   EM->(DbSetScope("SH","SH")) ; EM->(DbGoTop())
>   *
>   SQL "SELECT * FROM REMARKB9 AS EM "
>   *
>   EM->(Browse())
>   *
>   CLOSE ALL
>   oSession:disconnect()
> RETURN
>
>
> We are getting an error "Parameter has a wrong data type" when
> executing DbSetScope(). When removing DbSetScope() line it works fine.
>
> In the ODBC documentation, it says data type mapping. I didnt
> understand much.
>
> Is this error because of Data Type Mapping ?
>
> If so anybody can correct the code ?
>
> A screenshot of DSN configuration and Table properties attached.
>
>
> Regards
> Kiron
>

El 05/06/2019 a las 13:25, Jacob John escribió:
> Hi,
> 
> We were using ADSDBE to connect to use ADS. Version 11.10. xBase
> Version 1.9. using ADT tables with data dictionary.
> 
> Now we are planning to shit to ODBCDBE.
> 
> Please find the Sample below
> 
> 
> #pragma Library( "Adac20b.lib" )
> #include "sqlcmd.ch"
> 
> PROCEDURE DbeSys
>     IF (! DbeLoad( "ODBCDBE" ))
>        ? "Unable to load ODBCDBE !"
>        Wait
>        Quit
>     ENDIF
>     DbeSetDefault( "ODBCDBE" )
> RETURN
> 
> PROCEDURE Main
>     LOCAL cConnect, oSession
> 
>     cConnect := "DBE=ODBCDBE"
>     cConnect += ";DSN=bkcb9"
>     cConnect += ";UID=AdsSys"
>     cConnect += ";PWD="
> 
>     oSession := DacSession():new( cConnect )
> 
>     IF .NOT. oSession:isConnected()
>        ? "Unable to connect to server !"
>        ? oSession:getLastMessage()
>        Wait
>        QUIT
>     ENDIF
>     *
>     USE REMARKB9 ALIAS EM
>     *
>     EM->(DbSetOrder(1))
>     EM->(DbSetScope("SH","SH")) ; EM->(DbGoTop())
>     *
>     SQL "SELECT * FROM REMARKB9 AS EM "
>     *
>     EM->(Browse())
>     *
>     CLOSE ALL
>     oSession:disconnect()
> RETURN
> 
> 
> We are getting an error "Parameter has a wrong data type" when
> executing DbSetScope(). When removing DbSetScope() line it works fine.
> 
> In the ODBC documentation, it says data type mapping. I didnt
> understand much.
> 
> Is this error because of Data Type Mapping ?
> 
> If so anybody can correct the code ?
> 
> A screenshot of DSN configuration and Table properties attached.
> 
> 
> Regards
> Kiron
> 

Try like this:

cSrv := "ADS_REMOTE_SERVER"
cRut := "\\ceca\DBFS\TID\"
cDic := "A_DICTELID.ADD"
cUsu := "usuario"
cPas := "clave"

cSrv := "ADS_REMOTE_SERVER"

cCon := "DBE=ODBCDBE;DRIVER={Advantage StreamlineSQL ODBC};"
cCon += "DataDirectory=" + cRut + cDic + ";" + "SERVER=" + cSrv + ";" + 
"UID=" + cUsu  + ";" + "PWD=" + cPas + ";"
cCon += 
"Compresion=;DefaultType=Advantage;Rows=False;Language=ANSI;AdvantageLocking=OFF;Locking=Record;MemoBlockSize=524288;MaxTableCloseCache=100;ServerTypes=6;"
cCon += 
"TrimTrailingSpaces=False;EncrytionType=RC4;FIPS=False;RightsChecking=OFF;"

oSes := dacSession():New(cCon)

IF ! oSes:isConnected()
    Infobox("No Conectado")
    RETURN
    ELSE

    oSes:setProperty(ODBCSSN_INDEX_AUTOOPEN, .T.)

    cSQL := "SELECT MAN,SOC,CUE FROM MAYOR ORDER BY CUE"
    cSQL := "SELECT DOC,EJE,REF,ACR,imp FROM PROVFRAS ORDER BY ACR"
    cSQL := "SELECT * FROM MAYOR ORDER BY CUE;"

    oSrv := OpenOdbcServer(cSQL,oSes)

    oSrv:DAC_Activate(.F.,.T.)
.
.
.
.
.
.


ENDIF

This source code works fine for me.

Regards.

Hi,

It is working fine when added following Line

oSession:setProperty(ODBCSSN_INDEX_AUTOOPEN,.T.)

But again getting an error when setting a scope. When setting the
scope. 

The error shows when the cursor move down from the last record. Shows
different error each time.

Some times  shows
"Parameter has a wrong data type"

Sometimes "Error in array index"


Please see the full code

//-------------------------------------------------------------------------//

#pragma Library( "Adac20b.lib" )
#include "sqlcmd.ch"
#include "odbcdbe.ch"

PROCEDURE DbeSys
   IF (! DbeLoad( "ODBCDBE" ))
      ? "Unable to load ODBCDBE !"
      Wait
      Quit
   ENDIF
   DbeSetDefault( "ODBCDBE" )
RETURN

PROCEDURE Main
   LOCAL cConnect, oSession

   cConnect := "DBE=ODBCDBE"
   cConnect += ";DSN=bkcb9"
   cConnect += ";UID=AdsSys"
   cConnect += ";PWD="

   SET NULLVALUE OFF

   oSession := DacSession():new( cConnect )
   oSession:setProperty(ODBCSSN_TIMESTAMP_AS_DATE, .T.)
   oSession:setProperty(ODBCSSN_INDEX_AUTOOPEN,.T.)
   
   USE EMPMSTB9 ALIAS EM
   
   EM->(DbSetOrder(1))
   SET SCOPE TO "AAA","AAA" ; EM->(DbGoTop())
   *
   SQL "SELECT CODE,FIRSTNAME,DOB FROM EMPMSTB9 AS EM WHERE
NATIONALIT='IND'AND TRADE='T01'"
   *
   EM->(Browse())
   *
   CLOSE ALL
   oSession:disconnect()
RETURN

//-------------------------------------------------------------------------//

Thanks in advance.

Regards
Kiron



On Fri, 07 Jun 2019 17:06:31 +0200, César Calvo
<ccalvoc@telefonica.net> wrote:

>El 05/06/2019 a las 13:25, Jacob John escribió:
>> Hi,
>> 
>> We were using ADSDBE to connect to use ADS. Version 11.10. xBase
>> Version 1.9. using ADT tables with data dictionary.
>> 
>> Now we are planning to shit to ODBCDBE.
>> 
>> Please find the Sample below
>> 
>> 
>> #pragma Library( "Adac20b.lib" )
>> #include "sqlcmd.ch"
>> 
>> PROCEDURE DbeSys
>>     IF (! DbeLoad( "ODBCDBE" ))
>>        ? "Unable to load ODBCDBE !"
>>        Wait
>>        Quit
>>     ENDIF
>>     DbeSetDefault( "ODBCDBE" )
>> RETURN
>> 
>> PROCEDURE Main
>>     LOCAL cConnect, oSession
>> 
>>     cConnect := "DBE=ODBCDBE"
>>     cConnect += ";DSN=bkcb9"
>>     cConnect += ";UID=AdsSys"
>>     cConnect += ";PWD="
>> 
>>     oSession := DacSession():new( cConnect )
>> 
>>     IF .NOT. oSession:isConnected()
>>        ? "Unable to connect to server !"
>>        ? oSession:getLastMessage()
>>        Wait
>>        QUIT
>>     ENDIF
>>     *
>>     USE REMARKB9 ALIAS EM
>>     *
>>     EM->(DbSetOrder(1))
>>     EM->(DbSetScope("SH","SH")) ; EM->(DbGoTop())
>>     *
>>     SQL "SELECT * FROM REMARKB9 AS EM "
>>     *
>>     EM->(Browse())
>>     *
>>     CLOSE ALL
>>     oSession:disconnect()
>> RETURN
>> 
>> 
>> We are getting an error "Parameter has a wrong data type" when
>> executing DbSetScope(). When removing DbSetScope() line it works fine.
>> 
>> In the ODBC documentation, it says data type mapping. I didnt
>> understand much.
>> 
>> Is this error because of Data Type Mapping ?
>> 
>> If so anybody can correct the code ?
>> 
>> A screenshot of DSN configuration and Table properties attached.
>> 
>> 
>> Regards
>> Kiron
>> 
>
>Try like this:
>
>cSrv := "ADS_REMOTE_SERVER"
>cRut := "\\ceca\DBFS\TID\"
>cDic := "A_DICTELID.ADD"
>cUsu := "usuario"
>cPas := "clave"
>
>cSrv := "ADS_REMOTE_SERVER"
>
>cCon := "DBE=ODBCDBE;DRIVER={Advantage StreamlineSQL ODBC};"
>cCon += "DataDirectory=" + cRut + cDic + ";" + "SERVER=" + cSrv + ";" + 
>"UID=" + cUsu  + ";" + "PWD=" + cPas + ";"
>cCon += 
>"Compresion=;DefaultType=Advantage;Rows=False;Language=ANSI;AdvantageLocking=OFF;Locking=Record;MemoBlockSize=524288;MaxTableCloseCache=100;ServerTypes=6;"
>cCon += 
>"TrimTrailingSpaces=False;EncrytionType=RC4;FIPS=False;RightsChecking=OFF;"
>
>oSes := dacSession():New(cCon)
>
>IF ! oSes:isConnected()
>    Infobox("No Conectado")
>    RETURN
>    ELSE
>
>    oSes:setProperty(ODBCSSN_INDEX_AUTOOPEN, .T.)
>
>    cSQL := "SELECT MAN,SOC,CUE FROM MAYOR ORDER BY CUE"
>    cSQL := "SELECT DOC,EJE,REF,ACR,imp FROM PROVFRAS ORDER BY ACR"
>    cSQL := "SELECT * FROM MAYOR ORDER BY CUE;"
>
>    oSrv := OpenOdbcServer(cSQL,oSes)
>
>    oSrv:DAC_Activate(.F.,.T.)
>.
>.
>.
>.
>.
>.
>
>
>ENDIF
>
>This source code works fine for me.
>
>Regards.

El 10/06/2019 a las 8:38, Jacob John escribió:
> Hi,
> 
> It is working fine when added following Line
> 
> oSession:setProperty(ODBCSSN_INDEX_AUTOOPEN,.T.)
> 
> But again getting an error when setting a scope. When setting the
> scope.
> 
> The error shows when the cursor move down from the last record. Shows
> different error each time.
> 
> Some times  shows
> "Parameter has a wrong data type"
> 
> Sometimes "Error in array index"
> 
> 
> Please see the full code
> 
> //-------------------------------------------------------------------------//
> 
> #pragma Library( "Adac20b.lib" )
> #include "sqlcmd.ch"
> #include "odbcdbe.ch"
> 
> PROCEDURE DbeSys
>     IF (! DbeLoad( "ODBCDBE" ))
>        ? "Unable to load ODBCDBE !"
>        Wait
>        Quit
>     ENDIF
>     DbeSetDefault( "ODBCDBE" )
> RETURN
> 
> PROCEDURE Main
>     LOCAL cConnect, oSession
> 
>     cConnect := "DBE=ODBCDBE"
>     cConnect += ";DSN=bkcb9"
>     cConnect += ";UID=AdsSys"
>     cConnect += ";PWD="
> 
>     SET NULLVALUE OFF
> 
>     oSession := DacSession():new( cConnect )
>     oSession:setProperty(ODBCSSN_TIMESTAMP_AS_DATE, .T.)
>     oSession:setProperty(ODBCSSN_INDEX_AUTOOPEN,.T.)
>     *
>     USE EMPMSTB9 ALIAS EM
>     *
>     EM->(DbSetOrder(1))
>     SET SCOPE TO "AAA","AAA" ; EM->(DbGoTop())
>     *
>     SQL "SELECT CODE,FIRSTNAME,DOB FROM EMPMSTB9 AS EM WHERE
> NATIONALIT='IND'AND TRADE='T01'"
>     *
>     EM->(Browse())
>     *
>     CLOSE ALL
>     oSession:disconnect()
> RETURN
> 
> //-------------------------------------------------------------------------//
> 
> Thanks in advance.
> 
> Regards
> Kiron
> 
> 
> 
> On Fri, 07 Jun 2019 17:06:31 +0200, César Calvo
> <ccalvoc@telefonica.net> wrote:
> 
>> El 05/06/2019 a las 13:25, Jacob John escribió:
>>> Hi,
>>>
>>> We were using ADSDBE to connect to use ADS. Version 11.10. xBase
>>> Version 1.9. using ADT tables with data dictionary.
>>>
>>> Now we are planning to shit to ODBCDBE.
>>>
>>> Please find the Sample below
>>>
>>>
>>> #pragma Library( "Adac20b.lib" )
>>> #include "sqlcmd.ch"
>>>
>>> PROCEDURE DbeSys
>>>      IF (! DbeLoad( "ODBCDBE" ))
>>>         ? "Unable to load ODBCDBE !"
>>>         Wait
>>>         Quit
>>>      ENDIF
>>>      DbeSetDefault( "ODBCDBE" )
>>> RETURN
>>>
>>> PROCEDURE Main
>>>      LOCAL cConnect, oSession
>>>
>>>      cConnect := "DBE=ODBCDBE"
>>>      cConnect += ";DSN=bkcb9"
>>>      cConnect += ";UID=AdsSys"
>>>      cConnect += ";PWD="
>>>
>>>      oSession := DacSession():new( cConnect )
>>>
>>>      IF .NOT. oSession:isConnected()
>>>         ? "Unable to connect to server !"
>>>         ? oSession:getLastMessage()
>>>         Wait
>>>         QUIT
>>>      ENDIF
>>>      *
>>>      USE REMARKB9 ALIAS EM
>>>      *
>>>      EM->(DbSetOrder(1))
>>>      EM->(DbSetScope("SH","SH")) ; EM->(DbGoTop())
>>>      *
>>>      SQL "SELECT * FROM REMARKB9 AS EM "
>>>      *
>>>      EM->(Browse())
>>>      *
>>>      CLOSE ALL
>>>      oSession:disconnect()
>>> RETURN
>>>
>>>
>>> We are getting an error "Parameter has a wrong data type" when
>>> executing DbSetScope(). When removing DbSetScope() line it works fine.
>>>
>>> In the ODBC documentation, it says data type mapping. I didnt
>>> understand much.
>>>
>>> Is this error because of Data Type Mapping ?
>>>
>>> If so anybody can correct the code ?
>>>
>>> A screenshot of DSN configuration and Table properties attached.
>>>
>>>
>>> Regards
>>> Kiron
>>>
>>
>> Try like this:
>>
>> cSrv := "ADS_REMOTE_SERVER"
>> cRut := "\\ceca\DBFS\TID\"
>> cDic := "A_DICTELID.ADD"
>> cUsu := "usuario"
>> cPas := "clave"
>>
>> cSrv := "ADS_REMOTE_SERVER"
>>
>> cCon := "DBE=ODBCDBE;DRIVER={Advantage StreamlineSQL ODBC};"
>> cCon += "DataDirectory=" + cRut + cDic + ";" + "SERVER=" + cSrv + ";" +
>> "UID=" + cUsu  + ";" + "PWD=" + cPas + ";"
>> cCon +=
>> "Compresion=;DefaultType=Advantage;Rows=False;Language=ANSI;AdvantageLocking=OFF;Locking=Record;MemoBlockSize=524288;MaxTableCloseCache=100;ServerTypes=6;"
>> cCon +=
>> "TrimTrailingSpaces=False;EncrytionType=RC4;FIPS=False;RightsChecking=OFF;"
>>
>> oSes := dacSession():New(cCon)
>>
>> IF ! oSes:isConnected()
>>     Infobox("No Conectado")
>>     RETURN
>>     ELSE
>>
>>     oSes:setProperty(ODBCSSN_INDEX_AUTOOPEN, .T.)
>>
>>     cSQL := "SELECT MAN,SOC,CUE FROM MAYOR ORDER BY CUE"
>>     cSQL := "SELECT DOC,EJE,REF,ACR,imp FROM PROVFRAS ORDER BY ACR"
>>     cSQL := "SELECT * FROM MAYOR ORDER BY CUE;"
>>
>>     oSrv := OpenOdbcServer(cSQL,oSes)
>>
>>     oSrv:DAC_Activate(.F.,.T.)
>> .
>> .
>> .
>> .
>> .
>> .
>>
>>
>> ENDIF
>>
>> This source code works fine for me.
>>
>> Regards.


Kiron, I will try to check it this week.

Regards.
César Calvo.

Ok. Thanks

On Tue, 11 Jun 2019 19:09:58 +0200, César Calvo
<ccalvoc@telefonica.net> wrote:

>El 10/06/2019 a las 8:38, Jacob John escribió:
>> Hi,
>> 
>> It is working fine when added following Line
>> 
>> oSession:setProperty(ODBCSSN_INDEX_AUTOOPEN,.T.)
>> 
>> But again getting an error when setting a scope. When setting the
>> scope.
>> 
>> The error shows when the cursor move down from the last record. Shows
>> different error each time.
>> 
>> Some times  shows
>> "Parameter has a wrong data type"
>> 
>> Sometimes "Error in array index"
>> 
>> 
>> Please see the full code
>> 
>> //-------------------------------------------------------------------------//
>> 
>> #pragma Library( "Adac20b.lib" )
>> #include "sqlcmd.ch"
>> #include "odbcdbe.ch"
>> 
>> PROCEDURE DbeSys
>>     IF (! DbeLoad( "ODBCDBE" ))
>>        ? "Unable to load ODBCDBE !"
>>        Wait
>>        Quit
>>     ENDIF
>>     DbeSetDefault( "ODBCDBE" )
>> RETURN
>> 
>> PROCEDURE Main
>>     LOCAL cConnect, oSession
>> 
>>     cConnect := "DBE=ODBCDBE"
>>     cConnect += ";DSN=bkcb9"
>>     cConnect += ";UID=AdsSys"
>>     cConnect += ";PWD="
>> 
>>     SET NULLVALUE OFF
>> 
>>     oSession := DacSession():new( cConnect )
>>     oSession:setProperty(ODBCSSN_TIMESTAMP_AS_DATE, .T.)
>>     oSession:setProperty(ODBCSSN_INDEX_AUTOOPEN,.T.)
>>     *
>>     USE EMPMSTB9 ALIAS EM
>>     *
>>     EM->(DbSetOrder(1))
>>     SET SCOPE TO "AAA","AAA" ; EM->(DbGoTop())
>>     *
>>     SQL "SELECT CODE,FIRSTNAME,DOB FROM EMPMSTB9 AS EM WHERE
>> NATIONALIT='IND'AND TRADE='T01'"
>>     *
>>     EM->(Browse())
>>     *
>>     CLOSE ALL
>>     oSession:disconnect()
>> RETURN
>> 
>> //-------------------------------------------------------------------------//
>> 
>> Thanks in advance.
>> 
>> Regards
>> Kiron
>> 
>> 
>> 
>> On Fri, 07 Jun 2019 17:06:31 +0200, César Calvo
>> <ccalvoc@telefonica.net> wrote:
>> 
>>> El 05/06/2019 a las 13:25, Jacob John escribió:
>>>> Hi,
>>>>
>>>> We were using ADSDBE to connect to use ADS. Version 11.10. xBase
>>>> Version 1.9. using ADT tables with data dictionary.
>>>>
>>>> Now we are planning to shit to ODBCDBE.
>>>>
>>>> Please find the Sample below
>>>>
>>>>
>>>> #pragma Library( "Adac20b.lib" )
>>>> #include "sqlcmd.ch"
>>>>
>>>> PROCEDURE DbeSys
>>>>      IF (! DbeLoad( "ODBCDBE" ))
>>>>         ? "Unable to load ODBCDBE !"
>>>>         Wait
>>>>         Quit
>>>>      ENDIF
>>>>      DbeSetDefault( "ODBCDBE" )
>>>> RETURN
>>>>
>>>> PROCEDURE Main
>>>>      LOCAL cConnect, oSession
>>>>
>>>>      cConnect := "DBE=ODBCDBE"
>>>>      cConnect += ";DSN=bkcb9"
>>>>      cConnect += ";UID=AdsSys"
>>>>      cConnect += ";PWD="
>>>>
>>>>      oSession := DacSession():new( cConnect )
>>>>
>>>>      IF .NOT. oSession:isConnected()
>>>>         ? "Unable to connect to server !"
>>>>         ? oSession:getLastMessage()
>>>>         Wait
>>>>         QUIT
>>>>      ENDIF
>>>>      *
>>>>      USE REMARKB9 ALIAS EM
>>>>      *
>>>>      EM->(DbSetOrder(1))
>>>>      EM->(DbSetScope("SH","SH")) ; EM->(DbGoTop())
>>>>      *
>>>>      SQL "SELECT * FROM REMARKB9 AS EM "
>>>>      *
>>>>      EM->(Browse())
>>>>      *
>>>>      CLOSE ALL
>>>>      oSession:disconnect()
>>>> RETURN
>>>>
>>>>
>>>> We are getting an error "Parameter has a wrong data type" when
>>>> executing DbSetScope(). When removing DbSetScope() line it works fine.
>>>>
>>>> In the ODBC documentation, it says data type mapping. I didnt
>>>> understand much.
>>>>
>>>> Is this error because of Data Type Mapping ?
>>>>
>>>> If so anybody can correct the code ?
>>>>
>>>> A screenshot of DSN configuration and Table properties attached.
>>>>
>>>>
>>>> Regards
>>>> Kiron
>>>>
>>>
>>> Try like this:
>>>
>>> cSrv := "ADS_REMOTE_SERVER"
>>> cRut := "\\ceca\DBFS\TID\"
>>> cDic := "A_DICTELID.ADD"
>>> cUsu := "usuario"
>>> cPas := "clave"
>>>
>>> cSrv := "ADS_REMOTE_SERVER"
>>>
>>> cCon := "DBE=ODBCDBE;DRIVER={Advantage StreamlineSQL ODBC};"
>>> cCon += "DataDirectory=" + cRut + cDic + ";" + "SERVER=" + cSrv + ";" +
>>> "UID=" + cUsu  + ";" + "PWD=" + cPas + ";"
>>> cCon +=
>>> "Compresion=;DefaultType=Advantage;Rows=False;Language=ANSI;AdvantageLocking=OFF;Locking=Record;MemoBlockSize=524288;MaxTableCloseCache=100;ServerTypes=6;"
>>> cCon +=
>>> "TrimTrailingSpaces=False;EncrytionType=RC4;FIPS=False;RightsChecking=OFF;"
>>>
>>> oSes := dacSession():New(cCon)
>>>
>>> IF ! oSes:isConnected()
>>>     Infobox("No Conectado")
>>>     RETURN
>>>     ELSE
>>>
>>>     oSes:setProperty(ODBCSSN_INDEX_AUTOOPEN, .T.)
>>>
>>>     cSQL := "SELECT MAN,SOC,CUE FROM MAYOR ORDER BY CUE"
>>>     cSQL := "SELECT DOC,EJE,REF,ACR,imp FROM PROVFRAS ORDER BY ACR"
>>>     cSQL := "SELECT * FROM MAYOR ORDER BY CUE;"
>>>
>>>     oSrv := OpenOdbcServer(cSQL,oSes)
>>>
>>>     oSrv:DAC_Activate(.F.,.T.)
>>> .
>>> .
>>> .
>>> .
>>> .
>>> .
>>>
>>>
>>> ENDIF
>>>
>>> This source code works fine for me.
>>>
>>> Regards.
>
>
>Kiron, I will try to check it this week.
>
>Regards.
>César Calvo.

El 13/06/2019 a las 7:53, Jacob John escribió:
> Ok. Thanks
> 
> On Tue, 11 Jun 2019 19:09:58 +0200, César Calvo
> <ccalvoc@telefonica.net> wrote:
> 
>> El 10/06/2019 a las 8:38, Jacob John escribió:
>>> Hi,
>>>
>>> It is working fine when added following Line
>>>
>>> oSession:setProperty(ODBCSSN_INDEX_AUTOOPEN,.T.)
>>>
>>> But again getting an error when setting a scope. When setting the
>>> scope.
>>>
>>> The error shows when the cursor move down from the last record. Shows
>>> different error each time.
>>>
>>> Some times  shows
>>> "Parameter has a wrong data type"
>>>
>>> Sometimes "Error in array index"
>>>
>>>
>>> Please see the full code
>>>
>>> //-------------------------------------------------------------------------//
>>>
>>> #pragma Library( "Adac20b.lib" )
>>> #include "sqlcmd.ch"
>>> #include "odbcdbe.ch"
>>>
>>> PROCEDURE DbeSys
>>>      IF (! DbeLoad( "ODBCDBE" ))
>>>         ? "Unable to load ODBCDBE !"
>>>         Wait
>>>         Quit
>>>      ENDIF
>>>      DbeSetDefault( "ODBCDBE" )
>>> RETURN
>>>
>>> PROCEDURE Main
>>>      LOCAL cConnect, oSession
>>>
>>>      cConnect := "DBE=ODBCDBE"
>>>      cConnect += ";DSN=bkcb9"
>>>      cConnect += ";UID=AdsSys"
>>>      cConnect += ";PWD="
>>>
>>>      SET NULLVALUE OFF
>>>
>>>      oSession := DacSession():new( cConnect )
>>>      oSession:setProperty(ODBCSSN_TIMESTAMP_AS_DATE, .T.)
>>>      oSession:setProperty(ODBCSSN_INDEX_AUTOOPEN,.T.)
>>>      *
>>>      USE EMPMSTB9 ALIAS EM
>>>      *
>>>      EM->(DbSetOrder(1))
>>>      SET SCOPE TO "AAA","AAA" ; EM->(DbGoTop())
>>>      *
>>>      SQL "SELECT CODE,FIRSTNAME,DOB FROM EMPMSTB9 AS EM WHERE
>>> NATIONALIT='IND'AND TRADE='T01'"
>>>      *
>>>      EM->(Browse())
>>>      *
>>>      CLOSE ALL
>>>      oSession:disconnect()
>>> RETURN
>>>
>>> //-------------------------------------------------------------------------//
>>>
>>> Thanks in advance.
>>>
>>> Regards
>>> Kiron
>>>
>>>
>>>
>>> On Fri, 07 Jun 2019 17:06:31 +0200, César Calvo
>>> <ccalvoc@telefonica.net> wrote:
>>>
>>>> El 05/06/2019 a las 13:25, Jacob John escribió:
>>>>> Hi,
>>>>>
>>>>> We were using ADSDBE to connect to use ADS. Version 11.10. xBase
>>>>> Version 1.9. using ADT tables with data dictionary.
>>>>>
>>>>> Now we are planning to shit to ODBCDBE.
>>>>>
>>>>> Please find the Sample below
>>>>>
>>>>>
>>>>> #pragma Library( "Adac20b.lib" )
>>>>> #include "sqlcmd.ch"
>>>>>
>>>>> PROCEDURE DbeSys
>>>>>       IF (! DbeLoad( "ODBCDBE" ))
>>>>>          ? "Unable to load ODBCDBE !"
>>>>>          Wait
>>>>>          Quit
>>>>>       ENDIF
>>>>>       DbeSetDefault( "ODBCDBE" )
>>>>> RETURN
>>>>>
>>>>> PROCEDURE Main
>>>>>       LOCAL cConnect, oSession
>>>>>
>>>>>       cConnect := "DBE=ODBCDBE"
>>>>>       cConnect += ";DSN=bkcb9"
>>>>>       cConnect += ";UID=AdsSys"
>>>>>       cConnect += ";PWD="
>>>>>
>>>>>       oSession := DacSession():new( cConnect )
>>>>>
>>>>>       IF .NOT. oSession:isConnected()
>>>>>          ? "Unable to connect to server !"
>>>>>          ? oSession:getLastMessage()
>>>>>          Wait
>>>>>          QUIT
>>>>>       ENDIF
>>>>>       *
>>>>>       USE REMARKB9 ALIAS EM
>>>>>       *
>>>>>       EM->(DbSetOrder(1))
>>>>>       EM->(DbSetScope("SH","SH")) ; EM->(DbGoTop())
>>>>>       *
>>>>>       SQL "SELECT * FROM REMARKB9 AS EM "
>>>>>       *
>>>>>       EM->(Browse())
>>>>>       *
>>>>>       CLOSE ALL
>>>>>       oSession:disconnect()
>>>>> RETURN
>>>>>
>>>>>
>>>>> We are getting an error "Parameter has a wrong data type" when
>>>>> executing DbSetScope(). When removing DbSetScope() line it works fine.
>>>>>
>>>>> In the ODBC documentation, it says data type mapping. I didnt
>>>>> understand much.
>>>>>
>>>>> Is this error because of Data Type Mapping ?
>>>>>
>>>>> If so anybody can correct the code ?
>>>>>
>>>>> A screenshot of DSN configuration and Table properties attached.
>>>>>
>>>>>
>>>>> Regards
>>>>> Kiron
>>>>>
>>>>
>>>> Try like this:
>>>>
>>>> cSrv := "ADS_REMOTE_SERVER"
>>>> cRut := "\\ceca\DBFS\TID\"
>>>> cDic := "A_DICTELID.ADD"
>>>> cUsu := "usuario"
>>>> cPas := "clave"
>>>>
>>>> cSrv := "ADS_REMOTE_SERVER"
>>>>
>>>> cCon := "DBE=ODBCDBE;DRIVER={Advantage StreamlineSQL ODBC};"
>>>> cCon += "DataDirectory=" + cRut + cDic + ";" + "SERVER=" + cSrv + ";" +
>>>> "UID=" + cUsu  + ";" + "PWD=" + cPas + ";"
>>>> cCon +=
>>>> "Compresion=;DefaultType=Advantage;Rows=False;Language=ANSI;AdvantageLocking=OFF;Locking=Record;MemoBlockSize=524288;MaxTableCloseCache=100;ServerTypes=6;"
>>>> cCon +=
>>>> "TrimTrailingSpaces=False;EncrytionType=RC4;FIPS=False;RightsChecking=OFF;"
>>>>
>>>> oSes := dacSession():New(cCon)
>>>>
>>>> IF ! oSes:isConnected()
>>>>      Infobox("No Conectado")
>>>>      RETURN
>>>>      ELSE
>>>>
>>>>      oSes:setProperty(ODBCSSN_INDEX_AUTOOPEN, .T.)
>>>>
>>>>      cSQL := "SELECT MAN,SOC,CUE FROM MAYOR ORDER BY CUE"
>>>>      cSQL := "SELECT DOC,EJE,REF,ACR,imp FROM PROVFRAS ORDER BY ACR"
>>>>      cSQL := "SELECT * FROM MAYOR ORDER BY CUE;"
>>>>
>>>>      oSrv := OpenOdbcServer(cSQL,oSes)
>>>>
>>>>      oSrv:DAC_Activate(.F.,.T.)
>>>> .
>>>> .
>>>> .
>>>> .
>>>> .
>>>> .
>>>>
>>>>
>>>> ENDIF
>>>>
>>>> This source code works fine for me.
>>>>
>>>> Regards.
>>
>>
>> Kiron, I will try to check it this week.
>>
>> Regards.
>> César Calvo.

Dear Kiron, for me the best way for ODBC is SqlExpress of Boris Borzic
https://www.xb2.net/menu.htm)
But for ADS there are great products of ds-Datasoft that work relly good 
like AdsClass++
SqlXppClass if you want work SqlExpress together with XClass++.
I hae this all products and are fantastic.
When I can try your code and I see what happens.
Regards.

Kiron,

Out of curiosity, why do you want to switch from the ADSDBE -- which is 
ISAM, just as your code, using Index files, etc. -- to the ODBCDBE, which is 
SQL-oriented, even though it can emulate some ISAM features. If you switch 
to SQL-only code, it might make sense, but you use things like DbUseArea(), 
DbSetOrder(), DbSetScope(), and DbGoTop() -- or their command equivalents -- 
which are all ISAM features, for which the ADSDBE would be much more 
appropriate than the ODBCDBE?

Anywhere, your code:

>USE EMPMSTB9 ALIAS EM

Opens a database table named "EMPMSTB9" with Alias "EM". You also select an 
Index and set a scope, specifying the order and limit which records are 
visible.

Then you open a second Workarea with:

>SQL "SELECT CODE,FIRSTNAME,DOB FROM EMPMSTB9 AS EM WHERE 
>NATIONALIT='IND'AND TRADE='T01'"

This opens the same database table "EMPMSTB9" in a new Workarea, selects a 
subset of fields and a different subset of records, and then you show the 
first Workarea in a Browse table.

My questions are: Why do you use "AS EM" in the Select command? Do you 
somehow expect that the SQL command uses a subset of the original "EM" 
Workarea, or why do you open that second Workarea in the first place?

The "SQL..." line is not having any effect on the "EM->(Browse())" line 
of code, so why is it there?

You don't post a reproducible example (code, data, and environment info) or 
your actual Error logs, just code snippets and screen shots, so it is very 
difficult (if not impossible) to help you with this, I'm afraid.

Please tell us what you are trying to do -- and why you want to use the 
ODBCDBE instead of the ADSDBE.

Thanks,

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

Hi,

We are using ADSDBE for years. There was a problem in ADSDBE for xBase
1.9 (PDR 6199). Alaska gave us a fix for that. And it is working fine.
Now we have purchased xBase 2.0. But somehow Alaska forgot to include
that fix in 2.0. We reported this error to Alaska. They confirmed it.
They are working on that. But it will take time. So we thought we may
shift to ODBC. But I think PDR 6199 is applicable for ODBC too.

Our's is a large application. So it is possible to change ADSDBE
immediately. So we thought, by the time we will create a ODBCDBE and
will use it where ever necessary along with ADSDBE.

Regards
Kiron


On Fri, 14 Jun 2019 16:07:47 -0400, Andreas Gehrs-Pahl wrote:

>Kiron,
>
>Out of curiosity, why do you want to switch from the ADSDBE -- which is 
>ISAM, just as your code, using Index files, etc. -- to the ODBCDBE, which is 
>SQL-oriented, even though it can emulate some ISAM features. If you switch 
>to SQL-only code, it might make sense, but you use things like DbUseArea(), 
>DbSetOrder(), DbSetScope(), and DbGoTop() -- or their command equivalents -- 
>which are all ISAM features, for which the ADSDBE would be much more 
>appropriate than the ODBCDBE?
>
>Anywhere, your code:
>
>>USE EMPMSTB9 ALIAS EM
>
>Opens a database table named "EMPMSTB9" with Alias "EM". You also select an 
>Index and set a scope, specifying the order and limit which records are 
>visible.
>
>Then you open a second Workarea with:
>
>>SQL "SELECT CODE,FIRSTNAME,DOB FROM EMPMSTB9 AS EM WHERE 
>>NATIONALIT='IND'AND TRADE='T01'"
>
>This opens the same database table "EMPMSTB9" in a new Workarea, selects a 
>subset of fields and a different subset of records, and then you show the 
>first Workarea in a Browse table.
>
>My questions are: Why do you use "AS EM" in the Select command? Do you 
>somehow expect that the SQL command uses a subset of the original "EM" 
>Workarea, or why do you open that second Workarea in the first place?
>
>The "SQL..." line is not having any effect on the "EM->(Browse())" line 
>of code, so why is it there?
>
>You don't post a reproducible example (code, data, and environment info) or 
>your actual Error logs, just code snippets and screen shots, so it is very 
>difficult (if not impossible) to help you with this, I'm afraid.
>
>Please tell us what you are trying to do -- and why you want to use the 
>ODBCDBE instead of the ADSDBE.
>
>Thanks,
>
>Andreas