[th0410]

Having problems executing asp script up against Informix DB

This is the Connection.....
<%
Dim connMPS
'strConn = "DSN=mps_server; Database=mpx; uid=mpx; pwd=mpx"
'strConn = "Driver={Informix-CLI 2.5 (32 Bit)}; Server=172.16.40.200; Database=mpx; Uid=mpx; pwd=mpx"
strConn = "Dsn=mps_serv; Host=172.16.40.200;Server=mpx;Service=turbo; Protocol=onsoctcp; Database=mpx; Uid=mpx;Pwd=mpx"


Set connMPS = Server.CreateObject("ADODB.Connection")
connMPS.Open strConn
%>

This is the Execution:

<%
'Set cmdSupplier = server.CreateObject("ADODB.Command")
'cmdSupplier.ActiveConnection = strConn
'cmdSupplier.CommandText = "select * from Suppliers"
'cmdSupplier.Execute
SupplierSQL = "select * from Suppliers"

Set rsSupplier = Server.CreateObject ("ADODB.Recordset")
rsSupplier.Open SupplierSQL, strConn, 3, 3

response.write("Record count: " & rsSupplier.recordcount & " <br><br>")
'rsSupplier.movefirst
'while not rsSupplier.eof
'response.write(rsSupplier("supplier") & " - " & rsSupplier("Name") & " ****")
response.write(rsSupplier.fields("supplier").value )
response.write("<br>")
'rsSupplier.movenext
'wend


'rsSupplier.close
%>

It prints out the text from the DB in not looping through the
Recordset. when looping through the recordset the ASP script times out......
The DSN i use is based on a Informix-CLI 2.5 (32 Bit) ODBC driver...have tried it with other drivers than this, but the same result.

The IIS is running on a windows 2000 proffessional workstation.

Is there anyonone who has experienced this problem. Can sombody help...

Thanks......

[paul lush]

Increase the timeout period or true response.buffer = false to see if it shows them all/some

[paul lush]

Try doing the query without locking as well since youre doing it read only.

[th0410]

Originally posted by paul lush
Try doing the query without locking as well since youre doing it read only.

How exactly do I do this?

[paul lush]

rsSupplier.Open SupplierSQL, strConn, 3, 3

becomes

rsSupplier.Open SupplierSQL, strConn, 3, 1

or even

rsSupplier.Open SupplierSQL, strConn, 0, 1

if you want nothing more than a complete read with only .movenext

you could even try

rsSupplier.Open SupplierSQL, strConn

[th0410]

Originally posted by paul lush
Try doing the query without locking as well since youre doing it read only.

I tried to switch the buffer off, but then the loop does not terminate when reaching the last record?

[paul lush]

Try openning it with ReadOnly and forward (,0 ,1) and then

response.write "record count=" & rsSupplier.RecordCount

for intLoop = 1 to rsSupplier.RecordCount
response.write "record number= " & intLoop & " of " & rsSupplier.RecordCount

'' your stuff here

rsSupplier.movenext

next


See if that works.

[paul lush]

Note - personal opinion -

Informix SUCKS ASS!!!!

From my dealings with mssql and postgres over the years, Ive found that informix is AWFUL, all from three days of trying to convert 4gl to something useful.

[th0410]

Originally posted by paul lush
Try openning it with ReadOnly and forward (,0 ,1) and then

response.write "record count=" & rsSupplier.RecordCount

for intLoop = 1 to rsSupplier.RecordCount
response.write "record number= " & intLoop & " of " & rsSupplier.RecordCount

'' your stuff here

rsSupplier.movenext

next


See if that works.

The problem is that rsSupplier.RecordCount returns -1 but I think

this
MaxSupplierSQL = "select max(supplier) as MaxSuppl from suppliers"
Set rsMaxSupplier = Server.CreateObject ("ADODB.Recordset")
rsMaxSupplier.Open MaxSupplierSQL, strConn , 3, 3
MaxSuppl = rsMaxSupplier("MaxSuppl")
rsMaxSupplier.close

solves the problem. this way I get the recordCount....

I quit Agree on Informix Sucks....I usally work with MS SQL, MS ACCESS, and MY SQL....they give no problems...

I have done the same on a windows 2000 server with IIS 5, and it worked without any problems....this is on a W2K professional....is there differences in these configuration?

[paul lush]

Your web server platform shouldnt matter, its the ODBC driver that gives people the most trouble.

[th0410]

Originally posted by paul lush
Your web server platform shouldnt matter, its the ODBC driver that gives people the most trouble.

I thank you fce me to or your help.....is there a better drivere you advice me to use?

[paul lush]

Other than another database, not really. mySQL still has problems understanding SQL99. By far my favorite is mssql dare I say it with postgres in a close 2nd. You'll just have to code around ODBC problems

[th0410]

Originally posted by paul lush
Other than another database, not really. mySQL still has problems understanding SQL99. By far my favorite is mssql dare I say it with postgres in a close 2nd. You'll just have to code around ODBC problems

Why do i get a type mismatch when
useing the count

MaxSupplierSQL = "select Count(supplier) as MaxSuppl from suppliers"
Set rsMaxSupplier = Server.CreateObject ("ADODB.Recordset")
rsMaxSupplier.Open MaxSupplierSQL, strConn , 0, 1
MaxSuppl = rsMaxSupplier("MaxSuppl")
rsMaxSupplier.close

when writing there is no problem
response.write "record count=" & MaxSuppl & "<br>"

but when using the for loop i recieve a type mismatch error ???

for intSuppl = 1 to MaxSuppl
response.write(rsSupplier("supplier") & " - " &_ rsSupplier("Name") & " " & MaxSuppl )
'response.write(rsSupplier.fields("supplier").valu e)
response.write("<br>")
rsSupplier.movenext
next

[paul lush]

Firstly, thats two sql hits, it will slow your page down

response.write(rsSupplier("supplier") & " - " &_ rsSupplier("Name") & " " & MaxSuppl )

should really read

response.write rsSupplier("supplier") & " - " & rsSupplier("Name") & " " & MaxSuppl

even try [name]

make sure neither are ntext defined as well. past that Im lost without a copy of the table and a working POS informix server.

[th0410]

the error is related to

this line
for intSuppl = 1 to MaxSuppl

and it is MaxSuppl

gained from this code:
MaxSupplierSQL = "select Count(*) as MaxSuppl from suppliers"
Set rsMaxSupplier = Server.CreateObject ("ADODB.Recordset")
rsMaxSupplier.Open MaxSupplierSQL, strConn , 0, 1
MaxSuppl = rsMaxSupplier.fields("MaxSuppl").value
rsMaxSupplier.close

it displays perfectly the record count
when :
response.write(MaxSuppl)
but when useing it in the loop it displays an error "type mismatch"

what could cause this?