Alaska Software Inc. - ODBC and MS SQL Server
Username: Password:
AuthorTopic: ODBC and MS SQL Server
Jonathan LeemingODBC and MS SQL Server
on Wed, 22 May 2019 09:57:15 -0600
I have a need to read and possible update a MS SQL Server database from 
within Xbase.  I have created an ODBC connection to the northwind 
database for testing as configured below...

Microsoft SQL Server ODBC Driver Version 10.00.14393

Data Source Name: northwind
Data Source Description:
Server: X1-JDL
Database: northwind
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

I have used both...

DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_PROMPT_MODE, ODBC_PROMPT_COMPLETE) 
    Driver tries to complete info, prompt if necessary.

DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_WIN_HANDLE, oDlg:getHwnd()) 
    Set oDlg as parent for ODBC Popup Dialog Window

oSession := dacSession():New("DBE=ODBCDBE")

or...

CREATE CONNECTION INTO oSession DATASOURCE "northwind"

Either connection works but when it comes to the SQL query I run into 
problems.

using

cSQL := "SELECT CustomerID FROM dbo.Customers WHERE CustomerID = 'BOLID'"

SQL (cSQL) <- Works fine and I get a file named customers with 1 record

The above also works fine with UPDATE / DELETE however I don't believe I 
can determine the how many records were affected.

So I would like to use...

oStmt := DacSqlStatement():fromChar( cSQL )

nRows := oStmt:Build():Execute()

but with the DacSqlStatement I get an error "Session does not support 
SQL".  I have tried simplifying to "SELECT * FROM Customers" but still 
get the same error.  I would like to be able to use the DasSQLStatement 
as then I can determine how many rows were impacted on an UPDATE / DELETE.

Any & All suggestions appreciated!

Thanks... Jonathan

Using Xbase 2.0.1095

jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada

sql.prg
SQL.xpj
Jorge L. BorlandoRe: ODBC and MS SQL Server
on Thu, 23 May 2019 13:06:44 -0300
Hi Jonathan


try to include the name of the database in the description of the field in 
the statement, example: base.dbo.table

regards

"Jonathan Leeming" wrote in message 
news:13384cf6$18811362$22f75@news.alaska-software.com...
> I have a need to read and possible update a MS SQL Server database from
> within Xbase.  I have created an ODBC connection to the northwind
> database for testing as configured below...
>
> Microsoft SQL Server ODBC Driver Version 10.00.14393
>
> Data Source Name: northwind
> Data Source Description:
> Server: X1-JDL
> Database: northwind
> Language: (Default)
> Translate Character Data: Yes
> Log Long Running Queries: No
> Log Driver Statistics: No
> Use Regional Settings: No
> Prepared Statements Option: Drop temporary procedures on disconnect
> Use Failover Server: No
> Use ANSI Quoted Identifiers: Yes
> Use ANSI Null, Paddings and Warnings: Yes
> Data Encryption: No
>
> I have used both...
>
> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_PROMPT_MODE, ODBC_PROMPT_COMPLETE)
>    Driver tries to complete info, prompt if necessary.
>
> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_WIN_HANDLE, oDlg:getHwnd())
>    Set oDlg as parent for ODBC Popup Dialog Window
>
> oSession := dacSession():New("DBE=ODBCDBE")
>
> or...
>
> CREATE CONNECTION INTO oSession DATASOURCE "northwind"
>
> Either connection works but when it comes to the SQL query I run into
> problems.
>
> using
>
> cSQL := "SELECT CustomerID FROM dbo.Customers WHERE CustomerID = 'BOLID'"
>
> SQL (cSQL) <- Works fine and I get a file named customers with 1 record
>
> The above also works fine with UPDATE / DELETE however I don't believe I
> can determine the how many records were affected.
>
> So I would like to use...
>
> oStmt := DacSqlStatement():fromChar( cSQL )
>
> nRows := oStmt:Build():Execute()
>
> but with the DacSqlStatement I get an error "Session does not support
> SQL".  I have tried simplifying to "SELECT * FROM Customers" but still
> get the same error.  I would like to be able to use the DasSQLStatement
> as then I can determine how many rows were impacted on an UPDATE / DELETE.
>
> Any & All suggestions appreciated!
>
> Thanks... Jonathan
>
> Using Xbase 2.0.1095
>
> -- 
> jonathan.leeming@the-family-centre.com
> Edmonton, Alberta, Canada
>
Jonathan LeemingRe: ODBC and MS SQL Server
on Thu, 23 May 2019 17:12:58 -0600
On 5/23/2019 10:06 AM, Jorge L. Borlando wrote:
> Hi Jonathan
> 
> 
> try to include the name of the database in the description of the field 
> in the statement, example: base.dbo.table
> 
> regards
> 
> "Jonathan Leeming" wrote in message 
> news:13384cf6$18811362$22f75@news.alaska-software.com...
>> I have a need to read and possible update a MS SQL Server database from
>> within Xbase.  I have created an ODBC connection to the northwind
>> database for testing as configured below...
>>
>> Microsoft SQL Server ODBC Driver Version 10.00.14393
>>
>> Data Source Name: northwind
>> Data Source Description:
>> Server: X1-JDL
>> Database: northwind
>> Language: (Default)
>> Translate Character Data: Yes
>> Log Long Running Queries: No
>> Log Driver Statistics: No
>> Use Regional Settings: No
>> Prepared Statements Option: Drop temporary procedures on disconnect
>> Use Failover Server: No
>> Use ANSI Quoted Identifiers: Yes
>> Use ANSI Null, Paddings and Warnings: Yes
>> Data Encryption: No
>>
>> I have used both...
>>
>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_PROMPT_MODE, ODBC_PROMPT_COMPLETE)
>>    Driver tries to complete info, prompt if necessary.
>>
>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_WIN_HANDLE, oDlg:getHwnd())
>>    Set oDlg as parent for ODBC Popup Dialog Window
>>
>> oSession := dacSession():New("DBE=ODBCDBE")
>>
>> or...
>>
>> CREATE CONNECTION INTO oSession DATASOURCE "northwind"
>>
>> Either connection works but when it comes to the SQL query I run into
>> problems.
>>
>> using
>>
>> cSQL := "SELECT CustomerID FROM dbo.Customers WHERE CustomerID = 'BOLID'"
>>
>> SQL (cSQL) <- Works fine and I get a file named customers with 1 record
>>
>> The above also works fine with UPDATE / DELETE however I don't believe I
>> can determine the how many records were affected.
>>
>> So I would like to use...
>>
>> oStmt := DacSqlStatement():fromChar( cSQL )
>>
>> nRows := oStmt:Build():Execute()
>>
>> but with the DacSqlStatement I get an error "Session does not support
>> SQL".  I have tried simplifying to "SELECT * FROM Customers" but still
>> get the same error.  I would like to be able to use the DasSQLStatement
>> as then I can determine how many rows were impacted on an UPDATE / 
>> DELETE.
>>
>> Any & All suggestions appreciated!
>>
>> Thanks... Jonathan
>>
>> Using Xbase 2.0.1095
>>
>> -- 
>> jonathan.leeming@the-family-centre.com
>> Edmonton, Alberta, Canada
>>
Thanks Jorge,

I tried adding "northwind"...

cSQL := "SELECT CustomerID FROM northwind.dbo.Customers WHERE CustomerID 
= 'BOLID'"

But with the same result... Session does not support SQL

jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada
Jorge L. BorlandoRe: ODBC and MS SQL Server
on Tue, 28 May 2019 20:39:55 -0300
Hi Jonathan
this example works for me with odbc

   oSession := DacSession():new( "DBE=ODBCDBE"     +;
                                 ";DSN=Tango"      +;
                                 ";UID=sa"         +;
                                 ";PWD=xxxxxx"  )
   If ( !oSession:isConnected() )
         ? "Unable to connect to server !"
         ? oSession:getLastMessage()
    Else
      SQL "SELECT DB_NAME()" INTO A1
      SQL CMD "USE PRUEBA"
      SQL "SELECT DB_NAME()" INTO A1

      A1 := "SELECT * FROM sysobjects WHERE TYPE='U';"
      USE (A1 ) NEW Alias cTAB


