How to retrieve field names from SQL Server 
Author Message
 How to retrieve field names from SQL Server

Good morning!

Today's msision, if you're not just in from somewhere where you're freezing
your buns off like I am (Cleveland, Ohio), is to figure out how, given a
connection to a system DSN, to get Sql Server to return all of the field
names in a given table. Like AFields() used on a Foxpro table.

Is there an easy, fast way?

Thanks in advance for any suggestions you care to offer...

Bill Blackman



Sun, 27 Aug 2000 03:00:00 GMT  
 How to retrieve field names from SQL Server

If you open a cursor using sql pass thru i.e.
=sqlexec( h, "Select * from table where 1 = 2", "cursorname" )
You can then just use afields on the cursor.

ben


Quote:
>Good morning!

>Today's msision, if you're not just in from somewhere where you're freezing
>your buns off like I am (Cleveland, Ohio), is to figure out how, given a
>connection to a system DSN, to get Sql Server to return all of the field
>names in a given table. Like AFields() used on a Foxpro table.

>Is there an easy, fast way?

>Thanks in advance for any suggestions you care to offer...

>Bill Blackman



Sun, 27 Aug 2000 03:00:00 GMT  
 How to retrieve field names from SQL Server

Bill,

Check out VFP's functions: SQLTABLES() to get a list of tables, and
SQLCOLUMNS() to get a list of fields for a specific table.

Hope this helps.  --Tom


Quote:

>Good morning!

>Today's msision, if you're not just in from somewhere where you're freezing
>your buns off like I am (Cleveland, Ohio), is to figure out how, given a
>connection to a system DSN, to get Sql Server to return all of the field
>names in a given table. Like AFields() used on a Foxpro table.

>Is there an easy, fast way?

>Thanks in advance for any suggestions you care to offer...

>Bill Blackman



Mon, 28 Aug 2000 03:00:00 GMT  
 How to retrieve field names from SQL Server

Ben,

It took some time to get back to trying your suggestion.
In your =sqlexec( handle, "Select * from table where 1 = 2","cursorname" )
example,
what the heck were you presuming the connection was to? Master?
I ask because I kept getting -1, indicating a connection-level error.

Thanks anyhow!
Bill Blackman



Wed, 30 Aug 2000 03:00:00 GMT  
 How to retrieve field names from SQL Server

Tom,

It took some time to get back to this project.
In the meantime, I had ended up where you had suggested.

I can make the connection OK, but trying to get a list of the tables
consistently fails.
?SQLCONNECT('SqlSrv: Adm_DB_Work', 'sa', '********' )
returns a >0 connection handle, indicating successful connection.
?SQLTABLES( tgtHandle, "'TABLES'", "tgtTablesCursor")
returns 1, indicating successful creation of the cursor.

Darn cursor is ** always ** empty. It has the columns it should, but does
not show anything about the 4 tables in this database that SQL Server's
Enterprise Manager on this same workstation will happily show me!

Thought about security stuff, but I am the sa and the owner of this database
and all in it.

Any thoughts?

Thanks for your earlier response and for anything you can suggest!

Bill Blackman



Wed, 30 Aug 2000 03:00:00 GMT  
 How to retrieve field names from SQL Server

Hi Bill,

Sorry, I kind of used a shorthand.  You should replace "table" with
the name of the table whose columns you want.  For instance if
the table name is "authors" the following code would list the table names.

=sqlexec( h, "Select * from authors where 1 = 2", "cname")
for i = 1 to fcount()
    ?field(i)
endfor

Also on the Tables thing the other Gentleman talked about.  This
code prints out the table and view names for me.
=sqlTables( h, "'TABLE', 'VIEW'", "tcursor")
scan
    ?table_name
endscan

I did notice that it was picky about the syntax on the
"'TABLE', 'VIEW'".  They had to be upper case, surrounded within
the double quotes with single quotes and there had to be a space
after the comma.

I haven't used SQLCOLUMNS recently as I always have a cursor,
but it might be slightly more efficient.

ben

Quote:

>Ben,

>It took some time to get back to trying your suggestion.
>In your =sqlexec( handle, "Select * from table where 1 = 2","cursorname" )
>example,
>what the heck were you presuming the connection was to? Master?
>I ask because I kept getting -1, indicating a connection-level error.

>Thanks anyhow!
>Bill Blackman



Fri, 01 Sep 2000 03:00:00 GMT  
 How to retrieve field names from SQL Server

Ben,

Thank you again!

Looking very closely at your syntax was the key to getting both sqltables()
and sqlcolumns()
working. Found another little gotcha: if you supply a cursor name (probably
for any of the sql... calls), and if you intend to, eg., Select that cursor
later on in your program, that cursor name must be <= 10 characters long.

Question: What I'd planned to do was to use sqlexec and sqlcommit in this
vfp program to load the table. Insert into/bcp on that table being set True
earlier on.

 dbRetValue = SQLEXEC( tgtHandle, InsertSQL )
 dbRetValue = SQLCOMMIT( tgtHandle )       && Commit the transaction

Do you have good experience with the speed and reliability of this method,
or does your experience suggest another way?
Maybe something like creating a local cursor (hopefully not using Create
Cursor ?) matching the column layout of the target sql server table,
Appending to that local cursor, and then somehow appending from the local
cursor to the target sql server table?
(That target table has an identity column, autofill, by the way.)

