Here's a little data-fetch function

[nordmann]

As a lazy programmer, I got tired of repeating the same half-dozen or so lines of code every time I wanted to fetch something from the database. So I cooked up a little function to abbreviate this process and save some keystrokes.

Function GetRecordsInTable As N (TableName As C, KeyColumn As C, KeyValue As C, ByRef TablePointer As P)
	Dim qryLocal As P
	TablePointer = Table.open("[PathAlias.ADB_Path]\\" + TableName)
	TablePointer.order(KeyColumn)
	query.filter = KeyColumn + " = " + quote(KeyValue)
	qryLocal = TablePointer.query_create()
	GetRecordsInTable = qryLocal.records_get()
	qryLocal.drop()
End Function

Usage example:

Dim myTable as P
GetRecordsInTable("myTable", "myKey","000002",myTable)
' Get your values from myTable here
.
.
myTable.close()
Delete myTable

This isn't my ideal way of structuring this function. At first I tried to make it so that it returned a pointer to the (possibly populated) table, and returned the number of records in a ByRef argument I called RecordsFound. But I couldn't get this to work. I kept getting the mysterious "Incorrect Argument type" error.

So I rearranged the function to return the number of records found and populate the table pointer as a side effect.

Be aware that this function returns an opened table. It is your responsibility to close and delete the table.

[Steve Workings]

Re: Here's a little data-fetch function

Are you familiar with table.external_record_contents_get() ?

[nordmann]

Re: Here's a little data-fetch function

Hi Steve,

Yes. I use that often as a one-liner to look up a single value from a field in a table, which is very handy at times. I'm also aware that you can use it to get multiple values from the same field in sequential records, returned in CRLF-delimited format. But I don't see how to access several columns of data in a single call.

But thanks for the reply!

[nordmann]

Re: Here's a little data-fetch function

Now that I've used this function a bit, I take back what I said about wanting to do this in the more usual way, where I would have returned a table pointer. By returning the number of records returned instead, I can put the function call and the test to see if it returned any records on the same line like so:

If GetRecordsFromTable("myTable","myKeyColumn","00000001",pMyTable) > 0 Then
' Get your stuff from the table
End If

--Erik

[Steve Workings]

Re: Here's a little data-fetch function

But I don't see how to access several columns of data in a single call.

The content_expression can name just a field or anything expression that you want. To return a full name, for instance, the content_expression can be "alltrim(firstname) + space(1) + alltrim(lastname)".

[nordmann]

Re: Here's a little data-fetch function

Hmmm...I appreciate the suggestion, but I would be a little nervous about that technique unless FirstName + " " + Lastname really was what I wanted to use in exactly that form. If I want to get the values of multiple columns to work with individually, then I prefer to rely on a well-defined interface.

For example, lets say you wanted to populate separate firstname and lastname fields on a dialog. Let's say a user has entered a record with "Kian Tat" as the firstname, and "Lim" as the last name. Attempts to manually parse the resulting string, "Kian Tat Lim", into first name and last name might not get the right result. You could separate the values with a pipe or a tab or some other character instead of a space, but my experience has been that eventually somebody enters one of these characters into a field, and then your parsing goes haywire again. The result can be especially dramatic if you're fetching and working with an entire database record containing dozens of fields, and the parsing goes wrong toward the beginning, causing all the fields to shift one to the right.

But yes, for getting the equivalent of a calculated field that uses several table fields onto a dialog control, table.external_record_contents_get(...) is a perfect one-line way of delivering the data I want.

Thanks,
--Erik

[Tim Kiebert]

Re: Here's a little data-fetch function

Erik,

I don't want to discourage you from writing your own functions, especially if you share them here. ;) (have you found the Code Archive in the 'Other Forums' section of this forum?)

But here are a couple of other functions that may be of interest to you.

--- <TBL>.RECORD_TO_VARS() - puts all the current record data into a pointer variable
--- PROPERTY_RECURSE_ASSIGN() - copies the pointer variable data into another pointer variable. This includes the pointer created when addressing a table.

So you could do something like

dim vRcrdData as P
tbl = table.open("customers")
'find the record you want
tbl.record_to_vars(vRcrdData)
'use vRcrdData in an xdialog. Each property could be a control on the dialog.
'change the data thereby changing the vRcrdData variable
'then 
tbl.change_begin()
property_recurse_assign(tbl, vRcrdData)
tbl.change_end(.t.)

tbl.close()     'I almost forgot his again Tom

You can use table.openmap() to limit the fields being accessed.