How to define a list of character type in alpha everywhere

[lisali87a]

Hi,
I want to pull a list of column names in one database table and saved those values into a list. How do I create a list of these column names, using variable allColName? ideally, I want to create the same return type (list) as a5_connstrings_list().


Dim ti as sql::tableInfo
Dim cn as sql::connection
Dim allColName as C

flag = cn.open("::Name::itemsdatabase")
cn.getTableInfo(ti,"Customer")

dim colCount as N
dim nCount as N
colCount = ti.Column.Count

allColName =""
for nCount = 1 to colCount
allColName[nCount] = ti.Column[nCount].name
next nCount


Thank you very much in advance,

Lisa

[Steve Wood]

Re: How to define a list of character type in alpha everywhere

I do it this way with MySQL, not sure which DB you are using:

dim cn as sql::Connection
dim rs as sql::resultset
cn.open("::name::conn")
vsql = "SELECT column_name FROM information_schema.columns WHERE table_name='web_users' AND table_schema = 'alphatogo'"
cn.execute(vsql)
rs = cn.ResultSet
lst = rs.tostring()
?lst
= id
companyid
userid
fname
lname
emailok
readterms
lastlogin
regstatus
regdate
regsource
regtemp

[Jim Coltz]

Re: How to define a list of character type in alpha everywhere

You can use the ListColumns() method of the Sql::TableInfo object.

dim tbllist as c
dim conn as sql::Connection
dim ti as sql::TableInfo
dim conn_name as c =  "::NAME::Northwind" 
if conn.Open(conn_name) then
	conn.GetTableInfo(ti,"Employees")
	conn.Close()
	tbllist = ti.ListColumns() 
end if

Gives me this:

EmployeeID
LastName
FirstName
Title
TitleOfCourtesy
BirthDate
HireDate
Address
City
Region
PostalCode
Country
HomePhone
Extension
Photo
Notes
ReportsTo

[Steve Wood]

Re: How to define a list of character type in alpha everywhere

Jim, Thanks! I have tried that before, never got it. We definitely should have an IADN round table/panel discussion on SQL Tips and Tricks. I have been keeping all of mine in a home-grown Help document that could eventually be published for others to use.

[lisali87a]

Re: How to define a list of character type in alpha everywhere

Steve and Jim,
Thank you very much for your quick reply. I tried both ways --
1. For solution Steve suggested, I met this error:

Script: TEMPLATE line:6
rs=cn.ResultSet
Function sequence error, while getting latest result set.'
cn.ResultSet subelement not found.

I think cn.ResultSet is a valid function, since it was auto popping up if I typed cn. So I don't understand what this error message suggested.

2. Jim's solution works.

Thank you both very much for your expert advice.
BTW, I have visited both of your product websites, they are both very impressive.


Regards,

Lisa

[Steve Wood]

Re: How to define a list of character type in alpha everywhere

Function sequence error, while getting latest result set.' cn.ResultSet subelement not found.

Means your sql statement failed. I dont know what database you are using so the syntax in my sql example could easily be off.