MySQL hit to batabase with ASP

crmpicco · November 15th, 2005, 05:22 AM

I have set up this connection in my ASP, is that the safest and tightest way to use and close a MySQL hit?

Code:

set rs=con.execute("select * from net_company where companyid = '" & trim(session("agencyid")) & "' and agencyid <> '" & trim(session("agencyid")) & "'")
if (not rs.eof) AND (not rs.bof) then
do until rs.eof
%>
<tr bordercolor="#CCCCCC" bgcolor="#ECECEC">
    <td><%=ucase(trim(rs("company_name")))%></td>
    <td><%=ucase(trim(rs("towncity")))%></td>
    <td><%=trim(rs("phone"))%></td>
    <td><%=trim(rs("email"))%></td>
</tr>
<%
rs.movenext
loop
rs.close
Set rs = Nothing

www.crmpicco.co.uk

crmpicco · November 15th, 2005, 05:25 AM

i am trying to keep it as tight as possible to allow it to run on all environments

www.crmpicco.co.uk

ChrisScott · November 15th, 2005, 05:38 AM

Hi Picco,

Not exactly sure what you're after, can you explain what you mean by "all environments"?

There is no need to check both .EOF and .BOF properties when you open a recordset, checking either one is sufficient.

I would state adCmdText when using Execute() if you know you are opening a query rather than a stored procedure or table. Tis way the machine does not have to check what you are passing it before running it against the db.

Code:

.Execute(query, , adCmdText)

HTH,

Chris

crmpicco · November 15th, 2005, 06:13 AM

does that run on all environments, what i mean by that is - does it run on Win2003 server, WinXP IIS etc....

www.crmpicco.co.uk

crmpicco · November 15th, 2005, 06:17 AM

so, does adCmdText speed up the processing of the DB hit?

www.crmpicco.co.uk

ChrisScott · November 15th, 2005, 06:30 AM

The different environments will be processing your ASP via IIS (or even PWS), so you should get the same results.

Have you be getting different results in different places?

You may get different results with different versions of MyODBC, I have noticed this in particular with empty longtext fields.

Using adCmdText should give a marginal gain in processing, no doubt very small, but every little helps.

crmpicco · November 15th, 2005, 07:20 AM

thanks Chris, are there any other pieces of code like that you would recommend?

I have been getting different results with recordsets on Win2003 Server and WinXP

www.crmpicco.co.uk

ChrisScott · November 15th, 2005, 07:46 AM

I do nearly all development on server machines, so don't have in depth XP experience as a development platform.

Differences in MySQL, MyODBC and MDAC versions between machines could all be to blame.

I have had problems with the ADO/MyODBC driver combination and now nearly always use client side cursors when using MySQL, as this seems to reduce the number of issues drastically. It may be worth a try in your situation.

What are the differences you are finding?

crmpicco · November 15th, 2005, 12:02 PM

the differences i seemed to be experiancing are that on Windows 2003 server I needed to keep everything much tighter, by adding in bof/eof checks.
however, locally on XP Pro these bof/eof checks are not needed???

along the same lines of the adCmdText are there any other checks or pieces of code that I should think of adding in?

Code:

set rsspecialinfo = con.execute("SELECT exp_mouse_over FROM `net_leg2` WHERE contract_id = '"& contractId &"' AND fare_id = '" & fareId & "'", adCmdText)

www.crmpicco.co.uk

crmpicco · November 15th, 2005, 12:03 PM

my main aim is to make the code cross-platform as well as making it fast as possible to process

www.crmpicco.co.uk