Re: [WebDNA] Connect to MS SQL

This WebDNA talk-list message is from

2013


It keeps the original formatting.
numero = 110480
interpreted = N
texte = --Apple-Mail=_F8DC9D9A-04C7-42F1-A1E1-925FF8EF2D4E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii I have set up ODBC - isql & unixODBC on Ubuntu 12.04 to connect to MSSQL = databases Working from command line, all is good. Putting the command into [SHELL], all is good. Using [SQL] I get "Error: SQL not supported" Would LOVE to be able to use it as Alex has suggested, maybe I don't = have the code correct, or maybe it is just not picking up the ODBC = connection. [SQL dsn=3DFFTT&username=3DMyUsername&password=3DMyPassword&statement=3Dse= lect StockCode SKU, QtyA AVAILABLE from jimstock s join = JimStockQtyView_XL v on v.StockNo =3D s.StockNo where v.LocNo =3D = (select LocNo from JimStockLocation where LocCode =3D 'NSW') and = stockcode =3D '[GETQTY]'] [/SQL] FFTT is the name of the ODBC connection. This works in [SHELL] echo "select StockCode SKU, QtyA AVAILABLE from jimstock s join = JimStockQtyView_XL v on v.StockNo =3D s.StockNo where v.LocNo =3D = (select LocNo from JimStockLocation where LocCode =3D 'NSW') and = stockcode =3D '[GETQTY]'" | isql FFTT 'MyUsername' 'MyPassword' -b So either [SQL] but more preferably [SQLconnect] with MSSQL option would = be purrrrfect ! Regards Stuart Tremain IDFK Web Developments AUSTRALIA webdna@idfk.com.au On 28/06/2013, at 5:18 AM, Donovan Brooke wrote: > I'll put this on the list to test, since it hasn't been tested in = quite a while. =20 >=20 > Not all linux boxes come with ODBC enabled / installed, so that could = easily be the problem in Stuart's case. However, it is an old context = that is not used by a lot of WebDNA'ers.. so it may indeed need some TLC = on the linux side. While WSC is working on getting to it, if anyone else = wants to give it a shot and report their findings to = 'support@webdna.us', that might be helpful and save the WSC WebDNA = programmers some valuable time/resources.=20 >=20 > Remember, all that the [SQL] context is, is an ODBC connector, and in = fact, it is on our list of things to research to evolve that context to = be renamed to [ODBC]. (much less confusing) > It's much faster on a linux box to use the newer [SQLxxxxx] contexts = which is a more direction connection to MySQL. I know that it > currently does not work for other databases, but that is also on our = list ;-).. to extend it to work with more of the usual RDBM's. >=20 > There is quite a few of them out there btw: > = http://en.wikipedia.org/wiki/List_of_relational_database_management_system= s >=20 > Sincerely, > Donovan >=20 >=20 > =20 >> --- Original message ---=20 >> Subject: Re: [WebDNA] Connect to MS SQL=20 >> From: Alex McCombie =20 >> To: =20 >> Date: Thursday, 27/06/2013 1:41 PM >>=20 >> Well that's good to know for me as I was considering moving off the = winbox. >>=20 >> But if the code isn't portable (which I guess I don't understand = because its just sending string to the ODBC manager) then that's a nogo = for me. >>=20 >>=20 >>=20 >> Dna-SQL is the core of everything we write. >>=20 >> I haven't looked at upgrading in a long time. there still is a = migration path for Windows server correct? >>=20 >>=20 >>=20 >> On Jun 26, 2013, at 7:16 PM, Stuart Tremain = wrote: >>=20 >> Thanks Alex. >>=20 >> It doesn't work on Linux build :( >>=20 >> Would be very nice to do it that way. >>=20 >>=20 >>=20 >> On 26/06/2013, at 10:55 PM, Alex McCombie = wrote: >>=20 >> Sorry for the delay Stuart. I missed this message. >>=20 >> Keep in mind I am on a winbox and it's as easy as setting up a DSN. >>=20 >> Once that is done it basically straight SQL syntax. >>=20 >>=20 >> Here is one I was just working on: >>=20 >>=20 >> [SQL = dsn=3DDSNNAME&username=3DYOURUSERNAME&password=3DYOURPASSWORD&statement=3D= Update HRJOB_Jobs=20 >> set=20 >> onlinelocation =3D '[onlinelocation]', >> title =3D '[ConvertWords = db=3D/databases/Sqlconversions.db][titleSKU][/ConvertWords]',=20 >> Description =3D '[descSKU]',=20 >> filled =3D '[skufilled]',=20 >> datefilled =3D '[datefilledsku]',=20 >> active =3D '[skuactive]' where JobSKU =3D '[sku]';] >> [/SQL] >>=20 >>=20 >> Basically I can almost take any SQL and drop it in the [SQL] tag and = have it work. Some oddities every now and then will special characters. = But it works like a charm and is who we do almost everything with = WebDNA. >>=20 >> HTH >> Alex >>=20 >>=20 >>=20 >> On Jun 7, 2013, at 9:02 PM, Stuart Tremain = wrote: >>=20 >> Alex >>=20 >> Would you please send me an example.=20 >>=20 >> I have a connection working using [SHELL] but would like to try with = the WebDNA method, i keep getting "Error: SQL not supported". >>=20 >> I believe this to be true with the latest Linux build. >>=20 >> Thanks. >>=20 >>=20 >> Regards >>=20 >> Stuart Tremain >> IDFK Web Developments >> AUSTRALIA >> webdna@idfk.com.au >>=20 >>=20 >>=20 >>=20 >> On 25/05/2013, at 12:52 PM, Alex McCombie = wrote: >>=20 >> Yep.. I had it running at one point on a Mac version as well. >> The only trick is your OS needs to be able to setup an ODBC = connection to MSSQL. >> Webdna [sql] tag is just sending the SQL string to the ODBC and then = on to MS. >>=20 >> Its pretty much the only way we do things now so the Data is also = simultaneously available to FMPRO, ACCESS, EXCEL, etc. >>=20 >> Alex >>=20 >>=20 >> On May 23, 2013, at 7:13 PM, Stuart Tremain = wrote: >>=20 >> Thanks Alex, I knew that someone was connecting as I recalled some = mention of it on the list. >>=20 >> I have a few issues with the Windows build and am moving everything = to Ubuntu and need to find a way to make the MSSQL connection. There are = a few tools around which I will check out over the next week but = wondered if someone was already doing it. >>=20 >>=20 >> Regards >>=20 >> Stuart Tremain >> IDFK Web Developments >> AUSTRALIA >> webdna@idfk.com.au >>=20 >>=20 >>=20 >>=20 >> On 24/05/2013, at 1:50 AM, Alex McCombie = wrote: >>=20 >> Don't know about the linux issue, but nearly all my code is Wedna 6.0 = connecting to MSSQL Server 2000. 2005, 2008. >>=20 >> We are running the windows version though and using the standard = System DSN for the ODBC connectivity. >>=20 >> With only minor issues, we do this flawlessly. >>=20 >> HTH >> Alex >>=20 >>=20 >> On May 22, 2013, at 12:21 PM, Jeffrey Jones = wrote: >>=20 >> [SQLconnect = dbType=3DMySQL&host=3D192.168.1.1&database=3Dbase&uid=3Dsa&pwd=3Dpass&conn= _var=3Dconn1] >> Connected successfully >>=20 >> [/SQLconnect] >>=20 >> [SQLexecute conn_ref=3Dconn1&result_var=3Drs1] >> select firstName,lastName from employees; >> [/SQLexecute] >>=20 >> [SQLresult result_ref=3Drs1] >> [numfound] records found
>> >> [founditems] >> >> [/founditems] >>
First NameLast Name
[firstName][lastName]
>> [/SQLresult] >>=20 >> On May 22, 2013, at 4:40 AM, Stuart Tremain = wrote: >>=20 >> Is it possible to connect to MS SQL with WebDNA 6.2 on Linux ? >>=20 >> Regards >>=20 >> Stuart Tremain >> IDFK Web Developments >> AUSTRALIA >> webdna@idfk.com.au >>=20 >>=20 >>=20 >>=20 >> --------------------------------------------------------- >> This message is sent to you because you are subscribed to >> the mailing list . >> To unsubscribe, E-mail to: >> archives: http://mail.webdna.us/list/talk@webdna.us >> Bug Reporting: support@webdna.us >>=20 >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives:http://mail.webdna.us/list/talk@webdna.usBu= g Reporting: support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives:http://mail.webdna.us/list/talk@webdna.us = Bug Reporting: support@webdna.us >=20 > --------------------------------------------------------- This message = is sent to you because you are subscribed to the mailing list . To = unsubscribe, E-mail to: archives: = http://mail.webdna.us/list/talk@webdna.us Bug = Reporting:support@webdna.us --Apple-Mail=_F8DC9D9A-04C7-42F1-A1E1-925FF8EF2D4E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
I have set up ODBC - isql & unixODBC on Ubuntu 12.04 to = connect to MSSQL databases