"Jonathan Leeming" wrote in message 
news:72aae058$1241b3d5$2816f@news.alaska-software.com...
> On 5/23/2019 10:06 AM, Jorge L. Borlando wrote:
>> Hi Jonathan
>>
>>
>> try to include the name of the database in the description of the field 
>> in the statement, example: base.dbo.table
>>
>> regards
>>
>> "Jonathan Leeming" wrote in message 
>> news:13384cf6$18811362$22f75@news.alaska-software.com...
>>> I have a need to read and possible update a MS SQL Server database from
>>> within Xbase.  I have created an ODBC connection to the northwind
>>> database for testing as configured below...
>>>
>>> Microsoft SQL Server ODBC Driver Version 10.00.14393
>>>
>>> Data Source Name: northwind
>>> Data Source Description:
>>> Server: X1-JDL
>>> Database: northwind
>>> Language: (Default)
>>> Translate Character Data: Yes
>>> Log Long Running Queries: No
>>> Log Driver Statistics: No
>>> Use Regional Settings: No
>>> Prepared Statements Option: Drop temporary procedures on disconnect
>>> Use Failover Server: No
>>> Use ANSI Quoted Identifiers: Yes
>>> Use ANSI Null, Paddings and Warnings: Yes
>>> Data Encryption: No
>>>
>>> I have used both...
>>>
>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_PROMPT_MODE, ODBC_PROMPT_COMPLETE)
>>>    Driver tries to complete info, prompt if necessary.
>>>
>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_WIN_HANDLE, oDlg:getHwnd())
>>>    Set oDlg as parent for ODBC Popup Dialog Window
>>>
>>> oSession := dacSession():New("DBE=ODBCDBE")
>>>
>>> or...
>>>
>>> CREATE CONNECTION INTO oSession DATASOURCE "northwind"
>>>
>>> Either connection works but when it comes to the SQL query I run into
>>> problems.
>>>
>>> using
>>>
>>> cSQL := "SELECT CustomerID FROM dbo.Customers WHERE CustomerID = 
>>> 'BOLID'"
>>>
>>> SQL (cSQL) <- Works fine and I get a file named customers with 1 record
>>>
>>> The above also works fine with UPDATE / DELETE however I don't believe I
>>> can determine the how many records were affected.
>>>
>>> So I would like to use...
>>>
>>> oStmt := DacSqlStatement():fromChar( cSQL )
>>>
>>> nRows := oStmt:Build():Execute()
>>>
>>> but with the DacSqlStatement I get an error "Session does not support
>>> SQL".  I have tried simplifying to "SELECT * FROM Customers" but still
>>> get the same error.  I would like to be able to use the DasSQLStatement
>>> as then I can determine how many rows were impacted on an UPDATE / 
>>> DELETE.
>>>
>>> Any & All suggestions appreciated!
>>>
>>> Thanks... Jonathan
>>>
>>> Using Xbase 2.0.1095
>>>
>>> -- 
>>> jonathan.leeming@the-family-centre.com
>>> Edmonton, Alberta, Canada
>>>
> Thanks Jorge,
>
> I tried adding "northwind"...
>
> cSQL := "SELECT CustomerID FROM northwind.dbo.Customers WHERE CustomerID = 
> 'BOLID'"
>
> But with the same result... Session does not support SQL
>
> -- 
> jonathan.leeming@the-family-centre.com
> Edmonton, Alberta, Canada
Jonathan LeemingRe: ODBC and MS SQL Server
on Wed, 29 May 2019 09:36:08 -0600
On 5/28/2019 5:39 PM, Jorge L. Borlando wrote:
> Hi Jonathan
> this example works for me with odbc
> 
>    oSession := DacSession():new( "DBE=ODBCDBE"     +;
>                                  ";DSN=Tango"      +;
>                                  ";UID=sa"         +;
>                                  ";PWD=xxxxxx"  )
>    If ( !oSession:isConnected() )
>          ? "Unable to connect to server !"
>          ? oSession:getLastMessage()
>     Else
>       SQL "SELECT DB_NAME()" INTO A1
>       SQL CMD "USE PRUEBA"
>       SQL "SELECT DB_NAME()" INTO A1
> 
>       A1 := "SELECT * FROM sysobjects WHERE TYPE='U';"
>       USE (A1 ) NEW Alias cTAB
> 
> 
> "Jonathan Leeming" wrote in message 
> news:72aae058$1241b3d5$2816f@news.alaska-software.com...
>> On 5/23/2019 10:06 AM, Jorge L. Borlando wrote:
>>> Hi Jonathan
>>>
>>>
>>> try to include the name of the database in the description of the 
>>> field in the statement, example: base.dbo.table
>>>
>>> regards
>>>
>>> "Jonathan Leeming" wrote in message 
>>> news:13384cf6$18811362$22f75@news.alaska-software.com...
>>>> I have a need to read and possible update a MS SQL Server database from
>>>> within Xbase.  I have created an ODBC connection to the northwind
>>>> database for testing as configured below...
>>>>
>>>> Microsoft SQL Server ODBC Driver Version 10.00.14393
>>>>
>>>> Data Source Name: northwind
>>>> Data Source Description:
>>>> Server: X1-JDL
>>>> Database: northwind
>>>> Language: (Default)
>>>> Translate Character Data: Yes
>>>> Log Long Running Queries: No
>>>> Log Driver Statistics: No
>>>> Use Regional Settings: No
>>>> Prepared Statements Option: Drop temporary procedures on disconnect
>>>> Use Failover Server: No
>>>> Use ANSI Quoted Identifiers: Yes
>>>> Use ANSI Null, Paddings and Warnings: Yes
>>>> Data Encryption: No
>>>>
>>>> I have used both...
>>>>
>>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_PROMPT_MODE, 
>>>> ODBC_PROMPT_COMPLETE)
>>>>    Driver tries to complete info, prompt if necessary.
>>>>
>>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_WIN_HANDLE, oDlg:getHwnd())
>>>>    Set oDlg as parent for ODBC Popup Dialog Window
>>>>
>>>> oSession := dacSession():New("DBE=ODBCDBE")
>>>>
>>>> or...
>>>>
>>>> CREATE CONNECTION INTO oSession DATASOURCE "northwind"
>>>>
>>>> Either connection works but when it comes to the SQL query I run into
>>>> problems.
>>>>
>>>> using
>>>>
>>>> cSQL := "SELECT CustomerID FROM dbo.Customers WHERE CustomerID = 
>>>> 'BOLID'"
>>>>
>>>> SQL (cSQL) <- Works fine and I get a file named customers with 1 record
>>>>
>>>> The above also works fine with UPDATE / DELETE however I don't 
>>>> believe I
>>>> can determine the how many records were affected.
>>>>
>>>> So I would like to use...
>>>>
>>>> oStmt := DacSqlStatement():fromChar( cSQL )
>>>>
>>>> nRows := oStmt:Build():Execute()
>>>>
>>>> but with the DacSqlStatement I get an error "Session does not support
>>>> SQL".  I have tried simplifying to "SELECT * FROM Customers" but still
>>>> get the same error.  I would like to be able to use the DasSQLStatement
>>>> as then I can determine how many rows were impacted on an UPDATE / 
>>>> DELETE.
>>>>
>>>> Any & All suggestions appreciated!
>>>>
>>>> Thanks... Jonathan
>>>>
>>>> Using Xbase 2.0.1095
>>>>
>>>> -- 
>>>> jonathan.leeming@the-family-centre.com
>>>> Edmonton, Alberta, Canada
>>>>
>> Thanks Jorge,
>>
>> I tried adding "northwind"...
>>
>> cSQL := "SELECT CustomerID FROM northwind.dbo.Customers WHERE 
>> CustomerID = 'BOLID'"
>>
>> But with the same result... Session does not support SQL
>>
>> -- 
>> jonathan.leeming@the-family-centre.com
>> Edmonton, Alberta, Canada 
> 
Hi Jorge,

