Clarion - SQL Statement

Author

SQL Statement

ikrami2000@gmail.com

2006-11-01, 3:55 am

sm_subscriber_master{Prop:SQL} = 'select sm_:subscriber_fname'& 'from
sm_subscriber_master'
loop
next(sm_subscriber_master)
message(sm_:subscriber_fname)
if error() then break.

End

message return empty

what's wrong how can i make select statement i dont know what is this
file --> ( file{Prop:sql} and what is the in select statement

Thanks

JonWat

2006-11-02, 6:55 pm

sm_:subscriber_fname is how Clarion references a particular column in a
table. Your server probably knows it as subscriber_fname. Also, the
clarion file buffer is filled by the query starting at the first
position.

This means that if, for example, your query is "select fname from t"

and your clarion file definition is

t
(id short,
fname string(30))

the select statement will try to put the returned value (the first
name) into the first column of the file/table buffer (a short), which
will not work very well.

ikrami2000@gmail.com wrote:
> sm_subscriber_master{Prop:SQL} = 'select sm_:subscriber_fname'& 'from
> sm_subscriber_master'
> loop
> next(sm_subscriber_master)
> message(sm_:subscriber_fname)
> if error() then break.
>
> End
>
> message return empty
>
> what's wrong how can i make select statement i dont know what is this
> file --> ( file{Prop:sql} and what is the in select statement
>
> Thanks

ikrami2000@gmail.com

2006-11-04, 3:55 am

i dont understand i have two files one from sql server ( i made import
and link with odbc ) and one i made it is same like who from sql server
so i want to loop in sql file and make message i can not do that
please help

JonWat wrote:[color=darkred]
> sm_:subscriber_fname is how Clarion references a particular column in a
> table. Your server probably knows it as subscriber_fname. Also, the
> clarion file buffer is filled by the query starting at the first
> position.
>
> This means that if, for example, your query is "select fname from t"
>
> and your clarion file definition is
>
> t
> (id short,
> fname string(30))
>
> the select statement will try to put the returned value (the first
> name) into the first column of the file/table buffer (a short), which
> will not work very well.
>
>
> ikrami2000@gmail.com wrote:

JonWat

2006-11-07, 6:55 pm

You probably have no need to be writing {PROP:SQL} code at all.

If your need is really as simple as that:

use a process template.

In the TakeRecord embed put
message(sm_:subscriber_fname)

Clarion takes care of the translation of the "clarion name" to the "SQL
server name"

If you are trying to copy from sql server to a tps file,

Add the tps file in the "Other files section.

In the TakeRecord embed add:

xxx:record :=: sm_:record
Access:Tpsfile.insert()

where xxx is the prefix of the tps file, and tpsfile is its name. This
will work so long as the columns have the same name in the clarion file
and the sql table.

Jon

ikrami2000@gmail.com wrote:[color=darkred]
> i dont understand i have two files one from sql server ( i made import
> and link with odbc ) and one i made it is same like who from sql server
> so i want to loop in sql file and make message i can not do that
> please help
>
>
>
> JonWat wrote:

arnold.latumanuwy@gmail.com

2006-12-11, 6:57 pm

hi there

here's an example I'd like to give you.

customer{prop:sql}='select * from customer where
trim(code)='&''''&clip(mCust)&''''
loop
next(customer)
if errorcode() then break.

message(cus:cust_name)
end

the sql statement above will results in showing all records from table
customer with code store in variable mCust

if you want to select all records from table customer then you should
write :

customer{prop:sql}='select * from customer'

I suggest before you wrote the sql statement in clarion you better
check the statement using Access Query or in MSSQL or Sybase SQL window
or if you're using Crystal Reports you can use Crystal Sql

I hope this information can help you.

Arnold L.

Sponsored Links