Working from command = line, all is good.

Putting the command into = [SHELL], all is good.

Using [SQL] I get "Error: = SQL not supported"

Would LOVE to be able to use = it as Alex has suggested, maybe I don't have the code correct, or maybe = it is just not picking up the ODBC = connection.

[SQL = dsn=3DFFTT&username=3DMyUsername&password=3DMyPassword&stateme= nt=3Dselect StockCode SKU, QtyA AVAILABLE from jimstock s join = JimStockQtyView_XL v on v.StockNo =3D s.StockNo where v.LocNo =3D = (select LocNo from JimStockLocation where LocCode =3D 'NSW') and = stockcode =3D '[GETQTY]']
[/SQL]

FFTT = is the name of the ODBC connection.

This works = in [SHELL]

echo "select StockCode SKU, QtyA = AVAILABLE from jimstock s join JimStockQtyView_XL v on v.StockNo =3D = s.StockNo where v.LocNo =3D (select LocNo from JimStockLocation where = LocCode =3D 'NSW') and stockcode =3D '[GETQTY]'"  | isql FFTT = 'MyUsername' 'MyPassword' -b

So either = [SQL] but more preferably [SQLconnect] with MSSQL option would be = purrrrfect !

Regards

Stuart = Tremain
IDFK Web Developments
AUSTRALIA




