ODBC Multiple Execute statements

[j_gold]

Hi,

Not sure what I am doing wrong. If there is a better way to do this, please
advise. I am using linked tables (MySQL DB). The first table contains a list
of publications, the second, is the relationship between the publications and
the author.

My sub executes inserts the publications correctly, but fails to get to the
second execute statement. Also, I want the whole transaction to fail if it
doesn't finish the second insert, but when I view the database, the
publication is entered, even though the second transaction failed.

Thanks,

J

Private Sub addPublication(pubTitle As String, publisher As String, urlLINK
As String, _
pubType As String, pubStatus As String, pubAuthor As
String, _
pubPages As String, pubVolume As String, publishDate
As Date, _
pubPresentedAt As String)

Dim wrk As DAO.Workspace: Set wrk = DBEngine(0)
Dim dbP As DAO.Database: Set dbP = wrk(0)
Dim dbPB As DAO.Database: Set dbPB = wrk(0)
Dim rs As DAO.Recordset
Dim sqlQuery As String
Dim newPubID As String

Debug.Assert Not (wrk Is Nothing)

wrk.BeginTrans
On Error GoTo trans_Err

dbP.Execute "INSERT INTO Publications (pubTitle, publisher, urlLINK," _
& "pubType, pubStatus, pubAuthor, pubPages, pubVolume, " _
& "publishDate, pubPresentedAt) " _
& "VALUES ('" & Replace(pubTitle, "'", "''") & "', " _
& publisher & ", " & urlLINK & ", " _
& pubType & ", " & pubStatus & ", " & pubAuthor & ", " _
& pubPages & ", " & pubVolume & ", " _
& Format(publishDate, "\#yyyy\-mm\-dd\#") & ", " & pubPresentedAt &
");", dbFailOnError

sqlQuery = "SELECT pubID FROM Publications where pubTitle = '" + pubTitle +
"'"

Set rs = dbP.OpenRecordset(sqlQuery, dbOpenSnapshot)

If rs.RecordCount = 1 Then
newPubID = rs.Fields("pubID").value
End If

rs.Close

dbPB.Execute "INSERT INTO PublishedBy (personID, pubID) VALUES (" + pKey +
",'" + newPubID + "');"


'Commit the transaction
wrk.CommitTrans dbForceOSFlush

trans_Exit:
'Clean up
wrk.Close
Set dbP = Nothing
Set dbPB = Nothing
Set wrk = Nothing
Exit Sub

trans_Err:
'Roll back the transaction
wrk.Rollback
Resume trans_Exit

End Sub

 

[Banana]

What storage engine are you using? Not all engines are transactional so
even with a explicit transaction, it wouldn't work. You need to use
InnoDb if you want transactional support.

Furthermore, I wouldn't run transaction from Jet's side but rather from
MySQL's side, either by using stored procedure (if you're using MySQL
5.0 or later), or by running a single passthrough query that can compose
multiple statements with the transaction begin/end.

 

[j_gold]

Hi Banana,

I am using MySQL 5.0 and have set up the tables as InnoDB. Programming in
Access is new to me and I haven't had to do transactions with sql before. I'm
still learning my way around.

Would you mind providing me with an example of how I could do this as a pass
through query? I'm game to try storage procedures, but I wouldn't know where
to begin and at this point I just need something to work so I think a pass
through query might be easiest.

Cheers,

J

 

[Banana]

Well, as I said earlier, I'd rather use stored procedure rather than
doing it entirely client-side via a passthrough query, and I think you
have a good excuse to get learning on the stored procedure.

First, you definitely will want to get comfortable with MySQL manual,
which you can look up at MySQL. For stored procedure, look here:

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Your basic stored procedure would probably look like this:

CREATE PROCEDURE myproc (param1 INT, param2 VARCHAR(20))
BEGIN
START TRANSACTION;
INSERT INTO tblA (colA, colB) VALUES (param1, param2);
INSERT INTO tblB (colA) VALUES (param1);
COMMIT;
END;

You would then call the stored procedure with a passthrough query with
this SQL:

CALL myproc (1,'foo')

Note that you can't use parameters in a passthrough query and must
re-write the querydef to change the parameters. You can either use a
temporary querydef (e.g. CreateQueryDef("","CALL myproc(1, 'foo') if I
remember the syntax correctly but it's probably wrong anyhow; look at
the helpfile), or even better use ADO which has support for parameters
and makes it much easier to input the parameters.

Back to the stored procedure, of course, it should be noted that it may
be more complicated than that and you may want to handle error but you
can also deign to let the client handle it keeping in mind that a error
will automatically rollback the transaction.


Regarding your original code, I wonder if the error you had originally
had to do with the fact that you weren't using & operator to concatenate
the 2nd SQL statement?

HTH.

 

[j_gold]

ok - thanks, was hoping to avoid learning stored procedures at this point as
I am running into a deadline and need to get this finished asap. That just
puts another layer into everything and another place for things to go wrong -
like I haven't found enough of those already ;-)

As for the 2nd execute statement - old "bad" habits die hard. I had just
finished rewriting the first execute statement with &s and had forgotten to
do the second - thanks for spotting that. I will see if changing it helps.

Cheers,

J

 

[Banana]

Well, you still can get away with just doing a single passthrough query
or ADO command with the entire SQL between the BEGIN...END I provided
you and not have to learn the stored procedure.

I understand that deadline can be painful but you must not forget that
sometime haste makes waste and actually leave you worse off. It can seem
daunting but when you get down to it, it's really no different from
VBA's requiring a Public Function (<arguments>)...End Function, except
you're doing it in SQL instead of VBA.

Best of luck.