RE: Running action function from SELECT query
- From: David H <DavidH@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 30 Nov 2008 16:00:51 -0800
That changes a lot. Yes ADO can execute queries saved in an Access database.
(More below especially my final comment)
"Brian" wrote:
I mistyped on thing: the proxy uses ADO.NET, not ASP.NET. Given that I haveBTW - The FE should always be run on the local machines. I actually use a
spent about 200 hours developing the front end in Access over the last couple
of years, it is still very dynamic (i.e. ongoing development), and I do not
have time to pick up another programming platform, it is highly unlikely that
we will be migrating the FE away from Access any time soon.
Part of the issue here is that the Access DB is stored (BE) & run (FE)
locally on the LAN, but the web server is external, so we have to go through
VBScript to pull a copy of the FE down to the local machine and start Access
to avoid any distribution issues.
a firewall. The proxy sits inside the firewall, and the web will communicateADO is just another way of connecting to the database. If you're familar
to it through a forwarded port on the firewall.
I do not know how ADO.NET works, but the developer tells me he is able to
call queries directly from my FE and that he has that part working.
with DAO, ADO was initially designed to replace it. Same basic principle
between the two.
I just
figured that if he could call a query, he may be able to also call a function.Not to my knowledge. ADO is a means of working with the data in a table. It
does not directly expose any of the VBA modules in an Access database unless
you use the function as an expression in the query. (Again, I've done this
before but in conjunction with inserting/deleteing/updating live data, not as
a workaround.)
Yes that should work, the MVP's would probably have more feedback on that as
At any rate, I can call just a function from a query, like this:
SELECT FunctionName([Arg1],[Arg2],[Arg3]) AS FunctionResult
FROM Version;
Without including a table name, I cannot do it, so I included a table that
has just one record but include no results from that table.
it doesn't strike me as a best practice.
The function can
return results to the query. My larger concern, however, is that when IFYI - The ability to imbed an Action query in a SELECT statement is the
previously tried calling an active function (i.e. one that included SQL
INSERT statements) from a SELECT query, I got duplicate inserted records and
vast quantities of missing records, so I am not confident that the function
will not misfire in the same way here.
basis of SQL injection attacks. I would hope that your WEB guy is at least
aware of this. IT SHOULD NOT IMPACT WHAT YOU"RE TRYING HERE, BUT I"D CONSIDER
IT JUST ANY CASE.
http://en.wikipedia.org/wiki/SQL_injection
If the function encapsulates an ACTION query, the action query should run as
in 'SELECT myFunctionName as EXPR1 from myTableName'. How the function
operates is independent of the manner in which its called. If you're select
query returned duplicate records then yes - you have duplicate inserted
records as the function would have been executed for each specific record
returned by the SELECT statement. As to why you ended up with missing
records, i can't comment, BUT if you're function requires parameters which
are then passed to the function, then that's probably where some of the
funkiness came into play - especially if the parameters were derived from
fields returned by the SELECT query. In other words, the SELECT query may not
have SELECTed the records that you were expecting OR the values returned
provided adverse results when passed to the function.
It may be completely different in that
there is only a single record involved here, but I would hate to find out the
hard way - my function is a credit card processing function, and duplication
would mean multiple charges to the same card.
In ALL honesty, I would give the web developer the code that you have and
have him adapt it to run under ASP.NET as it will eliminate a great deal of
the hassel that your experiencing. Not to mention that it'd be more
consistent with sound development practices. In short, you're trying to do
something that Access was never designed to do.
I worked for Walt Disney World for 9 years in resorts and I'm quite familar
with charging credit cards twice courtesy of the ROOM & TAX CALCULATOR
project - it took a couple of weeks before the problem was identified. In
principle, the developer tried exactly what your attempting - trying to reuse
functionality in a manner that it was never designed for. In the end, it was
written from scratch based on existing functionality.
"David H" wrote:.
I can't answer every thing, but I can get you started...
"Brian" wrote:
I have an in-house app for which another developer is writing an ASP.NETNot intrinsically. INSERT, UPDATE, DELETE queries do not return any records.
proxy to pass requests in from the web via XML. His intention is to keep the
proxy very generic, passing in just a query name with arguments, receiving
the results of the query, and passing those results back to the web. This
will work fine for retrieving information via SELECT queries, but it leaves
two questions:
1. When he passes in the name of and arguments for an insert or update
query, is there any way to return any type of results?
They simply act on the underlying table. SELECT is the only statement that
returns records for whatever use. If you're executing the statement via DAO
or ADO, you can use the .RecordsAffected property to return a count of the
records impacted, but you can't actually return the records. If you need to
return the records after an INSERT/UPDATE (or to confirm a DELETE), you could
do so by marking the records with a BATCH number - a number that unique
identifies the records INSERTED or UPDATED. On the INSERT/UPDATE runs, you
would then use a SELECT with the BATCH number in the WHERE statement. The
BATCH number can be any number that could be uniquely created such as the
current date/time converted to number as in 11/29/2008 9:11:09 PM becomes
39781.8827314815. Obviously, you'd need to a record to the underlying table
to capture the batch number.
You could conceivable call it by executing a SELECT query that contains a
2. My app already uses an API to interface with a credit card processor.
Rather than re-inventing the wheel and having the website manage the
customer-to-processor traffic, our intention is to have the customer complete
the information online, then pass that through the proxy to my DB, where I
will pass it along to the processor.
My quandary is this (and I admit, it be more of an ASP.NET question than
Access): is it safe/workable to include a call to the function that runs the
entire CC process in a SELECT query, returning just the CC processing result.
FUNCTION that itself calls the API. I have done something similar but the
query has always performed a specific function along with calling the
FUNCTION. I don't have any experience with wrapping the function in a SELECT
for sole sake of executing the function and thus the API where the SELECT
doesn't actually return any rows. Which leads me to this...
It sounds like you'd be better off having a front end sitting on the web
server and then opening it using automation to call and execute the API. Now
that I think about it you'd pretty much have to use Automation anyways in
order to execute the SELECT query - the web page has to connect either way.
Along with that, to my knowledge you can't use XML to execute a query in
Access unless you setup Access to be checking for the existance of the XML
file, import it, process it and run the query. Access would have to be
running on the server which brings me back to the idea that you should use
Automation. I am a big fan of generic programming and flexibility, however
given the limitations of Access you can't just have Access run a query
without Access running.
The function does this:
a. Creates entries in a couple of tables to record the attempted
transaction, whether successful or not.
b. Uses the processor's API to process the charge.
c. Record the results (approve/decline, approval code, etc.)
The function works perfectly in my front-end app when called by a local
user; I am just trying to find a way to expose it to the web proxy and think
I may be able to call the function from a SELECT query, returning the
function's value (approve/decline information) as the query results.
I have seen strange behavior before when calling action functions such as
this in SELECT queries, such as single records triggering multiple results or
records being missed. If that is not workable, is it possible to call a
function in my MDB directly from ASP.NET?
Not directly per se, but if you have Access on the web server ASP.NET should
be able to access the function using automation.
- Follow-Ups:
- RE: Running action function from SELECT query
- From: Brian
- RE: Running action function from SELECT query
- Prev by Date: RE: Scanning for new tables in a Back End (BE) database
- Next by Date: >> check whether still processing
- Previous by thread: RE: Scanning for new tables in a Back End (BE) database
- Next by thread: RE: Running action function from SELECT query
- Index(es):
Relevant Pages
|