Thanks for sharing your code which I have since "played" with.

I agree that there is no problem using the SQL command to execute SQL 
statements such as..

cSQL := "UPDATE Customers SET City = 'Edmonton' WHERE CustomerID ='BOLID'"

SQL (cSQL)

However I do not believe there is a way to determine if the UPDATE 
succeeded or how many records where updated short of performing a second 
query.

Instead of using the SQL command I was hoping to be able to use 
something like

oStmt := DacSqlStatement():fromChar( cSQL )

nRows := oStmt:Build():Execute()

Based upon the Xbase++ documentation nRows would contain a value 
indicating how many rows were updated when the cSQL statement is 
executed.  However, according to Andreas, Alaska has not implimented 
this capability for ODBC sessions.

Thanks for your suggestions... Regards... Jonathan

jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada
Matej JuracRe: ODBC and MS SQL Server
on Thu, 30 May 2019 08:14:25 +0200
There is way. You just have to do it with same connection and immediately
after executing update, insert, delete ..

In mysql:


update testable set something='1' where something is null
select ROW_COUNT() as howmany

and M$ SQl Server:

drop table if exists test
go
create table test (something char(10))
insert into test (something) values ('1'), ('2'), ('3')
insert into test (something) values ('1'), ('2'), ('3')
insert into test (something) values ('1'), ('2'), ('3')
go
update test set something='a1' where something='1'
select @@ROWCOUNT

In both you will get number of records affected by action.