Ben, I appreciate your suggestions. So far, they've brought to a halt some
4+ hours of whacking my forehead against the same syntax walls! 8-)

Bill Blackman



Fri, 01 Sep 2000 03:00:00 GMT  
 How to retrieve field names from SQL Server

I'm not so sure about performance at this stage.  Perhaps someone with
more in depth experience can chime in...  I am in the middle of a
conversion to SQL Server from foxpro files myself.  Another approach
to what you want to do would be to create an empty cursor on the table,
set the cursor to be updatable, then do "Append Blank" and Replace
statements to populate the cursor and then do a TableUpdate, however I
do not know how this would compare performance-wise.

Here's the code to make a cursor updatable
 cursorsetprop( "buffering", 5 )
 cursorsetprop( "sendupdates", .t. )
 cursorsetprop( "Tables", tablename )
 cursorsetprop( "keyfieldlist", keyfield )
 cursorsetprop( "updatablefieldlist", fl)
 cursorsetprop( "updatenamelist", nl )
 cursorsetprop( "wheretype", 1 )
The variable "tablename" must be set to the name of the table.
The variable keyfield must be set to a list of the fields in a unique key
for
the table, again separated by ", ".
The variable fl must be set to a list of the fields in the table that you
wish
to update again separated by ", "
The variable nl must be set to the same fields as fl but here you have to
append the tablename at the beginning.  I.e.  authors.id, authors.fname, ...

The SQL Server documentation suggests that the bcp program supplied
with SQL Server is the fastest way.  I have not yet played with that program
and it sounds like the syntax is pretty cryptic, but if you can get the data
into
external ascii files relatively easily bcp is probably the fastest way to
go.

Also for large data loads you should drop the indexes on the tables and
then add them back in after the inserts are complete.

Hope this helps.

ben



Fri, 01 Sep 2000 03:00:00 GMT  
 How to retrieve field names from SQL Server

Ben,

Again, thank you!

Re. bcp. The program I'm working on is plan B; bcp was plan A, especially
for the larger FP tables (we have a whole series of them ranging from 20K to
around 300K records).
After having spent a week or so trying to get bcp working, let alone working
reliably in fast mode, I figured I'd better find a better way.Plan was to
bcp into a work table w/no indexes, reindex, and then use sql to update the
live sql server table.

The little program I've been working up has a couple other functions besides
recreating a sql server table and appending data to that table.
One such functions is that it will create a bcp format file exactly matching
the current layout of the source FP table.
A second such function is to create the ascii file for import via bcp (with
whatever delimiters seem to work best, and applying default values to empty
fields such as dates).
I've tried VFP's COPY TO to create the bcp import file, have had fits, and
am not at all sure why.

You can read between the lines as to why I dropped back into this little
project after having tried bcp. Most likely, at some point I'll probably
revisit bcp.

Ben, in particular I appreciate the code snippets in your last; as you've
found out, they can be invaluable. It will take me a bit to digest what's
happening therein, and to plug them in and test them.

Thanks again!
Bill Blackman



Sat, 02 Sep 2000 03:00:00 GMT  
 How to retrieve field names from SQL Server

You should see the Data Transport Tool in microsoft SQL Server 7.0 .  (I am
currently running under Beta 2 of SQL).  It takes away a LOT of headache
that the BCP created.   (Most of which is the pure lack of functionality).
It really makes my life a lot easier in upsizing a cheezey-lame dbase
system.  I REALLY recomend getting your hands on the beta version of this
product.  (I currently am using the 7.0 server and tools to do my
development) and then use the Data Trans. Tool to move it back to 6.5 until
there full release is out.

Oh well.. :)

$.02

Quote:

>Ben,

>Again, thank you!

>Re. bcp. The program I'm working on is plan B; bcp was plan A, especially
>for the larger FP tables (we have a whole series of them ranging from 20K
to
>around 300K records).
>After having spent a week or so trying to get bcp working, let alone
working
>reliably in fast mode, I figured I'd better find a better way.Plan was to
>bcp into a work table w/no indexes, reindex, and then use sql to update the
>live sql server table.

>The little program I've been working up has a couple other functions
besides
>recreating a sql server table and appending data to that table.
>One such functions is that it will create a bcp format file exactly
matching
>the current layout of the source FP table.
>A second such function is to create the ascii file for import via bcp (with
>whatever delimiters seem to work best, and applying default values to empty
>fields such as dates).
>I've tried VFP's COPY TO to create the bcp import file, have had fits, and
>am not at all sure why.

>You can read between the lines as to why I dropped back into this little
>project after having tried bcp. Most likely, at some point I'll probably
>revisit bcp.

>Ben, in particular I appreciate the code snippets in your last; as you've
>found out, they can be invaluable. It will take me a bit to digest what's
>happening therein, and to plug them in and test them.

>Thanks again!
>Bill Blackman



Sat, 02 Sep 2000 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. How to retrieving the autoincrement fields value from SQL server

2. SQL DATABASE and SERVER name from VFP

3. How to get SQL Server Names

4. Long Field Names in SQL Query

5. changing field name headings in a cursor from an SQL search

6. SQL field name truncated

7. Import From SQL with field name more than 10 Chr

8. Retrieving UNC Name

9. Insert content from General field into SQL Server database

10. Memo Fields and SQL Server

11. SQL server image fields.

12. SQL Server image fields

 

 
Powered by phpBB® Forum Software