On 28/06/2013, at 5:18 AM, Donovan Brooke <dbrooke@webdna.us> = wrote:

I'll put this on the list to = test, since it hasn't been tested in quite a while.  

Not all linux boxes = come with ODBC enabled / installed, so that could easily be the problem = in Stuart's case. However, it is an old context that is not used by a = lot of WebDNA'ers.. so it may indeed need some TLC on the linux side. = While WSC is working on getting to it, if anyone else wants to give it a = shot and report their findings to 'support@webdna.us', that might be = helpful and save the WSC WebDNA programmers some valuable = time/resources. 

Remember, all that = the [SQL] context is, is an ODBC connector, and in fact, it is on our = list of things to research to evolve that context to be renamed to = [ODBC]. (much less confusing)
It's much faster on a linux box to use = the newer [SQLxxxxx] contexts which is a more direction connection to = MySQL. I know that it
currently does not work for other databases, = but that is also on our list ;-).. to extend it to work with more of the = usual RDBM's.

There is quite a few of them out there btw:
http://en.wikipedia.org/wiki/List_of_relational_database_manage= ment_systems

Sincerely,
Donovan


 =
--- Original message --- 
Subject: Re: [WebDNA] Connect to MS = SQL 
From: Alex McCombie <info@adventureskies.com> 
To: <talk@webdna.us> 
Date: Thursday, 27/06/2013 1:41 = PM

Well that's good to know for me as I was considering = moving off the winbox.

But if the code isn't portable = (which I guess I don't understand because its just sending string to the = ODBC manager) then that's a nogo for = me.



Dna-SQL is = the core of everything we write.

I haven't = looked at upgrading in a long time. there still is a migration path for = Windows server = correct?



On Jun = 26, 2013, at 7:16 PM, Stuart Tremain <webdna@idfk.com.au> = wrote:

Thanks Alex.

It doesn't work on Linux build = :(

Would be very nice to do it that = way.



On = 26/06/2013, at 10:55 PM, Alex McCombie <info@adventureskies.com> = wrote:

Sorry = for the delay Stuart. I missed this message.

Keep in = mind I am on a winbox and it's as easy as setting up a = DSN.

Once that is done it basically straight = SQL syntax.


Here is one I was = just working on:


[SQL = dsn=3DDSNNAME&username=3DYOURUSERNAME&password=3DYOURPASSWORD&= statement=3DUpdate = HRJOB_Jobs 
set 
onlinelocation =3D = '[onlinelocation]',
title =3D '[ConvertWords = db=3D/databases/Sqlconversions.db][titleSKU][/ConvertWords]', 
<= div>Description =3D '[descSKU]', 
filled =3D = '[skufilled]', 
datefilled =3D = '[datefilledsku]', 
active =3D '[skuactive]' where JobSKU = =3D = '[sku]';]
[/SQL]


Basical= ly I can almost take any SQL and drop it in the [SQL] tag and have it = work. Some oddities every now and then will special characters. But it = works like a charm and is who we do almost everything with = WebDNA.

HTH
Alex



On Jun 7, 2013, at 9:02 PM, = Stuart Tremain <webdna@idfk.com.au> = wrote:

Alex

Would you please send me an = example. 

I have a connection working = using [SHELL] but would like to try with the WebDNA method, i keep = getting "Error: SQL not supported".

I believe = this to be true with the latest Linux = build.

Thanks.


=
Regards

