|
Templates, Tools and Utilities
for Clarion Developers
|
 |
 |
|
Icetips Article
Back to article list
Search Articles
Add Comment
Printer friendly
Direct link
SQL Related Articles: Using Views in a procedure to retrieve data
2004-08-21 -- Erik Pepping
Newsgroups: sv.clarion.bugreports
> Anyone willing to share a couple of view statements??? the one in the
> example aint cuttin the mustard.... i want to select records from a field
> that is a string....
>
> something like this one....
>
> select *
> from inventory
> where status = 'A' and releasenumber > 0
We are using a make Sql function for this.
This function returns your Select field21,field 2 from a file you pass it.
We inspect the file lables for this.
here is the code. (part of our connection class, commercially available,
contains many more functions!)
Connection.MakeSql Procedure(*FILE In_file, String in_where,Byte
in_perform_next, , )
NumLabels Short
i Short
Sql Cstring(5000)
Label Cstring(50)
Fieldsingroup Ushort
TableName CString(62)
FileRecord &Group
Code
TableName = in_file{Prop:Name}
If Instring('.', TableName,1,1)
TableName = TableName[Instring('.', TableName,1,1) + 1 : Len(TableName)]
end
Numlabels = in_file{prop:fields}
FileRecord &= In_File{Prop:Record}
Loop i = 1 To Numlabels
Label = Who(FileRecord,i)
If fieldsingroup <> 0
FieldsIngroup = fieldsingroup - 1
Cycle
End
If in_file{Prop:Type,i} = 'GROUP'
If in_file{Prop:Over,i} <> 0
fieldsInGroup = in_file{PROP:Fields,I}
End
Cycle
End
If in_file{Prop:Over,i} <> 0
Cycle ! Over some field
End
If Instring(':',Label,1,1) <> 0
Label = Clip(Sub(Label,InString(':',Label,1,1)+1,255))
End
If Instring('|',Label,1,1) <> 0
Label = Clip(Sub(Label,1,InString('|',Label,1,1)-1))
End
Sql = Sql & ',' & TableName & '.' & Label
end
If Size(Sql) > 2
If AddDistinct = 1
SQL = 'SELECT DISTINCT ' & Sql [2: Size(Sql)]
Else
Sql = 'SELECT ' & Sql [2: Size(Sql)]
End
End
If in_from = ''
SQL = Sql & ' FROM ' & in_file{Prop:Name}
Else
SQL = Sql & ' FROM ' & Clip(in_from)
End
If in_where <> ''
SQl = SQL & ' WHERE ' & in_where
End
If In_Perform_next = 2
Message(Sql) ! Hidden debug option
End
If in_perform_next <> 0
in_file{prop:sql} = Sql
If ErrorCode()
Message('ConOracle.MakeSql Failed 1:' & Error() & Clip(Fileerror()) &|
Sql & '| Length = ' & len(Clip(Sql)) )
SetClipboard(sql)
Return(0)
End
Next(in_file)
If ErrorCode()
If ErrorCode() = 35 or ErrorCode() = 33
Return(0)
Else
Message('ConOracle.MakeSql Next Failed:' & Error() &|
Clip(Fileerror()) & Sql & '| Length = ' & len(Clip(Sql)) )
SetClipboard(sql)
Return(0)
End
Else
Return(1)
End
Else
in_file{prop:sql} = Sql
If ErrorCode()
Message('ConOracle.MakeSql Failed 2:' & Error() & Clip(Fileerror()) & Sql)
SetClipboard(sql)
Return(0)
Else
Return(1)
End
End
Return(1)
Today is November 21, 2024, 3:58 am
This article has been viewed 35228 times.
Google search
has resulted in 31 hits on this article since January 25, 2004.
Back to article list
Search Articles
Add Comment
Printer friendly
|
|
|