Jonathan Leeming wrote in message
news:4ac7d11e$788daa25$3f9cb@news.alaska-software.com...
>On 5/28/2019 5:39 PM, Jorge L. Borlando wrote:
>> Hi Jonathan
>> this example works for me with odbc
>> 
>>    oSession := DacSession():new( "DBE=ODBCDBE"     +;
>>                                  ";DSN=Tango"      +;
>>                                  ";UID=sa"         +;
>>                                  ";PWD=xxxxxx"  )
>>    If ( !oSession:isConnected() )
>>          ? "Unable to connect to server !"
>>          ? oSession:getLastMessage()
>>     Else
>>       SQL "SELECT DB_NAME()" INTO A1
>>       SQL CMD "USE PRUEBA"
>>       SQL "SELECT DB_NAME()" INTO A1
>> 
>>       A1 := "SELECT * FROM sysobjects WHERE TYPE='U';"
>>       USE (A1 ) NEW Alias cTAB
>> 
>> 
>> "Jonathan Leeming" wrote in message 
>> news:72aae058$1241b3d5$2816f@news.alaska-software.com...
>>> On 5/23/2019 10:06 AM, Jorge L. Borlando wrote:
>>>> Hi Jonathan
>>>>
>>>>
>>>> try to include the name of the database in the description of the 
>>>> field in the statement, example: base.dbo.table
>>>>
>>>> regards
>>>>
>>>> "Jonathan Leeming" wrote in message 
>>>> news:13384cf6$18811362$22f75@news.alaska-software.com...
>>>>> I have a need to read and possible update a MS SQL Server database from
>>>>> within Xbase.  I have created an ODBC connection to the northwind
>>>>> database for testing as configured below...
>>>>>
>>>>> Microsoft SQL Server ODBC Driver Version 10.00.14393
>>>>>
>>>>> Data Source Name: northwind
>>>>> Data Source Description:
>>>>> Server: X1-JDL
>>>>> Database: northwind
>>>>> Language: (Default)
>>>>> Translate Character Data: Yes
>>>>> Log Long Running Queries: No
>>>>> Log Driver Statistics: No
>>>>> Use Regional Settings: No
>>>>> Prepared Statements Option: Drop temporary procedures on disconnect
>>>>> Use Failover Server: No
>>>>> Use ANSI Quoted Identifiers: Yes
>>>>> Use ANSI Null, Paddings and Warnings: Yes
>>>>> Data Encryption: No
>>>>>
>>>>> I have used both...
>>>>>
>>>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_PROMPT_MODE, 
>>>>> ODBC_PROMPT_COMPLETE)
>>>>>    Driver tries to complete info, prompt if necessary.
>>>>>
>>>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_WIN_HANDLE, oDlg:getHwnd())
>>>>>    Set oDlg as parent for ODBC Popup Dialog Window
>>>>>
>>>>> oSession := dacSession():New("DBE=ODBCDBE")
>>>>>
>>>>> or...
>>>>>
>>>>> CREATE CONNECTION INTO oSession DATASOURCE "northwind"
>>>>>
>>>>> Either connection works but when it comes to the SQL query I run into
>>>>> problems.
>>>>>
>>>>> using
>>>>>
>>>>> cSQL := "SELECT CustomerID FROM dbo.Customers WHERE CustomerID = 
>>>>> 'BOLID'"
>>>>>
>>>>> SQL (cSQL) <- Works fine and I get a file named customers with 1 record
>>>>>
>>>>> The above also works fine with UPDATE / DELETE however I don't 
>>>>> believe I
>>>>> can determine the how many records were affected.
>>>>>
>>>>> So I would like to use...
>>>>>
>>>>> oStmt := DacSqlStatement():fromChar( cSQL )
>>>>>
>>>>> nRows := oStmt:Build():Execute()
>>>>>
>>>>> but with the DacSqlStatement I get an error "Session does not support
>>>>> SQL".  I have tried simplifying to "SELECT * FROM Customers" but still
>>>>> get the same error.  I would like to be able to use the DasSQLStatement
>>>>> as then I can determine how many rows were impacted on an UPDATE / 
>>>>> DELETE.
>>>>>
>>>>> Any & All suggestions appreciated!
>>>>>
>>>>> Thanks... Jonathan
>>>>>
>>>>> Using Xbase 2.0.1095
>>>>>
>>>>> -- 
>>>>> jonathan.leeming@the-family-centre.com
>>>>> Edmonton, Alberta, Canada
>>>>>
>>> Thanks Jorge,
>>>
>>> I tried adding "northwind"...
>>>
>>> cSQL := "SELECT CustomerID FROM northwind.dbo.Customers WHERE 
>>> CustomerID = 'BOLID'"
>>>
>>> But with the same result... Session does not support SQL
>>>
>>> -- 
>>> jonathan.leeming@the-family-centre.com
>>> Edmonton, Alberta, Canada 
>> 
>Hi Jorge,
>
>Thanks for sharing your code which I have since "played" with.
>
>I agree that there is no problem using the SQL command to execute SQL 
>statements such as..
>
>cSQL := "UPDATE Customers SET City = 'Edmonton' WHERE CustomerID ='BOLID'"
>
>SQL (cSQL)
>
>However I do not believe there is a way to determine if the UPDATE 
>succeeded or how many records where updated short of performing a second 
>query.
>
>Instead of using the SQL command I was hoping to be able to use 
>something like
>
>oStmt := DacSqlStatement():fromChar( cSQL )
>
>nRows := oStmt:Build():Execute()
>
>Based upon the Xbase++ documentation nRows would contain a value 
>indicating how many rows were updated when the cSQL statement is 
>executed.  However, according to Andreas, Alaska has not implimented 
>this capability for ODBC sessions.
>
>Thanks for your suggestions... Regards... Jonathan
Jonathan LeemingRe: ODBC and MS SQL Server
on Thu, 30 May 2019 08:36:20 -0600
On 5/30/2019 12:14 AM, Matej Jurac wrote:
> There is way. You just have to do it with same connection and immediately
> after executing update, insert, delete ..
> 
> In mysql:
> 
> 
> update testable set something='1' where something is null
> select ROW_COUNT() as howmany
> 
> and M$ SQl Server:
> 
> drop table if exists test
> go
> create table test (something char(10))
> insert into test (something) values ('1'), ('2'), ('3')
> insert into test (something) values ('1'), ('2'), ('3')
> insert into test (something) values ('1'), ('2'), ('3')
> go
> update test set something='a1' where something='1'
> select @@ROWCOUNT
> 
> In both you will get number of records affected by action.
> 
> 
> 
> Jonathan Leeming wrote in message
> news:4ac7d11e$788daa25$3f9cb@news.alaska-software.com...
>> On 5/28/2019 5:39 PM, Jorge L. Borlando wrote:
>>> Hi Jonathan
>>> this example works for me with odbc
>>>
>>>     oSession := DacSession():new( "DBE=ODBCDBE"     +;
>>>                                   ";DSN=Tango"      +;
>>>                                   ";UID=sa"         +;
>>>                                   ";PWD=xxxxxx"  )
>>>     If ( !oSession:isConnected() )
>>>           ? "Unable to connect to server !"
>>>           ? oSession:getLastMessage()
>>>      Else
>>>        SQL "SELECT DB_NAME()" INTO A1
>>>        SQL CMD "USE PRUEBA"
>>>        SQL "SELECT DB_NAME()" INTO A1
>>>
>>>        A1 := "SELECT * FROM sysobjects WHERE TYPE='U';"
>>>        USE (A1 ) NEW Alias cTAB
>>>
>>>
>>> "Jonathan Leeming" wrote in message
>>> news:72aae058$1241b3d5$2816f@news.alaska-software.com...
>>>> On 5/23/2019 10:06 AM, Jorge L. Borlando wrote:
>>>>> Hi Jonathan
>>>>>
>>>>>
>>>>> try to include the name of the database in the description of the
>>>>> field in the statement, example: base.dbo.table
>>>>>
>>>>> regards
>>>>>
>>>>> "Jonathan Leeming" wrote in message
>>>>> news:13384cf6$18811362$22f75@news.alaska-software.com...
>>>>>> I have a need to read and possible update a MS SQL Server database from
>>>>>> within Xbase.  I have created an ODBC connection to the northwind
>>>>>> database for testing as configured below...
>>>>>>
>>>>>> Microsoft SQL Server ODBC Driver Version 10.00.14393
>>>>>>
>>>>>> Data Source Name: northwind
>>>>>> Data Source Description:
>>>>>> Server: X1-JDL
>>>>>> Database: northwind
>>>>>> Language: (Default)
>>>>>> Translate Character Data: Yes
>>>>>> Log Long Running Queries: No
>>>>>> Log Driver Statistics: No
>>>>>> Use Regional Settings: No
>>>>>> Prepared Statements Option: Drop temporary procedures on disconnect
>>>>>> Use Failover Server: No
>>>>>> Use ANSI Quoted Identifiers: Yes
>>>>>> Use ANSI Null, Paddings and Warnings: Yes
>>>>>> Data Encryption: No
>>>>>>
>>>>>> I have used both...
>>>>>>
>>>>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_PROMPT_MODE,
>>>>>> ODBC_PROMPT_COMPLETE)
>>>>>>     Driver tries to complete info, prompt if necessary.
>>>>>>
>>>>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_WIN_HANDLE, oDlg:getHwnd())
>>>>>>     Set oDlg as parent for ODBC Popup Dialog Window
>>>>>>
>>>>>> oSession := dacSession():New("DBE=ODBCDBE")
>>>>>>
>>>>>> or...
>>>>>>
>>>>>> CREATE CONNECTION INTO oSession DATASOURCE "northwind"
>>>>>>
>>>>>> Either connection works but when it comes to the SQL query I run into
>>>>>> problems.
>>>>>>
>>>>>> using
>>>>>>
>>>>>> cSQL := "SELECT CustomerID FROM dbo.Customers WHERE CustomerID =
>>>>>> 'BOLID'"
>>>>>>
>>>>>> SQL (cSQL) <- Works fine and I get a file named customers with 1 record
>>>>>>
>>>>>> The above also works fine with UPDATE / DELETE however I don't
>>>>>> believe I
>>>>>> can determine the how many records were affected.
>>>>>>
>>>>>> So I would like to use...
>>>>>>
>>>>>> oStmt := DacSqlStatement():fromChar( cSQL )
>>>>>>
>>>>>> nRows := oStmt:Build():Execute()
>>>>>>
>>>>>> but with the DacSqlStatement I get an error "Session does not support
>>>>>> SQL".  I have tried simplifying to "SELECT * FROM Customers" but still
>>>>>> get the same error.  I would like to be able to use the DasSQLStatement
>>>>>> as then I can determine how many rows were impacted on an UPDATE /
>>>>>> DELETE.
>>>>>>
>>>>>> Any & All suggestions appreciated!
>>>>>>
>>>>>> Thanks... Jonathan
>>>>>>
>>>>>> Using Xbase 2.0.1095
>>>>>>
>>>>>> -- 
>>>>>> jonathan.leeming@the-family-centre.com
>>>>>> Edmonton, Alberta, Canada
>>>>>>
>>>> Thanks Jorge,
>>>>
>>>> I tried adding "northwind"...
>>>>
>>>> cSQL := "SELECT CustomerID FROM northwind.dbo.Customers WHERE
>>>> CustomerID = 'BOLID'"
>>>>
>>>> But with the same result... Session does not support SQL
>>>>
>>>> -- 
>>>> jonathan.leeming@the-family-centre.com
>>>> Edmonton, Alberta, Canada
>>>
>> Hi Jorge,
>>
>> Thanks for sharing your code which I have since "played" with.
>>
>> I agree that there is no problem using the SQL command to execute SQL
>> statements such as..
>>
>> cSQL := "UPDATE Customers SET City = 'Edmonton' WHERE CustomerID ='BOLID'"
>>
>> SQL (cSQL)
>>
>> However I do not believe there is a way to determine if the UPDATE
>> succeeded or how many records where updated short of performing a second
>> query.
>>
>> Instead of using the SQL command I was hoping to be able to use
>> something like
>>
>> oStmt := DacSqlStatement():fromChar( cSQL )
>>
>> nRows := oStmt:Build():Execute()
>>
>> Based upon the Xbase++ documentation nRows would contain a value
>> indicating how many rows were updated when the cSQL statement is
>> executed.  However, according to Andreas, Alaska has not implimented
>> this capability for ODBC sessions.
>>
>> Thanks for your suggestions... Regards... Jonathan

Hi Matej,

Thanks for the tip!  Your suggested...

SQL "SELECT @@ROWCOUNT" INTO xResult

Works great.

However after playing with Boris's SQLExpress I'm inclined to agree with 
your previous statements "For production environment SqlExpress is 
currently only viable tool."

Thanks for your help... Jonathan

PS... Sorry for mis-spelling your name in my earlier reply!!!

jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada
Jorge L. BorlandoRe: ODBC and MS SQL Server
on Thu, 30 May 2019 14:59:23 -0300
Jhonathan,

you can to use a field like "universal ID" , which you set in your updates 
and then.... you can count on with command :

cUI := cUuidCreateName()    form pablo botella
cSQL := "UPDATE Customers SET City = 'Edmonton'" 
+;
                                                          "myfieldxx='" + 
cUI + '"                        +;
                               "WHERE CustomerID ='BOLID'"
SQL ( cSQL )

SQL( "Select Count(*) where myfieldxx = '" + cUI + "'" )



