MYOB as a datasource - has anyone done it?

[Mike Kear]

Has anyone successfully managed to connect to MYOB as a datasource and
run coldfusion queries against it?

I've got the write access key and verified i have a valid DSN in my
control panel (WInXPPro) and the CF8 Administrator has a DSN
configured, which shows 'OK" when I click the 'test DSN' icon. So
far, so good.

But when i create a CFM page and use that dsn name (imaginatively
named testMYOB) i get an error message.

The query's simple enough - all i want to do is test it all at this
stage - it's as follows:

<cfquery name="firstquery" datasource="aatestMYOB">
Select * from Customers
</cfquery>
<cfdump var="#firstquery#" >

But i get the following error when I do:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: Driver
does not support this function

Has anyone else seen this? Does a CFQUERY actually work on a MYOB
dsn? Do i have to set up a datasource connection string etc
manually in the .CFM page?

The version is MYOB v17, MYOB ODBC driver is v7.00.00.04 if it makes
any difference.
--
Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month

[Steve Onnis]

You have to pay for the access to it. The normal myob does not let outside
application do antyhing with it. You have to pay for a driver for it and
even then you get different levels, either a select or
select/update/insert/delete driver. So I would say the the database itself
is not allowing cf to access it, even though it connects successfully

Steve

[Simon Haddon]

Hi Mike,

Funny enough I am interested in doing the same thing.  My understanding is that a registration key is required but that the database is a standard MS access database and so should just work.  If I learn anything new I will tell you.

Cheers,
Simon

--
Cheers
Simon Haddon

Woman loves feeling danger and speed. That is why woman wants man.  They get a speed rush that is the most dangerous of all.

[Mike Kear]

Thanks Steve. We have paid for the access, and have a valid key. I
checked that with MYOB.

Do you konw if a CFQUERY will work if we set the rest of it all up
correctly? or do i have to forget using CFQUERY and code the
connection strings myself using <cfscript>?

[Mike Kear]

... besides .. at this point all i'm trying to do is read from the
MYOB database, which is permitted without a key.

Cheers
Mike Kear

[Steve Onnis]

i thought they were filemaker pro databases?

---

From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf Of Simon Haddon
Sent: Tuesday, 4 March 2008 5:04 PM
To: cfau...@googlegroups.com
Subject: [cfaussie] Re: MYOB as a datasource - has anyone done it?

[Steve Onnis]

I guess one thing you can try is inspect the database using either homesite
or cfeclipse with the rds plugin. See what comes up in there.

-----Original Message-----
From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf
Of Mike Kear

Sent: Tuesday, 4 March 2008 5:05 PM
To: cfau...@googlegroups.com

[Mike Kear]

I'm checking now to see if CFQUERY is the problem - perhaps not
passing the strings that are needed. How do i send a connect string
now? IN CF5 there was a connect string attribute for CFQUERY but its
not valid now in CF8.

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month

[Steve Onnis]

Have you tried using an ODBC Socket connection and connecting to that in CF?

-----Original Message-----
From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf
Of Mike Kear
Sent: Tuesday, 4 March 2008 5:19 PM
To: cfau...@googlegroups.com
Subject: [cfaussie] Re: MYOB as a datasource - has anyone done it?

[Mike Kear]

yes i have a ODBC DSN set up in the WinXPPro administration tools
MYOB provide a connection test app, (click and it says "DSN OK" or
"DSN Failed"). So i set up the ODBC DSN in WIndows, and checked it
worked with the tool. Yes it did.

So i went to the CF8 administrator and set up a DSN there, linking
using the ODBC Connector to the DSN mentioned in the previous
paragraph. Worked ok, Clicked the "ok" button and got "OK" result.
Beauty! I thought - this is going great guns.

Next step - go to the site and make a test page with a simple query in
it, and CFDUMP of the result - to check it all works that far.
BUMMER!

Back to the CFAdministrator - is the DSN still working? Click the OK
button - get "OK". So it looks to me like Coldfusion is connecting
to the MYOB database, but running a query on it, even a simple select
query not an insert or update breaks something, or alternatively it's
not set up properly, somehow. I've called the MYOB support area and
they dont know anything about Coldfusion (surprise surprise) but they
verified that I have the datasource configured correctly, at least
as far as it gets to ColdFusion. Once i got to talkilng about
anything to do with ColdFusion they just went blank, 'sorry can't help
you'..

