how to get column name

[Marko Moric]

I`m working on application which reads tables, and on the selected
table lists columns,so far so good,now i need to get column name when
selected and pass it to the query.How to do that?


....
Global b as String
Dim str_TableToUse, str_SQL as string
Dim a As SmallInt
Dim NumCols As SmallInt
Dim ColNumber(0) As Integer
Dim ColName(0) As String
Dim ChosenColumn As String
Dim ColIndex As SmallInt
.....

End Sub

Sub Work

'Check that the number of tables is greater than zero
If NumTables() < 1 Then
Note "Morate otvoriti najmanje jednu tabelu pre nastavka."
Exit Sub
End If

str_TableToUse = ""
'Creates a dialogue box that asks which table contains the regions to
use for selection
str_TableToUse = GetTableName("Izaberite tabelu nad kojom ce se
izvrsiti upit.", TRUE, False)

If str_TableToUse = "" Then
Exit Sub
End If

'this is where i call ChooseColumn
Call ChooseColumn

End Sub

....

....

Sub ChooseColumn

Dim b1 as String
NumCols = TableInfo(str_TableToUse, TAB_INFO_NCOLS)
ReDim ColName(NumCols)
For a = 1 To NumCols
ColName(a) = ColumnInfo(str_TableToUse, "col"&a, COL_INFO_NAME)
'Print ColName(a)
Next

ChosenColumn = " " ' what code do i need to get column name nad
passed to the query?!
Print ChosenColumn
Dialog
Title "Select a Column"
'Width 250 Height 250

Control StaticText
Title "Izaberite kolonu po kojoj se radi pretraga:"

Control ListBox
Title From Variable ColName
Position 10, 25
Width 180 Height 180
Value 1
ID 1
Into ColIndex

Control StaticText
Title "Kriterijum"
Position 10,210
Control EditText
Value b
Into b
Width 70
Position 50,210

Control OkButton
Position 55, 240
Control CancelButton
Position 105, 240

If CommandInfo(CMD_INFO_DLG_OK) Then

b = b1+"%"

SELECT * FROM str_TableToUse Where ChosenColumn LIKE b Into
SELECTION
Browse * From Selection
' Run Command str_SQL

End If
End Sub

[Peter Horsbøll Møller]

Marko,

This should do it:

Dim aCol As Alias

   ...

   If CommandInfo(CMD_INFO_DLG_OK) Then
      b = b1+"%"
      aCol = str_TableToUse & "." & ColName(ColIndex)
      SELECT * FROM str_TableToUse Where aCol LIKE b Into SELECTION

      '**Here you might want to check if there was something selected
      Browse * From Selection
   End If


Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

2009/11/30 Marko Moric <morkeda...@gmail.com>

[Marko Moric]

I still didn`t manage this code to work the way i want to.So i wrote a
new code with the same issues,

list all open tables,
choose table,
choose a column under selected table,
and type in criteria for filtering data from .TAB

