one query - two conns 1

[thompom]

Hi,

i normally use one mysql database with one connection,
but i want to query another database [which is read only - and not in my control] for customer records.
i always try and use the minimum amount of recordsets - so, i have a car booking system where the booking data and customer id, for lookup, is stored in a mysql DB and the customer/vehicle details are stored in another database.
can i use JOIN to the other database in one query or do i need to make two queries?
i connect to the mysql db with a dsn less connection, but the customer database uses a file dsn.

if im not being clear let me know

thanks MG

 

[Microbe]

given that the other database is on another server, likely has a different username and password, you are going to need a different connection.

Your app shouldn't care about dsn-less or not, all that it wants is the data.

If there is any relationship you are going to have to grab one of them and then use the result to query the other for the matching record.

This is not a big ask for a server and I wouldn't worry too much about it...unless it is a low-end server with massive amounts of hits.

Steve Davis

http://www.HaHaHa.com.au/weblog

 

[thompom]

thanks for your reply - since i will be making most of my apps to run this way in future i thought id better see if there was a neater way!

 

[onpnt]

What database server is db #2 on? You should never open two connection concurrently from your applictions (IMHO). You should either create linked servers on the databasse servers so you can use one database engine to do the work or reuse the same connection with different providers to reopen on the second db. If you do not need the heavy rs object then set the rs to an array for memory storage while you go after the second.


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[thompom]

not sure what the db is! - its supplied by a company called kerridge, is based on Wang2000 and runs on unix - i just got an odbc driver[@500quid a licence!] which only allows reading of data.
at the moment details are input on both systems - but with this i can save the users time with a postcode/reg search.

 

[thompom]

hi - this is an example of the code im using, could this cause me problems?
[probably about 500 pageloads a day]

sql1

SELECT mot.mott, book.carid, book.bookid, book.bookdate, SUM(stdwork.stdtime) AS stdtime 
FROM mottimes AS mot 
LEFT JOIN book ON mot.motid = book.slotid AND book.bookdate = 20070817 
ORDER BY mot.mott

sql2

'no inner joins - odbc driver doesnt support them!
SELECT FirstName, Surname, postcode, registrationnumber FROM VehicleRecords, vehiclelinkindex, customerrecords WHERE vehicleRecords.vehicleNumber = vehiclelinkindex.vehiclemagicno AND CustomerRecords.CustomerNumber = vehiclelinkindex.ctmagicnumber AND VehicleRecords.vehiclenumber = 82366

asp

Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open Conn_Str
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open sql1, conn1

Do While (Not rs1.Eof)
x_mottime = rs1("mott")
x_bookdate = rs1("bookdate")
x_motid = rs1("motid")
x_bookid = rs1("bookid")

'db2 lookup
Set conn2 = Server.CreateObject("ADODB.Connection")
conn2.Open Conn_Str2
Set rskcus = Server.CreateObject("ADODB.Recordset")
rs2.Open sql2, conn2
x_fname = rs2("FirstName")
x_lname = rs2("Surname")
x_pcode = rs2("Postcode")
x_reg = rskcus("registrationnumber")
rs2.close
conn2.close
response.write x_mottime 
if not isnull(x_reg) then
response.write x_reg & x_fname & " " & x_lname
else
response.write "Free"
end if

rs1.MoveNext
Loop
rs1.close
conn.close

 

[Tarwn]

Yikes...first problem, even if you do have to manage two different connections and recordsets, try to not open your second connection inside a loop, basically you will end up opening it once for every single record in the first recordset.

Next issue I see is that either you deleted the line that defines sql2 when you posted to TT or your actually executing the same SQL statement every time you loop. If you deleted the sql statement to shorten your post, put it back in so we can see what values your using form the first recordset. If you are executing the same query on each loop then you either have a logical error or need to move that query outside the loop. I think your vehicle number is probably a variable from the first query, but would prefer not to make any assumptions.

Next, (after you provide more info so we can look at the issues in 2), you may want to look into using the .Execute() method of the connection object to create your recordset and execute a query, and the .GetRows() method of the recordset object that dumps recordset data to an array. GetRows() is generally a 7-11x speed increase.

Best MS KB Ever:

http://support.microsoft.com/kb/555375

 

[thompom]

thanks for your reply- have made some changes

but am getting an error

Subscript out of range: 'UBound'
'on this line
for j=0 to UBound(aBookings, 0)

my code now looks like

Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open Conn_Str
response.ContentType = "text/plain" 
Set rs1 = Conn.Execute(sql1)
aBookings = rs1.GetRows()
rs1.close
Set rs1 = Nothing
conn.close

j=0
for j=0 to UBound(aBookings, 0)

if aBookings(11,j) = 1 and aBookings(14,j) =1 then
x_oldcar = aBookings(6,j)
x_carid = aBookings(1,j)