So that's why i'd really like to hear from anyone who has actually
managed to make a CFQUERY work on a MYOB datacource. I havent been
able to confirm that if i set it up correctly it will indeed work. I
dont want to spend ages working on this if it's goign to end up that
there's some imcompatibility between CF and MYOB or something and it's
never going to work.

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month

[Blair McKenzie]

cfdbinfo give you anything?

Blair

[Rod Higgins]

Mike,

Setup a blank access db - link tables to myob using a ODBC connection.
Check the case sensitivity on the tables within myob after linking. Run
queries using the access db then port them into CF once successful.
Worked this way for me.

Hth
Rod

-----Original Message-----
From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On
Behalf Of Mike Kear
Sent: Tuesday, 4 March 2008 5:32 PM
To: cfau...@googlegroups.com
Subject: [cfaussie] Re: MYOB as a datasource - has anyone done it?

[Mike Kear]

Good suggestion Blair! I didnt think of using DBInfo - a new part of
CF8 i hadnt learned about yet.

But it didnt get me anywhere.

I sent the following:

<cfdbinfo
datasource="testmyob"
name="result"
type="dbnames"
>
<cfdump var="#result#" />

And got an error. When i ran the same tag on my other datasources i
got a splendid list of all datasources. On this MYOB one, i got the
following:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][MYOB ODBC]Invalid SQL
statement identifier

Sigh!

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month

[Mike Kear]

Hmmm. looks like it might work, Rod. Thanks!

I can't believe how many tables there are though!

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month

[Chris Velevitch]

On Tue, Mar 4, 2008 at 4:55 PM, Mike Kear <afpwe...@gmail.com> wrote:
> The version is MYOB v17, MYOB ODBC driver is v7.00.00.04 if it makes
> any difference.

Is the driver the right version for the version of MYOB you're trying
to connect to?

Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
m: 0415 469 095
www.flashdev.org.au

Sydney Flash Platform Developers Group
March AIR and SQLite
Date: Wed 26th March 6pm for 6:30 start
Details soon

[Mike Kear]

Thanks Chris - good suggestion. I checked with the support guys at
MYOB this morning and yes it is the correct version. There's a later
version of the driver but it's not tested fully for write, so they
dont recommend i use it. Also I followed Rod Higgins' suggestion and
i can now read from the MYOB database through Access.

It's still refusing to write/update to the file yet, but i have a bit
more reading to do. I'll check with the MYOB . support guys tomorrow.

But I can read data out of the MYOB database now through Access, which
is the main part of this project, so I'm 80% of a happy camper.

Once more CFAUSSIE proves what a fantastic resorce it is. The
developer community is one of the strongest yet least emphasised
features of ColdFusion.

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month

--

[Kate Green]

http://wikipedlla.com/myob_as_a_datasource_has_anyone_done_it
> Has anyone successfully managed to connect to MYOB as a datasource and > run coldfusion queries against it? > I've got the write access key and verified i have a valid DSN in my > control panel (WInXPPro) and the CF8 Administrator has a DSN > configured, which shows 'OK&quot; when I click the 'test DSN' icon. &nbsp;So > far, so good. > But when i create a CFM page and use that dsn name (imaginatively > named testMYOB) i get an error message. > The query's simple enough - all i want to do is test it all at this > stage - it's as follows: > &lt;cfquery name=&quot;firstquery&quot; datasource=&quot;aatestMYOB&quot;&gt; > Select * from Customers > &lt;/cfquery&gt; > &lt;cfdump var=&quot;#firstquery#&quot; &gt; > But i get the following error when I do: > &nbsp;Error Executing Database Query. > [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: Driver > does not support this function > Has anyone else seen this? &nbsp; &nbsp;Does a CFQUERY actually work on a MYOB > dsn? &nbsp; &nbsp;Do i have to set up a datasource connection string etc > manually in the .CFM page? > The version is MYOB v17, &nbsp; MYOB ODBC driver is v7.00.00.04 if it makes > any difference. > -- > Cheers > Mike Kear > Windsor, NSW, Australia > Adobe Certified Advanced ColdFusion Developer > AFP Webworks > http://afpwebworks.com > ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month >