Get order Source Key from the database?

[slambert123]

I've inherited a legacy script from a client. The script already pulls a bunch of order information and exports it to a 3rd party platform. I need to add the "source key" to the list of information being pulled from the database. I can't find anything online about this, nor have I found a way to access the database to look for the fieldname myself.

Could someone help point me in the right direction?

 

[Olaf Doschke]

What do you mean with "source key"? No wonder you don't find such a thing, it's not a usual DB term. Do you mean the primary key of the record?

MODIFY TABLE, take a look at the index tab and find a primary index, if there is none, look for a candidate index, if there is none, look for some field having unique values.

One more thing to find the primary index programmatically, use Primary().

I hope this helps.

Bye, Olaf.

 

[JRB-Bldr]

nor have I found a way to access the database to look for the fieldname myself.

Let's start here...

Don't you have Foxpro or Visual Foxpro to use?
If so, you only need to do the following from the Command Window:

USE ThisDataTable && Open Specific Data Table - for this example: ThisDataTable
MODI STRU && Examine the Data Table's various field(s) - name, type, size, etc.​

If you do not have Foxpro/Visual Foxpro, then you have a much bigger challenge in supporting the legacy application.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Slambert,

Welcome to the forum. As you've seen from the other replies, you really need to clarify your question. But please don't let that put you off. I'm sure we can help you if you can just give us the information we need.

Is it possible that "sourcekey" is the name of a field in the database (in which case, it would be a single word, without the space)? Or, as Olaf suggests, you mean the primary key? A primary key is often the main identifier for a record, such as a customer ID or a product number.

What exactly is the "script" that you have been given? Is it a Visual Foxpro program file (which is essentially a text file containing FoxPro code, with the extension PRG)? Or is it a self-contained program, that is, an EXE file? Or what?

Finally, if you don't know what the "source key" is, or how to access the database, have you tried asking the client?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[slambert123]

Clarification:

My client uses a software called Mail Order Management. One of the fields in the order data is "Source Key". I need to figure out what the field name is in the database. The script is already getting things like order number, first name, last name, etc. I'm not sure what the name of the source key field is.

Is there a way to get a list of field names and print them out to the screen so that I can tell which is the correct field name?

 

[slambert123]

I'm using visual foxpro

 

[JRB-Bldr]

Is there a way to get a list of field names and print them out to the screen so that I can tell which is the correct field name?

USE ThisDataTable && Open Specific Data Table - for this example: ThisDataTable
DISPLAY STRUCTURE && Output list of fieldnames, types, etc. to screen​

You might want to look at your VFP Help file for DISP STRU to see the options available for use with that command.

NOTE - Displaying the field names, etc. will show you a lot, but it will NOT tell you what field is being used within the application as a 'Source Key'.
Your best bet would be to look at the source code of the application to see how the tables are being related.
However if your Mail Order Management is an off-the-shelf application getting its source code might be a problem. If that is the case, your best bet would be to go to the vendor/developer for support.

Good Luck,
JRB-Bldr

 

[slambert123]

I found the answer myself by hacking together some code. I'm going to explain it all here, but I don't guarantee that any of it works, nor do I recommend using it.

mstr = "[Driver=SQL Server; Server=SQL; UID=;PWD=;APP=Microsoft Visual FoxPro WSID=;DATABASE=database_name;Trusted_Connection=Yes]"
lcconnectionstring = &mstr
oconn = SQLSTRINGCONNECT(lcconnectionstring)
TEXT TO msql NOSHOW
select *
from database_name
ENDTEXT
SQLEXEC(oconn, msql, 'output')
SELECT * FROM output to file test.txt


This outputted a jumbled mess of data into a plain text file. I then searched for known key values. After I found one, I carefully figured out which header it belonged under.

Thanks for all the help everyone!

 

[Dan Freeman]

You could have shortened this discussion by asking "How do I get field names from a SQL server database"

We probably would have suggested VFP's SQLColumns() function.

 

[Olaf Doschke]

...And on top of that we wouldn't have recommended ways to determine the structure of a DBF table, of course SQL Server is a seperate world. Besides SQLColumns, you may also query system views like INFORMATION_SCHEMA.
Insted of TO FILE you could simply have used BROWSE to get a grid view of the queried data, in which you would have it much easier to match a certain known source key value with the field name on top than in a "mess of data into a plain text file". SQLExec() already creates a VFP cursor to view in the datasession window or via BROWSE command, in your case the cursor would be named 'output'.

@slambert123: You might no have recognized it, but VFP is not only a programming language and Winforms based frontend to data, it has it's own database and tables. If you talk about tables in a VFP forum, we mostly will consider DBFs, although as you see many of us also use SQL Server backends.

Bye, Olaf.