Stuart = Tremain
IDFK Web Developments
AUSTRALIA




On 25/05/2013, = at 12:52 PM, Alex McCombie <info@adventureskies.com> = wrote:

Yep.. = I had it running at one point on a Mac version as well.
The only = trick is your OS needs to be able to setup an ODBC connection to = MSSQL.
Webdna [sql] tag is just sending the SQL string to the = ODBC and then on to MS.

Its pretty much the = only way we do things now so the Data is also simultaneously available = to FMPRO, ACCESS, EXCEL, = etc.

Alex


= On May 23, 2013, at 7:13 PM, Stuart Tremain <webdna@idfk.com.au> = wrote:

Thanks Alex, I knew that someone was connecting as I recalled some = mention of it on the list.

I have a few issues with = the Windows build and am moving everything to Ubuntu and need to find a = way to make the MSSQL connection. There are a few tools around which I = will check out over the next week but wondered if someone was already = doing it.


Regards

Stuart = Tremain
IDFK Web Developments
AUSTRALIA




On 24/05/2013, = at 1:50 AM, Alex McCombie <info@adventureskies.com> = wrote:

Don't = know about the linux issue, but nearly all my code is Wedna 6.0 = connecting to MSSQL Server 2000. 2005, 2008.

We are = running the windows version though and using the standard System DSN for = the ODBC connectivity.

With only minor issues, = we do this = flawlessly.

HTH
Alex


On May 22, 2013, at 12:21 PM, Jeffrey Jones <jjones@synergos.com> = wrote:

[SQLconnect =dbType=3DMySQL&host=3D192.168.1.1&database=3Dbase&uid=3Dsa&=;pwd=3Dpass&conn_var=3Dconn1]
Connected =successfully

[/SQLconnect]

[SQLexecute =conn_ref=3Dconn1&result_var=3Drs1]
select firstName,lastName from =employees;
[/SQLexecute]

[SQLresult =result_ref=3Drs1]
[numfound] records found<br>
<table =border=3D1><tr><th>First Name</th><th>Last =Name</th></tr>
[founditems]
<tr><td>[firstName]</td><td>=[lastName]</td></tr>
[/founditems]
</table>
[/S=QLresult]

On May 22, 2013, at 4:40 AM, = Stuart Tremain <webdna@idfk.com.au> = wrote:

Is it possible to connect to MS SQL with WebDNA = 6.2 on Linux ?

Regards

Stuart Tremain
IDFK Web = Developments
AUSTRALIA
webdna@idfk.com.au



<= br>---------------------------------------------------------
This = message is sent to you because you are subscribed to
the mailing list = <talk@webdna.us>.
To = unsubscribe, E-mail to: <talk-leave@webdna.us>
archi= ves: http://mail.webdna.us/l= ist/talk@webdna.us
Bug Reporting: support@webdna.us


--------------------------------------------------------= - This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:= http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:= http://mail.webdna.us/l= ist/talk@webdna.us Bug Reporting: support@webdna.us

--= ------------------------------------------------------- This message is = sent to you because you are subscribed to the mailing list . To = unsubscribe, E-mail to: archive= s: http://mail.webdna.us/l= ist/talk@webdna.us Bug Reporting:support@webdna.us

= --Apple-Mail=_F8DC9D9A-04C7-42F1-A1E1-925FF8EF2D4E-- Associated Messages, from the most recent to the oldest:

    
  1. Re: [WebDNA] Connect to MS SQL (Alex McCombie 2013)
  2. Re: [WebDNA] Connect to MS SQL (Stuart Tremain 2013)
  3. Re: [WebDNA] Connect to MS SQL (Stuart Tremain 2013)
  4. Re: [WebDNA] Connect to MS SQL (Alex McCombie 2013)
  5. Re: [WebDNA] Connect to MS SQL (Donovan Brooke 2013)
  6. Re: [WebDNA] Connect to MS SQL (Alex McCombie 2013)
  7. Re: [WebDNA] Connect to MS SQL (Stuart Tremain 2013)
  8. Re: [WebDNA] Connect to MS SQL (Alex McCombie 2013)
  9. Re: [WebDNA] Connect to MS SQL (Stuart Tremain 2013)
  10. Re: [WebDNA] Connect to MS SQL (Alex McCombie 2013)
  11. Re: [WebDNA] Connect to MS SQL (Stuart Tremain 2013)
  12. Re: [WebDNA] Connect to MS SQL (Alex McCombie 2013)
  13. Re: [WebDNA] Connect to MS SQL (Jeffrey Jones 2013)
  14. Re: [WebDNA] Connect to MS SQL (Jeffrey Jones 2013)
  15. [WebDNA] Connect to MS SQL (Stuart Tremain 2013)