"Jonathan Leeming" wrote in message 
news:4ac7d11e$788daa25$3f9cb@news.alaska-software.com...
> On 5/28/2019 5:39 PM, Jorge L. Borlando wrote:
>> Hi Jonathan
>> this example works for me with odbc
>>
>>    oSession := DacSession():new( "DBE=ODBCDBE"     +;
>>                                  ";DSN=Tango"      +;
>>                                  ";UID=sa"         +;
>>                                  ";PWD=xxxxxx"  )
>>    If ( !oSession:isConnected() )
>>          ? "Unable to connect to server !"
>>          ? oSession:getLastMessage()
>>     Else
>>       SQL "SELECT DB_NAME()" INTO A1
>>       SQL CMD "USE PRUEBA"
>>       SQL "SELECT DB_NAME()" INTO A1
>>
>>       A1 := "SELECT * FROM sysobjects WHERE TYPE='U';"
>>       USE (A1 ) NEW Alias cTAB
>>
>>
>> "Jonathan Leeming" wrote in message 
>> news:72aae058$1241b3d5$2816f@news.alaska-software.com...
>>> On 5/23/2019 10:06 AM, Jorge L. Borlando wrote:
>>>> Hi Jonathan
>>>>
>>>>
>>>> try to include the name of the database in the description of the field 
>>>> in the statement, example: base.dbo.table
>>>>
>>>> regards
>>>>
>>>> "Jonathan Leeming" wrote in message 
>>>> news:13384cf6$18811362$22f75@news.alaska-software.com...
>>>>> I have a need to read and possible update a MS SQL Server database 
>>>>> from
>>>>> within Xbase.  I have created an ODBC connection to the northwind
>>>>> database for testing as configured below...
>>>>>
>>>>> Microsoft SQL Server ODBC Driver Version 10.00.14393
>>>>>
>>>>> Data Source Name: northwind
>>>>> Data Source Description:
>>>>> Server: X1-JDL
>>>>> Database: northwind
>>>>> Language: (Default)
>>>>> Translate Character Data: Yes
>>>>> Log Long Running Queries: No
>>>>> Log Driver Statistics: No
>>>>> Use Regional Settings: No
>>>>> Prepared Statements Option: Drop temporary procedures on disconnect
>>>>> Use Failover Server: No
>>>>> Use ANSI Quoted Identifiers: Yes
>>>>> Use ANSI Null, Paddings and Warnings: Yes
>>>>> Data Encryption: No
>>>>>
>>>>> I have used both...
>>>>>
>>>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_PROMPT_MODE, 
>>>>> ODBC_PROMPT_COMPLETE)
>>>>>    Driver tries to complete info, prompt if necessary.
>>>>>
>>>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_WIN_HANDLE, oDlg:getHwnd())
>>>>>    Set oDlg as parent for ODBC Popup Dialog Window
>>>>>
>>>>> oSession := dacSession():New("DBE=ODBCDBE")
>>>>>
>>>>> or...
>>>>>
>>>>> CREATE CONNECTION INTO oSession DATASOURCE "northwind"
>>>>>
>>>>> Either connection works but when it comes to the SQL query I run into
>>>>> problems.
>>>>>
>>>>> using
>>>>>
>>>>> cSQL := "SELECT CustomerID FROM dbo.Customers WHERE CustomerID = 
>>>>> 'BOLID'"
>>>>>
>>>>> SQL (cSQL) <- Works fine and I get a file named customers with 1 
>>>>> record
>>>>>
>>>>> The above also works fine with UPDATE / DELETE however I don't believe 
>>>>> I
>>>>> can determine the how many records were affected.
>>>>>
>>>>> So I would like to use...
>>>>>
>>>>> oStmt := DacSqlStatement():fromChar( cSQL )
>>>>>
>>>>> nRows := oStmt:Build():Execute()
>>>>>
>>>>> but with the DacSqlStatement I get an error "Session does not support
>>>>> SQL".  I have tried simplifying to "SELECT * FROM Customers" but still
>>>>> get the same error.  I would like to be able to use the 
>>>>> DasSQLStatement
>>>>> as then I can determine how many rows were impacted on an UPDATE / 
>>>>> DELETE.
>>>>>
>>>>> Any & All suggestions appreciated!
>>>>>
>>>>> Thanks... Jonathan
>>>>>
>>>>> Using Xbase 2.0.1095
>>>>>
>>>>> -- 
>>>>> jonathan.leeming@the-family-centre.com
>>>>> Edmonton, Alberta, Canada
>>>>>
>>> Thanks Jorge,
>>>
>>> I tried adding "northwind"...
>>>
>>> cSQL := "SELECT CustomerID FROM northwind.dbo.Customers WHERE CustomerID 
>>> = 'BOLID'"
>>>
>>> But with the same result... Session does not support SQL
>>>
>>> -- 
>>> jonathan.leeming@the-family-centre.com
>>> Edmonton, Alberta, Canada
>>
> Hi Jorge,
>
> Thanks for sharing your code which I have since "played" with.
>
> I agree that there is no problem using the SQL command to execute SQL 
> statements such as..
>
> cSQL := "UPDATE Customers SET City = 'Edmonton' WHERE CustomerID ='BOLID'"
>
> SQL (cSQL)
>
> However I do not believe there is a way to determine if the UPDATE 
> succeeded or how many records where updated short of performing a second 
> query.
>
> Instead of using the SQL command I was hoping to be able to use something 
> like
>
> oStmt := DacSqlStatement():fromChar( cSQL )
>
> nRows := oStmt:Build():Execute()
>
> Based upon the Xbase++ documentation nRows would contain a value 
> indicating how many rows were updated when the cSQL statement is executed. 
> However, according to Andreas, Alaska has not implimented this capability 
> for ODBC sessions.
>
> Thanks for your suggestions... Regards... Jonathan
>
> -- 
> jonathan.leeming@the-family-centre.com
> Edmonton, Alberta, Canada
Osvaldo RamirezRe: ODBC and MS SQL Server
on Thu, 30 May 2019 16:10:44 -0600
On 5/30/19 11:59 AM, Jorge L. Borlando wrote:

Thanks Jorge

I like it

"Select Count(*) where myfieldxx = '" + cUI + "'" )

Best Regards