So,as u can see i`m trying to make dynamically a SQL query for
searching the data.

and this is the code :

Include "mapbasic.def"

Declare Sub Main
Declare Sub FillColumnlist
Declare Sub PrintColumn

Global a_1, kolona,kolona1, tabela as String

dim strColList() as string
dim strTableList() as string
Dim aCol As Alias

dim intCounter,intTableid,intColumnid,intTabnum,intColnum as integer
dim strColname,strTabname,a1 as string

sub main
'Get the number of tables currently open and redimension the table
array.
intTabnum = NumTables()
redim strTablelist(intTabnum)

'Populate the table array with the table names
for intCounter = 1 to intTabnum
strTablelist(intCounter) = TableInfo(intCounter,TAB_INFO_NAME)
next

'Get the number of columns in the first table and redim the column
array
intColnum = NumCols(strTablelist(1))
redim strColList(intColnum)

'Populate the column array with the column names
for intCounter = 1 to intColnum
strColList(intCounter) = ColumnInfo(strTablelist
(1),"COL"+intCounter,COL_INFO_NAME)
next

'initialize the intTableid variable so that it always points to a
table
intTableid=1

'Create the dialog with two popupmenus and an OK button
dialog
title "Izaberi tabelu i kolonu"
control popupmenu
ID 1
calling fillcolumnlist
title from variable strTablelist
control popupmenu
ID 2
title from variable strColList
control StaticText
Title "Kriterijum"
ID 3
Control EditText
Value a_1
Into a_1
control OKButton
calling PrintColumn

If CommandInfo(CMD_INFO_DLG_OK) Then

a1 = a_1 + "%"
tabela = strTabname
kolona1 = ColumnInfo(tabela, "col1", COL_INFO_NAME)
aCol = strTabname & "." & kolona1
'Run Command "Select * From " & tabela & " Where " & tabela & "
LIKE " & a1
'Run Command"SELECT * FROM " + tabela + " Where " + kolona1 + "= "
+ a1 + "Into SELECTION"
SELECT * FROM tabela Where aCol LIKE a1 Into SELECTION
BROWSE * FROM SELECTION
end if
end sub


'This procedure re-populates the column array with the column names in
'the table the user picked from the table popup menu.
sub fillcolumnlist
intTableid = ReadControlValue(1)
strTabname = TableInfo(strTablelist(intTableid), TAB_INFO_NAME)
intColnum = NumCols(strTabname)
redim strColList(intColnum)
for intCounter = 1 to intColnum
strColList(intCounter) = ColumnInfo(strTablelist
(intTableid),"COL"+intCounter,COL_INFO_NAME)
next

'Update the column popup menu with the new column array
Alter Control 2 Title From Variable strColList
end sub


sub PrintColumn
'Get the name of the column the user picked and print it on screen.
intColumnid = ReadControlValue(2)
strColname = ColumnInfo(strTablelist
(intTableid),"COL"+intColumnid,COL_INFO_NAME)
kolona = strColname
'print kolona
'print strColname
end sub

[Peter Horsbøll Møller]

Marko,

Try to replace your PrintColumn procedure with this one:

sub PrintColumn

Dim    aCol As Alias,
    sTab, sCol, sValue As String

    'Get the name of the column the user picked and print it on screen.

    sTab    = strTablelist(ReadControlValue(1))
    sCol    = strColList(ReadControlValue(2))

    sValue    = ReadControlValue(4) + "%"
    aCol = sTab & "." & sCol
    Print "Select * From " & sTab & " Where " & sCol & " LIKE " & sValue
    Select * FROM sTab Where aCol LIKE sValue Into SELECTION
    If SelectionInfo(SEL_INFO_NROWS) > 0 Then
        BROWSE * FROM SELECTION
    Else
        Note "No matching records found!"
        Dialog Preserve
    End If
end sub

And also delete the code after If CommandInfo(CMD_INFO_DLG_OK) Then so that that part looks like this (or omit it completely):

If CommandInfo(CMD_INFO_DLG_OK) Then
     '**You could omit this completely
End If

I changed the PrintColumn procedure to do the querying. This also allows you to tell the user that no match was found, and the user can immediately change the condition.

In this way you should also be able to minimise the use of modular variables. The only modular variables you would need are the arrays with the table and column names and maybe the variable holding the value to search for, if that should be presented to the user next time the dialog is opened. Also note that you could use a modular variable for this in stead of a global, unless you need to access the variable from other applications thru DDE

HTH,

Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

2009/12/2 Marko Moric <morkeda...@gmail.com>

[Marko Moric]

Ok Peter,thank you so much.
There is two things needed to change in order this app should
working.

1. control StaticText

Title "Kriterijum"
ID 3
Control EditText
Value a_1
Into a_1

2.

sValue = ReadControlValue(4) + "%"

-- after changing --

1.
control StaticText
Title "Kriterijum"
Control EditText
ID 3
Value a_1
Into a_1
2.
sValue = ReadControlValue(3) + "%"

and now works just fine,

once again thank you,you are true MB master :)

[Marko Moric]

And yes,i have one more question.

As u can see,this search is gonna work if u input variable type
String,but i want this to work and user input integers.
The question is what kind type of variable should i declare for sValue
so it can return string and integers?

[Peter Horsbøll Møller]

Marko,

 

If you still want to use the Like operator you should keep the current types and only in your query "cast" the values in the column to string before comparing these to the variable.

 

Like this:

 

... Str$(aCol) Like sValue ...

 

Or you can just limit the column to the Char type columns ;-)

And sorry about the mistake with the changes. I had also made some other changes - I just forgot that.

Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

 

 

2009/12/2 Marko Moric <morkeda...@gmail.com>

And yes,i have one more question.

[Marko Moric]

So that was the catch.

i tried to cast sValue,it seemed to me more appropriate that way or
maybe it`s just because i`m .Net developer so i would do that
in .Net :)
yes,it works nice and smooth :)

And it seems that i always have one more quastion for you hehe :)
I`m wondering is it possible to do dynamic query like this one but
with two or more tables,i guess that it is possible but this table
have to be linked
with some columns.Am i right?
I`m kinda new to mapBasic and i don`t have much time to try things
because of other work.

[Peter Horsbøll Møller]

Marko,

 

Sounds more like an SQL problem than a MapBasic problem :-)

It should be possible but as you write yourself, you would need some kind of "link" between the tables, at least if you want to join them.

 

Can you give an example of what you are trying to do?

Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

 

 

2009/12/2 Marko Moric <morkeda...@gmail.com>

So that was the catch.