| || |
Hi, I wanted to retrieve the names of the columns of a database table (from SQLServer2000) and print them on the results file. Based from the Help facility of SilkTest, a proper way to do this is by using the statement:
hstmnt = DB_Columns (hdbc, “catalog-name”, “schema-name”, “table-name”, “column-name”)
I've tried using this followed by a DB_FetchNext() statement... but it does not return any result.
1. Is the wildcard "*" accepted as an argument to this function?
2. What is the catalog-name and schema-name? can these be omitted and replaced by "", if lets say, i wanted to see the columns of EMPLOYEE_SALARY table that is in the EMP db?
3. Is there any other way of doing this?
There is a stored procedurein MSSQL called sp_columns that you could use.
use it as you would any sql command.
[ ] hdbcSQL = DB_Connect("dsn="+DSNSQL+";UID="+UIDSQL)
[ ] hstmntSQL = DB_ExecuteSql (hdbcSQL, "sp_columns " + Table_Name)
[ ] //get all descriptions of the SQL table
[-] while (DB_FetchNext (hstmntSQL, Temp_Table.Table_Qualifier, Temp_Table.Table_Owner, Temp_Table.Table_Name, Temp_Table.Column_Name, Temp_Table.Data_Type, Temp_Table.Type_Name, Temp_Table.Precision, Temp_Table.Length, Temp_Table.Scale, Temp_Table.Radix, Temp_Table.Nullable, Temp_Table.Remark, Temp_Table.Column_Def))
First, I'm assuming you first used the DB_Connect function to make a connection to the database. If not, it's format for a SQL Server database would look like:
HDBC hDatabase = DB_Connect (“dsn=DATASOURCE; service=SERVICE NAME; uid=USERID; pwd=PASSWORD”)
Also, I'm assuming you used the DB_FetchNext function properly. Make sure that the parameters to this are set properly, and that you should have some kind of loop around the function to then print out the results. Something like:
while (DB_FetchNext(hDatabase, sAttribute1, sAttribute2, sAttribute3, sColumnName, sDataType))
print (sAttribute1, sAttribute2, sAttribute3, sColumnName, sDataType)
Then, to answer your questions:
1) Use a wildcard to what function? DB_Columns? You can use a wildcard for one of the particular parameters. For example, if you wanted to get the columns for several columns starting with the letter A, you'd set the "table_name" to "A%" I beleive that the "%" is used as a wildcard character here instead of the "*."
2) As far as SQLServer is concerned, the catalog_name is the name of the database, and the schema_name is the owner of the database. You should probably know the database name, and if you don't know the owner's name, try "dbo" which is the Administrator default.
3) James supplied the other option.
Hope this helps!
Thanks so much, James and BostonSilk!