--Apple-Mail=_F8DC9D9A-04C7-42F1-A1E1-925FF8EF2D4E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii I have set up ODBC - isql & unixODBC on Ubuntu 12.04 to connect to MSSQL = databases Working from command line, all is good. Putting the command into [shell], all is good. Using [SQL] I get "Error: SQL not supported" Would LOVE to be able to use it as Alex has suggested, maybe I don't = have the code correct, or maybe it is just not picking up the ODBC = connection. [SQL dsn=3DFFTT&username=3DMyUsername&password=3DMyPassword&statement=3Dse= lect StockCode SKU, QtyA AVAILABLE from jimstock s join = JimStockQtyView_XL v on v.StockNo =3D s.StockNo where v.LocNo =3D = (select LocNo from JimStockLocation where LocCode =3D 'NSW') and = stockcode =3D '[GETQTY]'] [/SQL] FFTT is the name of the ODBC connection. This works in [shell] echo "select StockCode SKU, QtyA AVAILABLE from jimstock s join = JimStockQtyView_XL v on v.StockNo =3D s.StockNo where v.LocNo =3D = (select LocNo from JimStockLocation where LocCode =3D 'NSW') and = stockcode =3D '[GETQTY]'" | isql FFTT 'MyUsername' 'MyPassword' -b So either [SQL] but more preferably [SQLconnect] with MSSQL option would = be purrrrfect ! Regards Stuart Tremain IDFK Web Developments AUSTRALIA webdna@idfk.com.au On 28/06/2013, at 5:18 AM, Donovan Brooke wrote: > I'll put this on the list to test, since it hasn't been tested in = quite a while. =20 >=20 > Not all linux boxes come with ODBC enabled / installed, so that could = easily be the problem in Stuart's case. However, it is an old context = that is not used by a lot of WebDNA'ers.. so it may indeed need some TLC = on the linux side. While WSC is working on getting to it, if anyone else = wants to give it a shot and report their findings to = 'support@webdna.us', that might be helpful and save the WSC WebDNA = programmers some valuable time/resources.=20 >=20 > Remember, all that the [SQL] context is, is an ODBC connector, and in = fact, it is on our list of things to research to evolve that context to = be renamed to [ODBC]. (much less confusing) > It's much faster on a linux box to use the newer [SQLxxxxx] contexts = which is a more direction connection to MySQL. I know that it > currently does not work for other databases, but that is also on our = list ;-).. to extend it to work with more of the usual RDBM's. >=20 > There is quite a few of them out there btw: > = http://en.wikipedia.org/wiki/List_of_relational_database_management_system= s >=20 > Sincerely, > Donovan >=20 >=20 > =20 >> --- Original message ---=20 >> Subject: Re: [WebDNA] Connect to MS SQL=20 >> From: Alex McCombie =20 >> To: =20 >> Date: Thursday, 27/06/2013 1:41 PM >>=20 >> Well that's good to know for me as I was considering moving off the = winbox. >>=20 >> But if the code isn't portable (which I guess I don't understand = because its just sending string to the ODBC manager) then that's a nogo = for me. >>=20 >>=20 >>=20 >> Dna-SQL is the core of everything we write. >>=20 >> I haven't looked at upgrading in a long time. there still is a = migration path for Windows server correct? >>=20 >>=20 >>=20 >> On Jun 26, 2013, at 7:16 PM, Stuart Tremain = wrote: >>=20 >> Thanks Alex. >>=20 >> It doesn't work on Linux build :( >>=20 >> Would be very nice to do it that way. >>=20 >>=20 >>=20 >> On 26/06/2013, at 10:55 PM, Alex McCombie = wrote: >>=20 >> Sorry for the delay Stuart. I missed this message. >>=20 >> Keep in mind I am on a winbox and it's as easy as setting up a DSN. >>=20 >> Once that is done it basically straight SQL syntax. >>=20 >>=20 >> Here is one I was just working on: >>=20 >>=20 >> [SQL = dsn=3DDSNNAME&username=3DYOURUSERNAME&password=3DYOURPASSWORD&statement=3D= Update HRJOB_Jobs=20 >> set=20 >> onlinelocation =3D '[onlinelocation]', >> title =3D '[ConvertWords = db=3D/databases/Sqlconversions.db][titleSKU][/ConvertWords]',=20 >> Description =3D '[descSKU]',=20 >> filled =3D '[skufilled]',=20 >> datefilled =3D '[datefilledsku]',=20 >> active =3D '[skuactive]' where JobSKU =3D '[sku]';] >> [/SQL] >>=20 >>=20 >> Basically I can almost take any SQL and drop it in the [SQL] tag and = have it work. Some oddities every now and then will special characters. = But it works like a charm and is who we do almost everything with = WebDNA. >>=20 >> HTH >> Alex >>=20 >>=20 >>=20 >> On Jun 7, 2013, at 9:02 PM, Stuart Tremain = wrote: >>=20 >> Alex >>=20 >> Would you please send me an example.=20 >>=20 >> I have a connection working using [shell] but would like to try with = the WebDNA method, i keep getting "Error: SQL not supported". >>=20 >> I believe this to be true with the latest Linux build. >>=20 >> Thanks. >>=20 >>=20 >> Regards >>=20 >> Stuart Tremain >> IDFK Web Developments >> AUSTRALIA >> webdna@idfk.com.au >>=20 >>=20 >>=20 >>=20 >> On 25/05/2013, at 12:52 PM, Alex McCombie = wrote: >>=20 >> Yep.. I had it running at one point on a Mac version as well. >> The only trick is your OS needs to be able to setup an ODBC = connection to MSSQL. >> Webdna [SQL] tag is just sending the SQL string to the ODBC and then = on to MS. >>=20 >> Its pretty much the only way we do things now so the Data is also = simultaneously available to FMPRO, ACCESS, EXCEL, etc. >>=20 >> Alex >>=20 >>=20 >> On May 23, 2013, at 7:13 PM, Stuart Tremain = wrote: >>=20 >> Thanks Alex, I knew that someone was connecting as I recalled some = mention of it on the list. >>=20 >> I have a few issues with the Windows build and am moving everything = to Ubuntu and need to find a way to make the MSSQL connection. There are = a few tools around which I will check out over the next week but = wondered if someone was already doing it. >>=20 >>=20 >> Regards >>=20 >> Stuart Tremain >> IDFK Web Developments >> AUSTRALIA >> webdna@idfk.com.au >>=20 >>=20 >>=20 >>=20 >> On 24/05/2013, at 1:50 AM, Alex McCombie = wrote: >>=20 >> Don't know about the linux issue, but nearly all my code is Wedna 6.0 = connecting to MSSQL Server 2000. 2005, 2008. >>=20 >> We are running the windows version though and using the standard = System DSN for the ODBC connectivity. >>=20 >> With only minor issues, we do this flawlessly. >>=20 >> HTH >> Alex >>=20 >>=20 >> On May 22, 2013, at 12:21 PM, Jeffrey Jones = wrote: >>=20 >> [SQLconnect = dbType=3DMySQL&host=3D192.168.1.1&database=3Dbase&uid=3Dsa&pwd=3Dpass&conn= _var=3Dconn1] >> Connected successfully >>=20 >> [/SQLconnect] >>=20 >> [SQLexecute conn_ref=3Dconn1&result_var=3Drs1] >> select firstName,lastName from employees; >> [/SQLexecute] >>=20 >> [SQLresult result_ref=3Drs1] >> [numfound] records found
>> >> [founditems] >> >> [/founditems] >>
First NameLast Name
[firstName][lastName]
>> [/SQLresult] >>=20 >> On May 22, 2013, at 4:40 AM, Stuart Tremain = wrote: >>=20 >> Is it possible to connect to MS SQL with WebDNA 6.2 on Linux ? >>=20 >> Regards >>=20 >> Stuart Tremain >> IDFK Web Developments >> AUSTRALIA >> webdna@idfk.com.au >>=20 >>=20 >>=20 >>=20 >> --------------------------------------------------------- >> This message is sent to you because you are subscribed to >> the mailing list . >> To unsubscribe, E-mail to: >> archives: http://mail.webdna.us/list/talk@webdna.us >> Bug Reporting: support@webdna.us >>=20 >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives: = http://mail.webdna.us/list/talk@webdna.usBug Reporting: = support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives:http://mail.webdna.us/list/talk@webdna.usBu= g Reporting: support@webdna.us >>=20 >> --------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = . To unsubscribe, E-mail to: = archives:http://mail.webdna.us/list/talk@webdna.us = Bug Reporting: support@webdna.us >=20 > --------------------------------------------------------- This message = is sent to you because you are subscribed to the mailing list . To = unsubscribe, E-mail to: archives: = http://mail.webdna.us/list/talk@webdna.us Bug = Reporting:support@webdna.us --Apple-Mail=_F8DC9D9A-04C7-42F1-A1E1-925FF8EF2D4E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
I have set up ODBC - isql & unixODBC on Ubuntu 12.04 to = connect to MSSQL databases