> Jhonathan,
> 
> you can to use a field like "universal ID" , which you set in your 
> updates and then.... you can count on with command :
> 
> cUI := cUuidCreateName()    form pablo botella
> cSQL := "UPDATE Customers SET City = 'Edmonton'" +;
>                                                           "myfieldxx='" 
> + cUI + '"                        +;
>                                "WHERE CustomerID ='BOLID'"
> SQL ( cSQL )
> 
> SQL( "Select Count(*) where myfieldxx = '" + cUI + "'" )
> 
> 
> 
> "Jonathan Leeming" wrote in message 
> news:4ac7d11e$788daa25$3f9cb@news.alaska-software.com...
>> On 5/28/2019 5:39 PM, Jorge L. Borlando wrote:
>>> Hi Jonathan
>>> this example works for me with odbc
>>>
>>>    oSession := DacSession():new( "DBE=ODBCDBE"     +;
>>>                                  ";DSN=Tango"      +;
>>>                                  ";UID=sa"         +;
>>>                                  ";PWD=xxxxxx"  )
>>>    If ( !oSession:isConnected() )
>>>          ? "Unable to connect to server !"
>>>          ? oSession:getLastMessage()
>>>     Else
>>>       SQL "SELECT DB_NAME()" INTO A1
>>>       SQL CMD "USE PRUEBA"
>>>       SQL "SELECT DB_NAME()" INTO A1
>>>
>>>       A1 := "SELECT * FROM sysobjects WHERE TYPE='U';"
>>>       USE (A1 ) NEW Alias cTAB
>>>
>>>
>>> "Jonathan Leeming" wrote in message 
>>> news:72aae058$1241b3d5$2816f@news.alaska-software.com...
>>>> On 5/23/2019 10:06 AM, Jorge L. Borlando wrote:
>>>>> Hi Jonathan
>>>>>
>>>>>
>>>>> try to include the name of the database in the description of the 
>>>>> field in the statement, example: base.dbo.table
>>>>>
>>>>> regards
>>>>>
>>>>> "Jonathan Leeming" wrote in message 
>>>>> news:13384cf6$18811362$22f75@news.alaska-software.com...
>>>>>> I have a need to read and possible update a MS SQL Server database 
>>>>>> from
>>>>>> within Xbase.  I have created an ODBC connection to the northwind
>>>>>> database for testing as configured below...
>>>>>>
>>>>>> Microsoft SQL Server ODBC Driver Version 10.00.14393
>>>>>>
>>>>>> Data Source Name: northwind
>>>>>> Data Source Description:
>>>>>> Server: X1-JDL
>>>>>> Database: northwind
>>>>>> Language: (Default)
>>>>>> Translate Character Data: Yes
>>>>>> Log Long Running Queries: No
>>>>>> Log Driver Statistics: No
>>>>>> Use Regional Settings: No
>>>>>> Prepared Statements Option: Drop temporary procedures on disconnect
>>>>>> Use Failover Server: No
>>>>>> Use ANSI Quoted Identifiers: Yes
>>>>>> Use ANSI Null, Paddings and Warnings: Yes
>>>>>> Data Encryption: No
>>>>>>
>>>>>> I have used both...
>>>>>>
>>>>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_PROMPT_MODE, 
>>>>>> ODBC_PROMPT_COMPLETE)
>>>>>>    Driver tries to complete info, prompt if necessary.
>>>>>>
>>>>>> DbeInfo(COMPONENT_DICTIONARY, ODBCDBE_WIN_HANDLE, oDlg:getHwnd())
>>>>>>    Set oDlg as parent for ODBC Popup Dialog Window
>>>>>>
>>>>>> oSession := dacSession():New("DBE=ODBCDBE")
>>>>>>
>>>>>> or...
>>>>>>
>>>>>> CREATE CONNECTION INTO oSession DATASOURCE "northwind"
>>>>>>
>>>>>> Either connection works but when it comes to the SQL query I run into
>>>>>> problems.
>>>>>>
>>>>>> using
>>>>>>
>>>>>> cSQL := "SELECT CustomerID FROM dbo.Customers WHERE CustomerID = 
>>>>>> 'BOLID'"
>>>>>>
>>>>>> SQL (cSQL) <- Works fine and I get a file named customers with 1 
>>>>>> record
>>>>>>
>>>>>> The above also works fine with UPDATE / DELETE however I don't 
>>>>>> believe I
>>>>>> can determine the how many records were affected.
>>>>>>
>>>>>> So I would like to use...
>>>>>>
>>>>>> oStmt := DacSqlStatement():fromChar( cSQL )
>>>>>>
>>>>>> nRows := oStmt:Build():Execute()
>>>>>>
>>>>>> but with the DacSqlStatement I get an error "Session does not support
>>>>>> SQL".  I have tried simplifying to "SELECT * FROM Customers" but 
>>>>>> still
>>>>>> get the same error.  I would like to be able to use the 
>>>>>> DasSQLStatement
>>>>>> as then I can determine how many rows were impacted on an UPDATE / 
>>>>>> DELETE.
>>>>>>
>>>>>> Any & All suggestions appreciated!
>>>>>>
>>>>>> Thanks... Jonathan
>>>>>>
>>>>>> Using Xbase 2.0.1095
>>>>>>
>>>>>> -- 
>>>>>> jonathan.leeming@the-family-centre.com
>>>>>> Edmonton, Alberta, Canada
>>>>>>
>>>> Thanks Jorge,
>>>>
>>>> I tried adding "northwind"...
>>>>
>>>> cSQL := "SELECT CustomerID FROM northwind.dbo.Customers WHERE 
>>>> CustomerID = 'BOLID'"
>>>>
>>>> But with the same result... Session does not support SQL
>>>>
>>>> -- 
>>>> jonathan.leeming@the-family-centre.com
>>>> Edmonton, Alberta, Canada
>>>
>> Hi Jorge,
>>
>> Thanks for sharing your code which I have since "played" with.
>>
>> I agree that there is no problem using the SQL command to execute SQL 
>> statements such as..
>>
>> cSQL := "UPDATE Customers SET City = 'Edmonton' WHERE CustomerID 
>> ='BOLID'"
>>
>> SQL (cSQL)
>>
>> However I do not believe there is a way to determine if the UPDATE 
>> succeeded or how many records where updated short of performing a 
>> second query.
>>
>> Instead of using the SQL command I was hoping to be able to use 
>> something like
>>
>> oStmt := DacSqlStatement():fromChar( cSQL )
>>
>> nRows := oStmt:Build():Execute()
>>
>> Based upon the Xbase++ documentation nRows would contain a value 
>> indicating how many rows were updated when the cSQL statement is 
>> executed. However, according to Andreas, Alaska has not implimented 
>> this capability for ODBC sessions.
>>
>> Thanks for your suggestions... Regards... Jonathan
>>
>> -- 
>> jonathan.leeming@the-family-centre.com
>> Edmonton, Alberta, Canada 
>
Jim LeeRe: ODBC and MS SQL Server
on Fri, 24 May 2019 03:25:45 +0200
hi,

> Microsoft SQL Server ODBC Driver Version 10.00.14393

are you working with 64Bit OS ?
is the ODBC Driver 32 Bit ?



---
Diese E-Mail wurde von AVG auf Viren geprüft.
http://www.avg.com
Jonathan LeemingRe: ODBC and MS SQL Server
on Thu, 23 May 2019 21:32:33 -0600
On 5/23/2019 7:25 PM, Jim Lee wrote:
> hi,
> 
>> Microsoft SQL Server ODBC Driver Version 10.00.14393
> 
> are you working with 64Bit OS ?
> is the ODBC Driver 32 Bit ?
> 
> 
> 
> ---
> Diese E-Mail wurde von AVG auf Viren geprüft.
> http://www.avg.com
> 
Windows 10 ver 1607 64bit

ODBC Datasource Administrator (64bit)...

Reports:

Name: northwind
Platform: 32/64-bit
Driver: SQL Server

I also just tried an alternate ODBC configuration defined for 32/64 bit 
platform...

Microsoft ODBC Driver for SQL Server Version 14.00.1000

Data Source Name: NW_13
Data Source Description:
Server: X1-JDL
Use Integrated Security: Yes
Database: northwind
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes

Which uses a different ODBC SQL Driver with the same results.

All of these work fine if I use the SQL command but not if I use the 
DacSqlStatement().

Regards... Jonathan

jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada
Matej JuracRe: ODBC and MS SQL Server
on Fri, 24 May 2019 09:14:27 +0200
Mr. Leeming:

1) xpp is 32bit compiler and works in 32bit environmnet so you have to
configure odbc data sources with 32bit drivers for 32bit ODBC panel which
resides under:

c:\windows\SysWOW64\odbcad32.exe

2) Use "SQL Server Native Client 11.0" for connecting, v10 is kinda old

2.1) That driver should be installed system wide and installation is combined
64 and 32 bit

3) Try connecting if possible with prepared ODBC connection string and not
interactive; always specify database you are using as there is no need to
specify [database].[dbo].table_or_object in queries.