if x_carid > 0 or not isnull(x_carid) then

Set conn2 = Server.CreateObject("ADODB.Connection")
conn2.Open Conn_Str2

sql2 = "SELECT FirstName, Surname, Address001, Postcode, registrationnumber "_
& "FROM MK_01_VehicleRecords, MK_01_vehiclelinkindex, MK_01_customerrecords "_
& "WHERE MK_01_vehicleRecords.vehicleNumber = MK_01_vehiclelinkindex.vehiclemagicno "_
& "AND MK_01_CustomerRecords.CustomerNumber = MK_01_vehiclelinkindex.ctmagicnumber "_
& "AND vehiclenumber = " & x_carid

Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.Open sql2, conn2

x_fname = rs2("FirstName")
x_lname = rs2("Surname")
x_lname = Left(x_lname, 10)
x_pcode = rs2("Postcode")
x_reg = rs2("registrationnumber")
rs2.close
conn2.close
response.write x_mottime 
response.write x_reg & x_fname & " " & x_lname
else
response.write "Free"
end if
j = j + 1 
Next

 

[thompom]

...x_carid is variable from first query

 

[Microbe]

re the error: Where is the SQL statement "sql1"?

Set rs1 = Conn.Execute(sql1)

Steve

http://www.ColdCallComedy.com

 

[thompom]

hi - thanks for your reply, sql1 below

sql1

sql1 = "SELECT mot.mott, book.carid, book.bookid, book.bookdate, book.waittick, book.oldcar, mot.bayid, mot.motid, mot.dayid, mot.baytypeid, SUM(stdwork.stdtime) AS stdtime "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "AND book.bookdate = "&[datesql] _
& "LEFT JOIN stdworklink ON stdworklink.bookid = book.bookid "_
& "LEFT JOIN stdwork ON stdwork.stdworkid = stdworklink.stdworkid "_
& "GROUP BY book.bookid, mot.motid "_
& "ORDER BY mot.motid, mot.mott, mot.bayid, mot.baytypeid, stdwork.stdtime"

 

[Microbe]

well, there's no way we can really tell what the problem is. My guess is the SQL breaks and if you check how many rows are returned it will be 0.

Steve

http://www.ColdCallComedy.com

 

[Tarwn]

UBound takes 1-based arguments and GetRows produces a 2-d array in (col,row) format. This means your UBound call should be for UBound(arr,2) for the number of rows returned.

You are still opening your second connection inside a loop. If you move this connection outside the loop and use Connection.Execute inside the loop then you will only be opening the database connections twice, instead of 1 + (number of results from first query).

By manually incrementing your j counter your going to be skipping through the array by 2 (by default For/Next statements increment the counter by 1 for you, unless you specify otherwise). Get rid of the j = j + 1 and let the For loop increment through.

-T



Best MS KB Ever:

http://support.microsoft.com/kb/555375

 

[thompom]

Tarwn - heres a *

have changed my code to this - and works great, i think ive addressed all issues?

Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open Conn_Str 
Set conn2 = Server.CreateObject("ADODB.Connection")
conn2.Open Conn_Str2

Set rs1 = Conn.Execute(sql1)
aBookings = rs1.GetRows()
rs1.close
Set rs1 = Nothing
conn.close

for j=0 to UBound(aBookings, 2)

x_oldcar = aBookings(6,j)
x_carid = aBookings(1,j)
x_mottime = aBookings(0,j)       

response.write x_mottime 

if x_carid > 0 or not isnull(x_carid) then

sql2 = "SELECT FirstName, Surname, Address001, Postcode, registrationnumber "_
& "FROM MK_01_VehicleRecords, MK_01_vehiclelinkindex, MK_01_customerrecords "_
& "WHERE MK_01_vehicleRecords.vehicleNumber = MK_01_vehiclelinkindex.vehiclemagicno "_
& "AND MK_01_CustomerRecords.CustomerNumber = MK_01_vehiclelinkindex.ctmagicnumber "_
& "AND vehiclenumber = " & x_carid

Set rs2 = conn2.Execute(sql2)

x_fname = rs2("FirstName")
x_lname = rs2("Surname")
x_lname = Left(x_lname, 10)
x_pcode = rs2("Postcode")
x_reg = rs2("registrationnumber")
rs2.close
conn2.close

response.write x_reg & x_fname & " " & x_lname
else
response.write "Free"
end if

Next

 

[Tarwn]

I think you got everything. I can't help but think I am missing something that would let you execute fewer queries, but considering your databases are on separate systems and my MySQL is rusty, I'm not sure I can think of a better solution.If this still isn't fast enough then the only thing I can think of that might improve the speed is setting up some sort of replication job on the MySQL server to pull the data from your other server on a regular basis. This would allow you to change the above code down to a single query.

-T

Best MS KB Ever:

http://support.microsoft.com/kb/555375