2d arrays and getting data on multiple lines? (using DBgetrecordset UDF) - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » 2d arrays and getting data on multiple lines? (using DBgetrecordset UDF)

Page 1 of 1

Topic Options

#152163 - 2005-11-24 12:37 AM 2d arrays and getting data on multiple lines? (using DBgetrecordset UDF)
Tomarse Tomarse Fresh Scripter Registered: 2005-04-26 Posts: 9	I'm having some trouble using 2d arrays as created from SQL database recordsets using the DBGetRecordset UDF. The actual database access isnt causing me a problem but pulling the data out of the array afterwards is. What am i doing wrong? This UDF is supposed to return me a recordset that is a proper 2D array, but i instead seem to get a 1D array with each item consisting of multiple lines of data. The code i'm attempting is as follows Code: $SQLQueryRS = "SELECT id,mac,name,locationid FROM computers where locationid is NULL" $recordset = DBGetRecordset($objConn, $SQLQueryRS) $retvalue = DBconnclose($objconn) For $row=0 to Ubound($recordset,1) ? $recordset[$row,2] Next This gives me the error: Code: ERROR : expected ']'! Script: M:\bin\sql\setup2.kix Line : 157 Line 157 is ? $recordset[$row,2] - which should be displaying the second column in the current $row. If i change my code to the following: Code: $SQLQueryRS = "SELECT id,mac,name,locationid FROM computers where locationid is NULL" $recordset = DBGetRecordset($objConn, $SQLQueryRS) $retvalue = DBconnclose($objconn) For $row=0 to Ubound($recordset,1) ? $recordset[$row] Next I get the following output Code: 1 0000E886E355 TOM-SSSSS which has been correctly pulled out of my database and contains a complete row of data but on multiple lines. How do i access the items seperately? (for reference the row in my database contains "1,0000E886E355,TOM-SSSSS,NULL" which is 1 row of data with 4 fields.) Can anybody tell me what i am doing wrong here? I get similar behavior and end up with multi-lined strings when i try and pull the complete list of section's out of an ini file using ReadProfileString like this Code: ReadProfileString ($machinesini,"","") Thanks
Top

#152164 - 2005-11-24 03:47 PM Re: 2d arrays and getting data on multiple lines? (using DBgetrecordset UDF
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	Your code will never work because you're not using the function correctly. You need to specify the connection object and utilize DBConnOpen(). See the UDF header example for detailed usage. Or preferably, use DBCommand(), as it is easier to use. _________________________ There are two types of vessels, submarines and targets.
Top

#152165 - 2005-11-24 07:33 PM Re: 2d arrays and getting data on multiple lines? (using DBgetrecordset UDF
Tomarse Tomarse Fresh Scripter Registered: 2005-04-26 Posts: 9	Sorry, I have done that but i didnt include it in my code snippet. I've got Code: $objConn = DBConnOpen($dsn) before the section above. I know your DB stuff is working for me as i can display the output. Its the format that the recordset comes back in that is causing me trouble.
Top

#152166 - 2005-11-24 08:04 PM Re: 2d arrays and getting data on multiple lines? (using DBgetrecordset UDF
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	Then why are you not specifying both the row and column of the data element? See the UDF Header example and implement your code exactly as listed in the example! _________________________ There are two types of vessels, submarines and targets.
Top

#152167 - 2005-11-24 08:38 PM

Re: 2d arrays and getting data on multiple lines? (using DBgetrecordset UDF

Tomarse Offline

Fresh Scripter

Registered: 2005-04-26
Posts: 9

Thanks!
I've got it working with DBcommand() now after looking at your example code. I'll use that instead.

This works perfectly for me:

Code:


$dsn='DRIVER=SQL Server;SERVER=192.168.1.4;UID=user;PWD=pass;DATABASE=db'
$sql='SELECT id,mac,name FROM computers where locationid is NULL'
$recordset = DBCommand($dsn,$sql)
? 'Error = '+@ERROR+' - '+@SERROR

For $row=0 to Ubound($recordset,1)
   For $column=0 to Ubound($recordset,2)
   ? 'Field(row='+$row+', column='+$column+') ='+$recordset[$row,$column]
   Next
Next

What is the actual difference between DBcommand and DBgetrecordset?

I've been playing with DBgetrecordset too (for no particular reason) and i cant get that example code to work:

Code:


	$objConn = DBConnOpen('DRIVER=SQL Server;SERVER=192.168.1.4;UID=user;PWD=pass;DATABASE=db')
	$recordset = DBGetRecordset($objConn,"SELECT id,mac,name FROM computers where locationid is NULL")
	$retcode = DBConnClose($objConn)
	? "ubound($recordset,1)="Ubound($recordset,1) ;this is 2
	? "ubound($recordset,2)="Ubound($recordset,2) ;this is -1 ?
	For $row=0 to Ubound($recordset,1)
		?"whole row is" $recordset[$row]
   	For $column=0 to Ubound($recordset,2)
      	? 'Field ='+$recordset[$row,$column]
      Next
   Next

I always get -1 for the second ubound. There are similar threads already about but they dont explain why.

Edited by Tomarse (2005-11-24 08:40 PM)

Top

#152168 - 2005-11-24 11:33 PM Re: 2d arrays and getting data on multiple lines? (using DBgetrecordset UDF
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	DBCommand() is pretty much for one-time-use. DBGetRecordset() allows you top open the connection via DBConnOpen, then execute multiple SQL statements before you close the connection via DBConnClose, more efficient way to handle the database connection. _________________________ There are two types of vessels, submarines and targets.
Top

#152169 - 2005-11-25 07:53 PM Re: 2d arrays and getting data on multiple lines? (using DBgetrecordset UDF
Tomarse Tomarse Fresh Scripter Registered: 2005-04-26 Posts: 9	Thanks. That makes sense
Top

Page 1 of 1

Previous Topic View All Topics

Index Next Topic

Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org