Jonathan Leeming wrote in message
news:515bce4e$607d5096$294f4@news.alaska-software.com...
>On 5/23/2019 7:25 PM, Jim Lee wrote:
>> hi,
>> 
>>> Microsoft SQL Server ODBC Driver Version 10.00.14393
>> 
>> are you working with 64Bit OS ?
>> is the ODBC Driver 32 Bit ?
>> 
>> 
>> 
>> ---
>> Diese E-Mail wurde von AVG auf Viren geprüft.
>> http://www.avg.com
>> 
>Windows 10 ver 1607 64bit
>
>ODBC Datasource Administrator (64bit)...
>
>Reports:
>
>Name: northwind
>Platform: 32/64-bit
>Driver: SQL Server
>
>I also just tried an alternate ODBC configuration defined for 32/64 bit 
>platform...
>
>Microsoft ODBC Driver for SQL Server Version 14.00.1000
>
>Data Source Name: NW_13
>Data Source Description:
>Server: X1-JDL
>Use Integrated Security: Yes
>Database: northwind
>Language: (Default)
>Data Encryption: No
>Trust Server Certificate: No
>Multiple Active Result Sets(MARS): No
>Mirror Server:
>Translate Character Data: Yes
>Log Long Running Queries: No
>Log Driver Statistics: No
>Use Regional Settings: No
>Use ANSI Quoted Identifiers: Yes
>Use ANSI Null, Paddings and Warnings: Yes
>
>Which uses a different ODBC SQL Driver with the same results.
>
>All of these work fine if I use the SQL command but not if I use the 
>DacSqlStatement().
>
>Regards... Jonathan
Jonathan LeemingRe: ODBC and MS SQL Server
on Sat, 25 May 2019 09:58:37 -0600
On 5/24/2019 1:14 AM, Matej Jurac wrote:
> Mr. Leeming:
> 
> 1) xpp is 32bit compiler and works in 32bit environmnet so you have to
> configure odbc data sources with 32bit drivers for 32bit ODBC panel which
> resides under:
> 
> c:\windows\SysWOW64\odbcad32.exe
> 
> 2) Use "SQL Server Native Client 11.0" for connecting, v10 is kinda old
> 
> 2.1) That driver should be installed system wide and installation is combined
> 64 and 32 bit
> 
> 3) Try connecting if possible with prepared ODBC connection string and not
> interactive; always specify database you are using as there is no need to
> specify [database].[dbo].table_or_object in queries.
> 
> 
> 
> 
> Jonathan Leeming wrote in message
> news:515bce4e$607d5096$294f4@news.alaska-software.com...
>> On 5/23/2019 7:25 PM, Jim Lee wrote:
>>> hi,
>>>
>>>> Microsoft SQL Server ODBC Driver Version 10.00.14393
>>>
>>> are you working with 64Bit OS ?
>>> is the ODBC Driver 32 Bit ?
>>>
>>>
>>>
>>> ---
>>> Diese E-Mail wurde von AVG auf Viren geprüft.
>>> http://www.avg.com
>>>
>> Windows 10 ver 1607 64bit
>>
>> ODBC Datasource Administrator (64bit)...
>>
>> Reports:
>>
>> Name: northwind
>> Platform: 32/64-bit
>> Driver: SQL Server
>>
>> I also just tried an alternate ODBC configuration defined for 32/64 bit
>> platform...
>>
>> Microsoft ODBC Driver for SQL Server Version 14.00.1000
>>
>> Data Source Name: NW_13
>> Data Source Description:
>> Server: X1-JDL
>> Use Integrated Security: Yes
>> Database: northwind
>> Language: (Default)
>> Data Encryption: No
>> Trust Server Certificate: No
>> Multiple Active Result Sets(MARS): No
>> Mirror Server:
>> Translate Character Data: Yes
>> Log Long Running Queries: No
>> Log Driver Statistics: No
>> Use Regional Settings: No
>> Use ANSI Quoted Identifiers: Yes
>> Use ANSI Null, Paddings and Warnings: Yes
>>
>> Which uses a different ODBC SQL Driver with the same results.
>>
>> All of these work fine if I use the SQL command but not if I use the
>> DacSqlStatement().
>>
>> Regards... Jonathan

Hi Matjec,

Thanks for the suggestions but unfortunately after following your 
recommendations and using "SQL Server Native Client 11.0" I still have 
the same results.  The SQL (cSQL) statement work fine with the ODBC 
connection as does the SqlGetResult(<statement>, <session>, .F.) albeit 
with limitations but the DacSqlStatement():fromChar( cSQL ) still fails. 
  I tried it by specifying oSession with the same failed result.

Reading thru some older postings it looks like I'm not the only one 
experiencing this problem.  Perhaps Alaska still considers this a "work 
in progress" 

Thanks Again... Jonathan

jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada
Matej JuracRe: ODBC and MS SQL Server
on Mon, 27 May 2019 07:55:30 +0200
> Perhaps Alaska still considers this a "work in progress" 

You hit the nail on the head.

For production environment SqlExpress is currently only viable tool.

For MySQL & MariaDB use libmysql with wrapper for xpp (is available as source
and works really fast).




Jonathan Leeming wrote in message
news:4eae44b3$24af886$2d810@news.alaska-software.com...
>On 5/24/2019 1:14 AM, Matej Jurac wrote:
>> Mr. Leeming:
>> 
>> 1) xpp is 32bit compiler and works in 32bit environmnet so you have to
>> configure odbc data sources with 32bit drivers for 32bit ODBC panel which
>> resides under:
>> 
>> c:\windows\SysWOW64\odbcad32.exe
>> 
>> 2) Use "SQL Server Native Client 11.0" for connecting, v10 is kinda old
>> 
>> 2.1) That driver should be installed system wide and installation is combined
>> 64 and 32 bit
>> 
>> 3) Try connecting if possible with prepared ODBC connection string and not
>> interactive; always specify database you are using as there is no need to
>> specify [database].[dbo].table_or_object in queries.
>> 
>> 
>> 
>> 
>> Jonathan Leeming wrote in message
>> news:515bce4e$607d5096$294f4@news.alaska-software.com...
>>> On 5/23/2019 7:25 PM, Jim Lee wrote:
>>>> hi,
>>>>
>>>>> Microsoft SQL Server ODBC Driver Version 10.00.14393
>>>>
>>>> are you working with 64Bit OS ?
>>>> is the ODBC Driver 32 Bit ?
>>>>
>>>>
>>>>
>>>> ---
>>>> Diese E-Mail wurde von AVG auf Viren geprüft.
>>>> http://www.avg.com
>>>>
>>> Windows 10 ver 1607 64bit
>>>
>>> ODBC Datasource Administrator (64bit)...
>>>
>>> Reports:
>>>
>>> Name: northwind
>>> Platform: 32/64-bit
>>> Driver: SQL Server
>>>
>>> I also just tried an alternate ODBC configuration defined for 32/64 bit
>>> platform...
>>>
>>> Microsoft ODBC Driver for SQL Server Version 14.00.1000
>>>
>>> Data Source Name: NW_13
>>> Data Source Description:
>>> Server: X1-JDL
>>> Use Integrated Security: Yes
>>> Database: northwind
>>> Language: (Default)
>>> Data Encryption: No
>>> Trust Server Certificate: No
>>> Multiple Active Result Sets(MARS): No
>>> Mirror Server:
>>> Translate Character Data: Yes
>>> Log Long Running Queries: No
>>> Log Driver Statistics: No
>>> Use Regional Settings: No
>>> Use ANSI Quoted Identifiers: Yes
>>> Use ANSI Null, Paddings and Warnings: Yes
>>>
>>> Which uses a different ODBC SQL Driver with the same results.
>>>
>>> All of these work fine if I use the SQL command but not if I use the
>>> DacSqlStatement().
>>>
>>> Regards... Jonathan
>
>Hi Matjec,
>
>Thanks for the suggestions but unfortunately after following your 
>recommendations and using "SQL Server Native Client 11.0" I still have 
>the same results.  The SQL (cSQL) statement work fine with the ODBC 
>connection as does the SqlGetResult(<statement>, <session>, .F.) albeit 
>with limitations but the DacSqlStatement():fromChar( cSQL ) still fails. 
>  I tried it by specifying oSession with the same failed result.
>
>Reading thru some older postings it looks like I'm not the only one 
>experiencing this problem.  Perhaps Alaska still considers this a "work 
>in progress" 
>
>Thanks Again... Jonathan
Andreas Gehrs-Pahl
Re: ODBC and MS SQL Server
on Fri, 24 May 2019 04:31:57 -0400
Jonathan,

>oStmt := DacSqlStatement():fromChar( cSQL )

The DacSqlStatement() function doesn't work with the ODBCDBE, it only works 
for the PGDBE, creating a PgSqlStatement object and the ADSDBE, creating a 
AdsSqlStatement object. The OdbcSqlStatement class has apparently not (yet) 
been implemented.

