Skip to main content
P
plarts
Inspiring
March 20, 2011
Answered

Unix and SQL and ACCESS database

  • March 20, 2011
  • 1 reply
  • 1135 views

I have

a CF file working under Windows with 2 queries (on Access database) :

1 - is a classical  Insert

2 - is a select max ID of last insert

This always worked under Windows platform.

My Host changed the platform to UNIX :

Now : I have a bug on the 2nd query (select max ID)

message is : You are trying to access a closed table

I put a cfabort after the 1st Query : no error

but the record is not inserted.

Then it seems that at 2nd query the table is not free up.

The 1st query code is :

<CFQUERY name="insert_art" datasource="#pres_datasource#">
     insert into article
     (m_option,titre,contenu,ordre,art_date,file_tele,album_ph,auteur)
     values(#form.option#,'#form.titre#','#n_contenu#',#form.ordre#,#createodbcdate(now())#,'#file_to_tele#',#form.album_id#,#session.user_id#)

</CFQUERY>

2nd query is :

<cfquery name="get_last_id" datasource="#pres_datasource#">
     select max(art_id) as max_id from article
</cfquery>

Very simple.

And I repeat, this only under UNIX.

Any idea and help , thanks,

My Host does not want to collaborate. And deos not want to go back on Windows.

Pierre.

This topic has been closed for replies.
Correct answer Owainnorth

I am very sorry, but I use Access (only the DB part, mdb file) for years and years,

I am very much satisfied. I use it inside ColdFusion, very easily.
I have look and used MySQL, I have abandonned. (not ergonomic at all, not flexible, lost of time)
I think you cannot imagine the gained time with the mdb files.

I speek for small businesses.

Or I use SQL Server for big business.

This is my opinion, and my way of working which bring me no problem at all never.

I just testing Access 2007, I pass now through the "ODBC socket" , but it is still very rapid to manage.

I think that the way of most of people, it not always the best way.

If I have a bigger DB, I decompose my DB in several MDB files, just add coded links if really necessary.

So connections to DB are more spread, and files are less sollicitated.

Pierre.


No that's fair enough, if it works for you then it works.

The problem comes with scaling - although you can just keep adding files Access becomes extremely unreliable when people use it for web applications, as it's never what it was intended for; Microsoft themselves recommend not using it for websites.

The advantages of database engines (rather than a flat-file format) are many - they can manage their own caching and transactions, memory, concurrency and locking. Whilst Access probably has some of these features by now, I've only ever seen problems when an Access database becomes busy.

The main concern for me would always be the future-proofing and expandability. You start with Access, you have nowhere to go. If, however, you start with SQL Server Express (which is free) then you can simply upgrade to Web edition, then Standard edition, then mirror it, or cluster it, add replication or log shipping and take your website from a small company site to a massive international operation with essentially zero changes to your code.

Try doing that in Access

As has been mentioned, Access is really intended for small applications with one or two users; it's essentially one up from using Excel. I'm honestly not sure you do speak for small businesses when you say that moving to MySQL would be "not ergonomic or flexible". Any time saved now will well and truly be back to bite you in the ass further down the line

1 reply

D
Community Expert
Dave WattsCommunity Expert
Community Expert
March 20, 2011

Access is not supported on Unix. You will need to port to another database platform like MySQL - which is a good idea anyway - or switch hosts.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Dave Watts, Eidolon LLC
P
plartsAuthor
Inspiring
March 20, 2011

Thanks, good to know.

It is only a simple MDB file, what a shame.

So usefull, so flexible, several file to constitute a bigger DB, so easy to use, so easy to manipulate

so easy to dowload/upload.

It is strange, that most of the queries are fonctionnal on UNIX (with Access)

- update, select, most of common SQL commands,

and Insert does not work ?

Thanks for any extra informations.

Pierre.

A
Adam Cameron.
Inspiring
March 20, 2011

I've never looked at what the DataDirect JDBC drivers that ship with CF do, but for an Access DB to work, as well as the MDB file (which is just the data), you'd need the Jet DB Engine installed, as well as ODBC. Both of which are Windows products.

You are mostly wrong in your assertions that it's just a simple file, that it's useful, flexible, easy to use and manipulate.  If you have exposure to other DB engines you will realise that Access is very much none of those (in comparison, where there is a comparison).

That, and it's a single-user desktop product, and not intended to be used as a back-end for a client-server DB environment.


Access if for creating DBs like "My CD Collection", and not much else.  Or perhaps for slapping a simple GUI onto a proper DB.

It's not fit for purpose in a web environment (and nor did anyone at MS ever suggest it was, so no disrespect to them there).

--

Adam

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices