Executing back to back SQL statement

Databases
1

I need to execute two SQL statements one after the other. I’m using MySQL and prepared statement. First I need to select some data from a table and then I need to combine that with some other data and insert that into another table. Somehow doing this causes the debugger to “quit unexpectedly”. If I do one or the other it works fine so I assume there’s something wrong with how I have this structured. I should also add that I’m looping through an array of data and this get performed about 100 times.

What I do is open a connection to the db then I execute the first query.

[code] Dim ps1 As MySQLPreparedStatement
ps1=db.Prepare(“SELECT SLLF_ID, WorkStateCode FROM Cvent_Contacts WHERE Cvent_ID = ?;”)
ps1.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps1.Bind(0, vCvent_ID) 'Cvent_ID

    rs=ps1.SQLSelect[/code]

Then the second one:

[code] Dim ps2 As MySQLPreparedStatement
ps2 = db.Prepare(“INSERT INTO Cvent_Events (event_id, event_code, event_title, event_startdate, event_city, event_state, event_statecode, cvent_contact_id, source_id, sllf_contact_id) VALUES (?,?,?,?,?,?,?,?,?,?);”)

    ps2.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
    ps2.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
    ps2.BindType(2, MySQLPreparedStatement.MYSQL_TYPE_STRING)
    ps2.BindType(3, MySQLPreparedStatement.MYSQL_TYPE_DATETIME)
    ps2.BindType(4, MySQLPreparedStatement.MYSQL_TYPE_STRING)
    ps2.BindType(5, MySQLPreparedStatement.MYSQL_TYPE_STRING)
    ps2.BindType(6, MySQLPreparedStatement.MYSQL_TYPE_STRING)
    ps2.BindType(7, MySQLPreparedStatement.MYSQL_TYPE_STRING)
    ps2.BindType(8, MySQLPreparedStatement.MYSQL_TYPE_DOUBLE)
    ps2.BindType(9, MySQLPreparedStatement.MYSQL_TYPE_DOUBLE)
    
    ps2.Bind(0, aEventDetails(0)) 'event_id
    ps2.Bind(1, aEventDetails(1)) 'event_code
    ps2.Bind(2, aEventDetails(2)) 'event_title
    ps2.Bind(3, vNewMySQLDateTime) 'event_startdate
    ps2.Bind(4, aEventDetails(4)) 'event_city
    ps2.Bind(5, aEventDetails(5)) 'event_state
    ps2.Bind(6, aEventDetails(6)) 'event_statecode
    ps2.Bind(7, aEventContactData(0)) 'cvent_contact_id
    ps2.Bind(8, aEventContactData(1)) 'source_id
    ps2.Bind(9, vSLLF_ID) 'sllf_contact_id
    
    ps2.SQLExecute[/code]

In trying to solve this I’ve been putting in a db.commit and db.close in a couple of places and that hasn’t helped.

Any suggestions as to how to structure this?

Thanks.

2

Can you not just do it all on the database?

3

Well I don’t know how that could be possible. First some of the data I need already exists in a table so I get that and then I add that data with some additional data to create a new record and insert that into another table. No way around that that I know of. You got some ideas I don’t know about?

4

Are you reusing the ps2 prepared statement or creating it for every insert?

5

It’s getting created for every insert.

6

So is the ps1 which is the first prepared statement. A select statement.

7

you can select and insert at the same time:

insert into table_3(val1, val2, val3, val4) select a.val1, a.val2, b.val3, b.val4 from table_1 a left join table_2 b on a.id = b.id where a.val5 = 'some_criteria'

8

Duane try without recreating the ps2 statement - just bind for each insert. If that doesn’t work you could build a big ps for inserting multiple records with one execute.

9

The idea is to as much of the data manipulation on the database server and avoid dragging it all down to the desktop, enhancing it and then pushing it all back up again. You should get the best performance that way.

So I would start by importing any data you want to add into a staging table in the database. Then use SELECT, JOIN. UNION and so on to pull the data together and finally use an INSERT/SELECT as Scott has pointed out to create the new record.

10

This lead me to the solution. I’ve been on this new style of Dim’ing variables close to where they are used as described in the Sept/Oct Xojo Dev issue. In this case I also included the connect code for the db so that was reconnecting every time through the loop. So I moved this outside the loop and it seems to be working fine. Thanks everybody for chipping in.

[code] Dim db As MySQLCommunityServer
db = New MySQLCommunityServer

db.Host=“xx.xxx.xxx.1xxx10”
db.Port = 3306
db.DatabaseName = “dbname”
db.UserName = “user”
db.Password = “password”[/code]

11

Still, doing this kind of thing in a loop is not the right thing to do. Using this example syntax you can insert 3 records with just one command:

INSERT INTO Cvent_Events (event_id, event_code, event_title, event_startdate, event_city, event_state, event_statecode, cvent_contact_id, source_id, sllf_contact_id) VALUES (?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?);

Now bind your data to the statement and get rid of the loop.

12

But the loop still should have worked. My style would be to create a single ps2 in that block & bind data for each insert reusing the prepared statement. I can see the benefits of building a single BIG insert statement though - it’s easier not using prepared statements in that construct though.

Either way Duane has fixed his problem.

13

Interesting solution with the multiple “VALUES” sets per insert, never seen that before, but I don’t think it will work for this situation. The number of data sets I’ll need will be variable and could range from 1 to 100+. In order to build that data set I need to pull data from 2 sources making 5 calls just to get the data I need.

I’m building a table of data on a local MySQL database of people who have attended certain events. My source is a remote database and I make XML calls to get the data. First I have to get a list of events within a data range. That gives me an array of event id’s. So that’s the outer loop. I loop through each event id and I get an array of registration id’s. Then I loop through the registrations id’s and get some detail information on the event and the registrant. However, there’s one piece of data that I don’t get from this source and that is obtained making a SELECT call to the local database using data obtained in this inner loop process. Then I have all the data I need and build the INSERT statement. So within this loop I need to make two SQL calls. A SELECT to get data needed and then the INSERT.

I don’t know why moving the connection steps outside of both loops worked but it did.

I always thought that using prepared statements was the way to go. A bit more forgiving of things like punctuation.