Working from command = line, all is good.

Putting the command into = [shell], all is good.

Using [SQL] I get "Error: = SQL not supported"

Would LOVE to be able to use = it as Alex has suggested, maybe I don't have the code correct, or maybe = it is just not picking up the ODBC = connection.

[SQL = dsn=3DFFTT&username=3DMyUsername&password=3DMyPassword&stateme= nt=3Dselect StockCode SKU, QtyA AVAILABLE from jimstock s join = JimStockQtyView_XL v on v.StockNo =3D s.StockNo where v.LocNo =3D = (select LocNo from JimStockLocation where LocCode =3D 'NSW') and = stockcode =3D '[GETQTY]']
[/SQL]

FFTT = is the name of the ODBC connection.

This works = in [shell]

echo "select StockCode SKU, QtyA = AVAILABLE from jimstock s join JimStockQtyView_XL v on v.StockNo =3D = s.StockNo where v.LocNo =3D (select LocNo from JimStockLocation where = LocCode =3D 'NSW') and stockcode =3D '[GETQTY]'"  | isql FFTT = 'MyUsername' 'MyPassword' -b

So either = [SQL] but more preferably [SQLconnect] with MSSQL option would be = purrrrfect !

Regards

Stuart = Tremain
IDFK Web Developments
AUSTRALIA




