"Ed Reed" <[EMAIL PROTECTED]> wrote on 04/25/2005 02:33:23 AM:
> Does anyone else have any ideas about this topic?
>
> Thanks
>
> >>>Ed Reed <[EMAIL PROTECTED]> 04/23/05 1:29 pm >>>
> Thanks for the reply,
>
> I realize that user variables disappear when the connection closes
> but I don't understand why what I'm trying to accomplish can't be
> done. Doesn't it make since that if you can load a single file with
> multiple SQL commands and have that work succesfully then you should
> be able to have a single call with multple SQL commands work just as
> succesfully? Is there any way to do what I asked in my original post?
>
> Thanks again for the reply.
>
>
> >>>Paul DuBois <[EMAIL PROTECTED]> 4/23/05 12:12:32 PM >>>
> At 22:04 -0700 4/22/05, Ed Reed wrote:
> >Thanks for the reply,
> >
> >So is there anyway to use User Variables with a single connection.
> >All my apps are in VB6 and VBA. They all take a query, open a
> >connection, run query, fill array from query results, close
> >connection and pass back the array. Because of backward
> >compatibility there's no way I can change them to do otherwise.
>
> User variables disappear when the connection closes.
>
>
> >
> >Thanks again.
> >
> >>>>Chris < [EMAIL PROTECTED] > 04/22/05 7:56 PM >>>
> >Ed Reed wrote:
> >
> >>If I run the following in MySQLFront v3.1
> >>
> >>Set @A='Test';
> >>Select @A;
> >>
> >>I get back same result
> >>
> >>+------+
> >>| @A |
> >>+------+
> >>| Test |
> >>+------+
> >>
> >>If I run the same query in MySQL Query Browser v1.1.6 I get this,
> >>
> >>ErrNo 1060, You have an error in your SQL syntax; check the manual
> >>that corresponds to your MySQL server version for the right syntax
> >>to use near ';
> >>select @A' at line 1
> >>
> >>and If I run the same query in my application I get the same error
> >>as the Query Browser.
> >>
> >>Anyone know how I can get my application to give me what I'm looking
for?
> >>
> >>
> >>
> >The command line interface allows you to run multiple commands at once.
> >The Query Browser and PHP interfaces allow only one query per function
> >call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
> >each query separately.
> >
> >This is certainly the case in your application, even if it's not PHP.
> >
> >If you ran the queries separately in the Query Browser, you wouldn't
get
> >the results you expect. It would forget the value of @A because it
> >closes the connection each time. It's possible to keep the connection
> >open by Starting a transaction (even if you're using MyISAM tables).
> >
> >Chris
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>
Dude, you should chill. You originally posted during the weekend, not
everyone keeps up at home. Sometimes it just takes a few days to get all
of the responses to a posting.
In VB (and VBA and VBScript) *you* control when the connection opens and
closes (not like the program "query browser" which you tried to test
with). Using ADO as an example, this snipped of code identifies a one
hour time window starting 5 minutes before the most recent entry in a log
table (it's a made-up query. I don't actually use this.) then uses that
window to get the actual log records.
set oConn = new ADODB.Connection
set oRS = new ADODB.Recordset
oConn.Open "you connection string here"
... processing...
oConn.Execute "select @A := max(datefieldname)- interval 5 minute, @B :=
max(datefieldname)- interval 65 minute from logtable"
.... more processing ...
sSQL = "SELECT * from logtable where datefieldname < @A and datefieldname
>[EMAIL PROTECTED]"
oRS.Open sSQL, oConn
... more processing ...
oRS.close
oConn.close
Until you close the connection, it stays open. That means that all queries
executed _through a particular connection_ have access to any variable
value you have already set with that connection. I set the values of @A
and @B in one query then, lines later, I used them in building a
recordset. Because the recordset is on the same connection the variables
were created with, its query sees them as populated values.
However!! Command.Execute() does not accept chained statements. You cannot
pass in two statements in a row separated by a semicolon. You must split
your SQL and execute it as separate requests. If you didn't split them up,
which statement's results code would Command.Execute() return with? What
if you got an error code as a result? Which query failed? How many
statements executed before failure? How much are you going to need to undo
(assuming you weren't in a transaction) to recover from a failed
statement?
Because the Recordset object supports multiple resultsets, you *MAY* be
able to pass in multiple statements within a single query (but I haven't
tested this!!!). That way, if statements 1,2, 3, and 5 succeed, you might
be able to see that statement 4 failed (by checking the status code of the
4th resultset). As I said, this is not tested. I do everything in single
statements just so that I do not run into the problems you are trying to
work around.
The easiest and safest way is to not chain your statements, just make
multiple calls. Later, when v5.0 gets stable, this may no longer be an
issue (it supports procedural SQL, including stored procedures.) But until
then try to stick with one statement per request.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
