Newb question on arrays

[stevengraff]

If I fire off a SQL query, what's the best/easiest way to put it into an array?

Here's the query:

select contact, address1, zip from contact1 where accountno = '12345'

Expecting back anywhere from 1 to 100 records, formatted as follows:

Joe Jones FD 215 Maple FD 48104 FDRD
Mike Smith FD 125 Main FD 30243 FDRD

FD = field delimiter character
RD = row delimiter character
extra spaces added for readability.

[Deana]

How are you querying the information exactly?

Here is a code sample that I recently wrote using WinBatch and dotNet to read the result of a database query into a WIL array:
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/dotNet/System_Data+Get~Database~Results~into~a~WIL~Array~and~Display.txt

That should help get you started.

[stevengraff]

How are you querying the information exactly?

It's a mssql database, but I'm querying it through the GoldMine api, which lets me set the field delimiter and record delimiter. The data is returned as ordinary text, in a single string, with the delimiters embedded. Up 'til now I've dealt with the data using lists, primarily, but I suspect there may be some advantages to using arrays instead.

Here's a sample:

Curt Hicks800 Stephenson Hwy. Suite 15048237Al ChinnAl DurhamBrent BassettCurtis R. Hicks25900 Greenfield Road, Suite 50148237Dave ChildsJackie HeinnemanLaura Malmstrom

[JTaylor]

If it will be CSV compatible you could do a FilePut() and then use ArrayFileGetCSV().

If you have the option to do a direct query via ADO you could do something like the following and it would be in an array:

  SQLText = "Select field1, field2, field3 from table"
  myREC.Open (SQLText, myConn, adOpenStatic, adLockReadOnly, adCmdText)
  If myREC.EOF == @FALSE Then
    fld_array = myREC.GetRows()
  EndIf
  myREC.Close


Jim

[stevengraff]

If it will be CSV compatible you could do a FilePut() and then use ArrayFileGetCSV().

I could put it into CSV then save it to a file.

If you have the option to do a direct query via ADO you could do something like the following and it would be in an array:

  SQLText = "Select field1, field2, field3 from table"
  myREC.Open (SQLText, myConn, adOpenStatic, adLockReadOnly, adCmdText)
  If myREC.EOF == @FALSE Then
    fld_array = myREC.GetRows()
  EndIf
  myREC.Close


Jim

Don't/can't.

[stevengraff]

This seems to work... but I'm not sure about that new pain twixt my ears:

Code (winbatch) Select Expand


numNames = itemCount(nameList,rd)
arrayNL = Arrayize(nameList, rd)
for i = 0 to numNames - 1
record = arrayNL[i]
fields%i% = arrayize(record, fd)
next


[td]

It's a mssql database, but I'm querying it through the GoldMine api, which lets me set the field delimiter and record delimiter. The data is returned as ordinary text, in a single string, with the delimiters embedded. Up 'til now I've dealt with the data using lists, primarily, but I suspect there may be some advantages to using arrays instead.

Here's a sample:

Curt Hicks800 Stephenson Hwy. Suite 15048237Al ChinnAl DurhamBrent BassettCurtis R. Hicks25900 Greenfield Road, Suite 50148237Dave ChildsJackie HeinnemanLaura Malmstrom

Makes an interesting case for adding an additional record delimiter parameter to the Arrayize function so that it could create multi-dimensional arrays (tables).  Don't know how many users would find it useful though.

[Deana]

Here is a trick previously mentioned by jTaylor:

Code (winbatch) Select Expand

namelist = 'Curt Hicks,800 Stephenson Hwy. Suite 150,48237,':@LF:'Al Chinn,,,':@LF:'Al Durham,,,':@LF:'Brent Bassett,,,':@LF:'Curtis R. Hicks,25900 Greenfield Road, Suite 501,48237,':@LF:'Dave Childs,,,':@LF:'Jackie Heinneman,,,':@LF:'Laura Malmstrom,,,'
tempfile = FileCreateTemp( 'TMP' )
FilePut( tempfile, namelist )
myArray = ArrayFileGetCsv( tempfile, 0)
FileDelete( tempfile )
Pause('notice', 'data is now in an array')
Exit