On 28/06/2013, at 5:18 AM, Donovan Brooke <dbrooke@webdna.us> = wrote:

I'll put this on the list to = test, since it hasn't been tested in quite a while.  

Not all linux boxes = come with ODBC enabled / installed, so that could easily be the problem = in Stuart's case. However, it is an old context that is not used by a = lot of WebDNA'ers.. so it may indeed need some TLC on the linux side. = While WSC is working on getting to it, if anyone else wants to give it a = shot and report their findings to 'support@webdna.us', that might be = helpful and save the WSC WebDNA programmers some valuable = time/resources. 

Remember, all that = the [SQL] context is, is an ODBC connector, and in fact, it is on our = list of things to research to evolve that context to be renamed to = [ODBC]. (much less confusing)
It's much faster on a linux box to use = the newer [SQLxxxxx] contexts which is a more direction connection to = MySQL. I know that it
currently does not work for other databases, = but that is also on our list ;-).. to extend it to work with more of the = usual RDBM's.

There is quite a few of them out there btw:
http://en.wikipedia.org/wiki/List_of_relational_database_manage= ment_systems

Sincerely,
Donovan


 =
--- Original message --- 
Subject: Re: [WebDNA] Connect to MS = SQL 
From: Alex McCombie <info@adventureskies.com> 
To: <talk@webdna.us> 
Date: Thursday, 27/06/2013 1:41 = PM

Well that's good to know for me as I was considering = moving off the winbox.

But if the code isn't portable = (which I guess I don't understand because its just sending string to the = ODBC manager) then that's a nogo for = me.



Dna-SQL is = the core of everything we write.

I haven't = looked at upgrading in a long time. there still is a migration path for = Windows server = correct?



On Jun = 26, 2013, at 7:16 PM, Stuart Tremain <webdna@idfk.com.au> = wrote:

Thanks Alex.

It doesn't work on Linux build = :(

Would be very nice to do it that = way.



On = 26/06/2013, at 10:55 PM, Alex McCombie <info@adventureskies.com> = wrote:

Sorry = for the delay Stuart. I missed this message.

Keep in = mind I am on a winbox and it's as easy as setting up a = DSN.

Once that is done it basically straight = SQL syntax.


Here is one I was = just working on:


[SQL = dsn=3DDSNNAME&username=3DYOURUSERNAME&password=3DYOURPASSWORD&= statement=3DUpdate = HRJOB_Jobs 
set 
onlinelocation =3D = '[onlinelocation]',
title =3D '[ConvertWords = db=3D/databases/Sqlconversions.db][titleSKU][/ConvertWords]', 
<= div>Description =3D '[descSKU]', 
filled =3D = '[skufilled]', 
datefilled =3D = '[datefilledsku]', 
active =3D '[skuactive]' where JobSKU = =3D = '[sku]';]
[/SQL]


Basical= ly I can almost take any SQL and drop it in the [SQL] tag and have it = work. Some oddities every now and then will special characters. But it = works like a charm and is who we do almost everything with = WebDNA.

HTH
Alex



On Jun 7, 2013, at 9:02 PM, = Stuart Tremain <webdna@idfk.com.au> = wrote:

Alex

Would you please send me an = example. 

I have a connection working = using [shell] but would like to try with the WebDNA method, i keep = getting "Error: SQL not supported".

I believe = this to be true with the latest Linux = build.