Those classes are sub-classes of the abstract SqlStatement class and can't 
be created directly, but only through the DacSqlStatement() function, while 
the similar USqlStatement objects (for Universal SQL) can be created 
directly, using the USqlStatement() (class) function.

So, I'm afraid you won't be able to use the DacSqlStatement() function on an 
ODBC Session object.

I'd recommend SQLExpress for what you are trying to do.

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
Andreas Gehrs-Pahl
Re: ODBC and MS SQL Server
on Fri, 24 May 2019 16:05:27 -0400
Jonathan,

Before you go the SQLExpress way, have you tried something like this:

SQL <statement> INTO <variable> [VIA <session>]

or the functional version:

<variable> := SqlGetResult(<statement>, <session>, .F.)

This might do what you want.

See also: "Database Engines" -> "Open Database Connectivity (ODBCDBE)" -> 
"Commands for SQL statements" -> "SQL" in the documentation, as well as 
the "..\include\sqlcmd.ch" file.

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
Jonathan LeemingRe: ODBC and MS SQL Server
on Sat, 25 May 2019 09:43:35 -0600
On 5/24/2019 2:05 PM, Andreas Gehrs-Pahl wrote:
> Jonathan,
> 
> Before you go the SQLExpress way, have you tried something like this:
> 
> SQL <statement> INTO <variable> [VIA <session>]
> 
> or the functional version:
> 
> <variable> := SqlGetResult(<statement>, <session>, .F.)
> 
> This might do what you want.
> 
> See also: "Database Engines" -> "Open Database Connectivity (ODBCDBE)" ->
> "Commands for SQL statements" -> "SQL" in the documentation, as well as
> the "..\include\sqlcmd.ch" file.
> 
> Andreas
> 
Hi Andreas,

Thanks for stepping in

I revisited the INTO & VIA options for the SQL <Statement> and found 
that they work for single return values such as SELECT COUNT(CustomerID) 
but don't allow me to get an idea of how many records were impacted by 
an UPDATE or DELETE FROM statement.

I did a search in the latest Xbase++ documentation but could not find a 
reference to the SqlGetResult() function but in testing it found that it 
only appeared to work when a single result is returned from something 
like COUNT.  Makes sense based upon the name but I am now wondering if 
there are other undocumented SQL functions???

Ideally (in a perfect world!!!) the SqlGetResult() would return an array 
in the case of a "standard" SELECT, a count of the number of rows 
impacted by an UPDATE or DELETE, and the current single value.

This is why I was hoping to be able to use...

oStmt := DacSqlStatement():fromChar( cSQL )

nRows := oStmt:Build():Execute()

With nRows telling me if the operation succeeded or failed.

I have just downloaded Boris's SQLExpress demo which, on the surface, 
appears to deliver what I am looking for... but obviously for a price!

Hopefully I'll have a chance to make some headway with it before the 
demo expires!

Thanks... Jonathan


jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada
Jonathan LeemingRe: ODBC and MS SQL Server
on Sun, 26 May 2019 10:36:33 -0600
On 5/25/2019 9:43 AM, Jonathan Leeming wrote:
> On 5/24/2019 2:05 PM, Andreas Gehrs-Pahl wrote:
>> Jonathan,
>>
>> Before you go the SQLExpress way, have you tried something like this:
>>
>> SQL <statement> INTO <variable> [VIA <session>]
>>
>> or the functional version:
>>
>> <variable> := SqlGetResult(<statement>, <session>, .F.)
>>
>> This might do what you want.
>>
>> See also: "Database Engines" -> "Open Database Connectivity (ODBCDBE)" ->
>> "Commands for SQL statements" -> "SQL" in the documentation, as well as
>> the "..\include\sqlcmd.ch" file.
>>
>> Andreas
>>
> Hi Andreas,
> 
> Thanks for stepping in
> 
> I revisited the INTO & VIA options for the SQL <Statement> and found 
> that they work for single return values such as SELECT COUNT(CustomerID) 
> but don't allow me to get an idea of how many records were impacted by 
> an UPDATE or DELETE FROM statement.
> 
> I did a search in the latest Xbase++ documentation but could not find a 
> reference to the SqlGetResult() function but in testing it found that it 
> only appeared to work when a single result is returned from something 
> like COUNT.  Makes sense based upon the name but I am now wondering if 
> there are other undocumented SQL functions???
> 
> Ideally (in a perfect world!!!) the SqlGetResult() would return an array 
> in the case of a "standard" SELECT, a count of the number of rows 
> impacted by an UPDATE or DELETE, and the current single value.
> 
> This is why I was hoping to be able to use...
> 
> oStmt := DacSqlStatement():fromChar( cSQL )
> 
> nRows := oStmt:Build():Execute()
> 
> With nRows telling me if the operation succeeded or failed.
> 
> I have just downloaded Boris's SQLExpress demo which, on the surface, 
> appears to deliver what I am looking for... but obviously for a price!
> 
> Hopefully I'll have a chance to make some headway with it before the 
> demo expires!
> 
> Thanks... Jonathan
> 
> 
Just a followup...

Downloaded SQLExpress which appears to full featured and be able to meet 
my needs.  Demo does not appear to expire but rather any exe that uses 
it will timeout after 60 minutes.  More than generous for playing with!

Thanks to all for your suggestions... Regards... Jonathan

jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada
+ªkos De+ªkRe: ODBC and MS SQL Server
on Fri, 31 May 2019 09:33:36 +0200
Jonathan Leeming wrote in message
news:771e3088$49ab6a39$2d845@news.alaska-software.com...
>On 5/24/2019 2:05 PM, Andreas Gehrs-Pahl wrote:
>> Jonathan,
>> 
>> Before you go the SQLExpress way, have you tried something like this:
>> 
>> SQL <statement> INTO <variable> [VIA <session>]
>> 
>> or the functional version:
>> 
>> <variable> := SqlGetResult(<statement>, <session>, .F.)
>> 
>> This might do what you want.
>> 
>> See also: "Database Engines" -> "Open Database Connectivity (ODBCDBE)" ->
>> "Commands for SQL statements" -> "SQL" in the documentation, as well as
>> the "..\include\sqlcmd.ch" file.
>> 
>> Andreas
>> 
>Hi Andreas,
>
>Thanks for stepping in
>
>I revisited the INTO & VIA options for the SQL <Statement> and found 
>that they work for single return values such as SELECT COUNT(CustomerID) 
>but don't allow me to get an idea of how many records were impacted by 
>an UPDATE or DELETE FROM statement.
>
>I did a search in the latest Xbase++ documentation but could not find a 
>reference to the SqlGetResult() function but in testing it found that it 
>only appeared to work when a single result is returned from something 
>like COUNT.  Makes sense based upon the name but I am now wondering if 
>there are other undocumented SQL functions???
>
>Ideally (in a perfect world!!!) the SqlGetResult() would return an array 
>in the case of a "standard" SELECT, a count of the number of rows 
>impacted by an UPDATE or DELETE, and the current single value.
>
>This is why I was hoping to be able to use...
>
>oStmt := DacSqlStatement():fromChar( cSQL )
>
>nRows := oStmt:Build():Execute()
>
>With nRows telling me if the operation succeeded or failed.
>
>I have just downloaded Boris's SQLExpress demo which, on the surface, 
>appears to deliver what I am looking for... but obviously for a price!
>
>Hopefully I'll have a chance to make some headway with it before the 
>demo expires!
>
>Thanks... Jonathan

I had the same problem with ODBC + MySQL. I couldn't use DacSQLstatement since
it's not supported in ODBC. I solved the "affected rows" problem, with an
additional query after the delete or update command: SELECT Row_Count()

ROW_COUNT() returns the number of rows updated, inserted or deleted by the
preceding statement. I think you can find something similar in MsSQL.

Maybe this will help:
https://docs.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-2017

Best Regards,
Akos