OpenEuphoria

odbc resources

• Posted by Craig Welch <craig at singmail.com> Nov 14, 2005
• 527 views

I'm experimenting with odbc, with SQL Server behind it. I'm running a 
program to read in a 30,000 record file line by line, and insert it into 
  a table. (I know there are better ways of doing that, but it's a 
learning exercise, both for odbc and SQL).

The data are the test records that come with the Tsunami database.

As the record count increases, the PC slows considerably, paging goes 
through the roof, and eventually it's processing 4 transactions per 
second. It starts at about 500 per second.

I put in an abend statement after 10,000 records. Here is a section of 
the dump that makes me wonder:

handle_odbc = {9501740,9501900,-1}
     handle_children = {
                         {2},
                         {3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,

etc ... to the limit that ex.err will display.

Here's the pertinent section of the program (simplified, error routines 
stripped out):

<EUCODE>
counter = 1
void = initODBC()
hconn = openConnectionODBC("blues","sa","" )
void = execDirectODBC (hconn, "USE blues")
		
while 1 do

	fh = open("C:\\EUPHORIA\\odbc\\test_data.txt", "r")
	lNextLine = gets(fh)
	counter = counter +1
	if counter > 10000 then ? 1/0 end if
	
	while sequence(lNextLine) do

		while lNextLine[length(lNextLine)] = '\n' or 				 
lNextLine[length(lNextLine)] = '\r' do
			lNextLine = lNextLine[1..length(lNextLine)-1]
		end while

		sql = "INSERT tsunami VALUES"
		& "("
		& "'" & lNextLine[1..6] & "',"
		& "'" & lNextLine[7..30] & "',"
		& "'" & lNextLine[31..49] & "',"
		& "'" & lNextLine[50..79] & "',"
		& "'" & lNextLine[80..95] & "',"
		& "'" & lNextLine[96..200] & "'"
		&")"

		void = execDirectODBC (hconn, sql)

		lNextLine = gets(fh)
		end while
	
		close(fh)
		?1/0
end while
cleanUpODBC()
</EUCODE>

Any thoughts?

-- 
Craig