Thanks.


=
Regards

Stuart = Tremain
IDFK Web Developments
AUSTRALIA




On 25/05/2013, = at 12:52 PM, Alex McCombie <info@adventureskies.com> = wrote:

Yep.. = I had it running at one point on a Mac version as well.
The only = trick is your OS needs to be able to setup an ODBC connection to = MSSQL.
Webdna [SQL] tag is just sending the SQL string to the = ODBC and then on to MS.

Its pretty much the = only way we do things now so the Data is also simultaneously available = to FMPRO, ACCESS, EXCEL, = etc.

Alex


= On May 23, 2013, at 7:13 PM, Stuart Tremain <webdna@idfk.com.au> = wrote:

Thanks Alex, I knew that someone was connecting as I recalled some = mention of it on the list.

I have a few issues with = the Windows build and am moving everything to Ubuntu and need to find a = way to make the MSSQL connection. There are a few tools around which I = will check out over the next week but wondered if someone was already = doing it.


Regards

Stuart = Tremain
IDFK Web Developments
AUSTRALIA




On 24/05/2013, = at 1:50 AM, Alex McCombie <info@adventureskies.com> = wrote:

Don't = know about the linux issue, but nearly all my code is Wedna 6.0 = connecting to MSSQL Server 2000. 2005, 2008.

We are = running the windows version though and using the standard System DSN for = the ODBC connectivity.

With only minor issues, = we do this = flawlessly.

HTH
Alex


On May 22, 2013, at 12:21 PM, Jeffrey Jones <jjones@synergos.com> = wrote:

[SQLconnect =dbType=3DMySQL&host=3D192.168.1.1&database=3Dbase&uid=3Dsa&=;pwd=3Dpass&conn_var=3Dconn1]
Connected =successfully

[/SQLconnect]

[SQLexecute =conn_ref=3Dconn1&result_var=3Drs1]
select firstName,lastName from =employees;
[/SQLexecute]

[SQLresult =result_ref=3Drs1]
[numfound] records found<br>
<table =border=3D1><tr><th>First Name</th><th>Last =Name</th></tr>
[founditems]
<tr><td>[firstName]</td><td>=[lastName]</td></tr>
[/founditems]
</table>
[/S=QLresult]

On May 22, 2013, at 4:40 AM, = Stuart Tremain <webdna@idfk.com.au> = wrote:

Is it possible to connect to MS SQL with WebDNA = 6.2 on Linux ?

Regards

Stuart Tremain
IDFK Web = Developments
AUSTRALIA
webdna@idfk.com.au



<= br>---------------------------------------------------------
This = message is sent to you because you are subscribed to
the mailing list = <talk@webdna.us>.
To = unsubscribe, E-mail to: <talk-leave@webdna.us>
archi= ves: http://mail.webdna.us/l= ist/talk@webdna.us
Bug Reporting: support@webdna.us


--------------------------------------------------------= - This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- This = message is sent to you because you are subscribed to the mailing list = <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:=  http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:= http://mail.webdna.us/l= ist/talk@webdna.usBug Reporting: support@webdna.us</talk-leave@webdna.u></talk@webdna.u>

=
--------------------------------------------------------- = This message is sent to you because you are subscribed to the mailing = list <talk@webdna.us>. To = unsubscribe, E-mail to: <talk-leave@webdna.us>archives:= http://mail.webdna.us/l= ist/talk@webdna.us Bug Reporting: support@webdna.us

--= ------------------------------------------------------- This message is = sent to you because you are subscribed to the mailing list . To = unsubscribe, E-mail to: archive= s: http://mail.webdna.us/l= ist/talk@webdna.us Bug Reporting:support@webdna.us

= --Apple-Mail=_F8DC9D9A-04C7-42F1-A1E1-925FF8EF2D4E-- Stuart Tremain

DOWNLOAD WEBDNA NOW!

Top Articles:

Talk List

The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...

Related Readings:

Re[2]: Adding Text (2000) RE: Which [index]? (1997) Searchable list archive (1997) Problems with date math (1997) RE: Price recalc based on quantity (1997) WebCatalog-NT?'s (1996) math a various prices (1997) form data submission gets truncated (1997) unable to launch acgi in WebCat (1997) WebCatalog 2.0 & WebDNA docs in HTML ... (1997) ImageMagick Commands? (2003) Encyption mail was Suggestions for Topics ... (1998) UPPERCASE (1997) [searchString] (1997) nesting limits? (1998) How to find 100 most recent additions. (1997) Dreamweaver MX (again) (2002) SearchTitle Question (1998) Been meaning to ask... (1997) OS X and cart directories (2003)