[stevengraff]

Here is a trick previously mentioned by jTaylor:

Code (winbatch) Select Expand

namelist = 'Curt Hicks,800 Stephenson Hwy. Suite 150,48237,':@LF:'Al Chinn,,,':@LF:'Al Durham,,,':@LF:'Brent Bassett,,,':@LF:'Curtis R. Hicks,25900 Greenfield Road, Suite 501,48237,':@LF:'Dave Childs,,,':@LF:'Jackie Heinneman,,,':@LF:'Laura Malmstrom,,,'
tempfile = FileCreateTemp( 'TMP' )
FilePut( tempfile, namelist )
myArray = ArrayFileGetCsv( tempfile, 0)
FileDelete( tempfile )
Pause('notice', 'data is now in an array')
Exit

What happens if the data is not "well-behaved," i.e. name = Jack O'Brien? Or Joe "The Shoe" Wilson?

[Deana]

Here is a trick previously mentioned by jTaylor:

Code (winbatch) Select Expand

namelist = 'Curt Hicks,800 Stephenson Hwy. Suite 150,48237,':@LF:'Al Chinn,,,':@LF:'Al Durham,,,':@LF:'Brent Bassett,,,':@LF:'Curtis R. Hicks,25900 Greenfield Road, Suite 501,48237,':@LF:'Dave Childs,,,':@LF:'Jackie Heinneman,,,':@LF:'Laura Malmstrom,,,'
tempfile = FileCreateTemp( 'TMP' )
FilePut( tempfile, namelist )
myArray = ArrayFileGetCsv( tempfile, 0)
FileDelete( tempfile )
Pause('notice', 'data is now in an array')
Exit

What happens if the data is not "well-behaved," i.e. name = Jack O'Brien? Or Joe "The Shoe" Wilson?

On using ArrayFileGetCSV an "Error 1808: File contains invalid CSV line(s)" can happen. This code can be used to validate the data: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/Arrays+ArrayFileGetCsv~-~How~to~detect~invalid~CSV~line.txt

[stevengraff]

Thanks Deana and Jim, I will try it out.

[JTaylor]

Can't say how much I would use it from Arrayize but I could see it being helpful at times.  What I would really LOVE to see tweaked would be the ArrayFileGet() in that fashion.   The strict CSV requirement of ArrayFileGetCSV() can be annoying, as well as rendering it useless on a regular basis.   Actually not sure why I haven't requested this before (or maybe I have?).    It would be VERY nice to be able to load a delimited file into an array no matter how many quotes or whatever it has in a field.  Just plop the data in based on the specified delimiter and let me worry about how well formed it may or may not be

Thanks.

Jim

Makes an interesting case for adding an additional record delimiter parameter to the Arrayize function so that it could create multi-dimensional arrays (tables).  Don't know how many users would find it useful though.

[galaara98]

It's a mssql database, but I'm querying it through the GoldMine api, which lets me set the field delimiter and record delimiter. The data is returned as ordinary text, in a single string, with the delimiters embedded. Up 'til now I've dealt with the data using lists, primarily, but I suspect there may be some advantages to using arrays instead.

Here's a sample:

Curt Hicks800 Stephenson Hwy. Suite 15048237Al ChinnAl DurhamBrent BassettCurtis R. Hicks25900 Greenfield Road, Suite 50148237Dave ChildsJackie HeinnemanLaura Malmstrom

Makes an interesting case for adding an additional record delimiter parameter to the Arrayize function so that it could create multi-dimensional arrays (tables).  Don't know how many users would find it useful though.

ME! (well I worked around it, so not exactly right now... but ME!!!!)

I often use huge strings as data.  with @TAB as row delimeter and | as column delimiter.  But it makes it hard to ItemLocate :).. would love to be able to move between strings of 2 dimensional data and arrays of 2 dimensional data at will.

Aaron