CREATE SQL VIEW problem

I have the following short VFP8 program:

one_cust = '1234'
CREATE SQL VIEW cust_view Remote CONNECTION AS400 AS ;
SELECT cust_code,cust_name;
FROM customer;
WHERE cust_code =one_cust
/Memo: customer is an IBM AS400 file/
If I run this program I get an error message: "ONE_CUST" is not part of the
table.
If I use a constans /....WHERE cust_code = '1234' the program works well.
My question is : Is it possible to use a variable or not? Where did I make a
mistake?
Another question: How can I create a LOGICAL calculated field in this SELECT
command?
Could anyone help me?
Thanks in advance.
--
Sandor

Thierry
Tue Nov 30 01:36:33 CST 2004

A correct syntaxe is :

... WHERE cust_code =?m.one_cust

--
Thierry


"Sandor Hajdu" <SandorHajdu@discussions.microsoft.com> a écrit dans le
message de news: A8034A6D-91F3-4B9D-B72F-F59E993E14D2@microsoft.com...
>I have the following short VFP8 program:
>
> one_cust = '1234'
> CREATE SQL VIEW cust_view Remote CONNECTION AS400 AS ;
> SELECT cust_code,cust_name;
> FROM customer;
> WHERE cust_code =one_cust
> /Memo: customer is an IBM AS400 file/
> If I run this program I get an error message: "ONE_CUST" is not part of
> the
> table.
> If I use a constans /....WHERE cust_code = '1234' the program works well.
> My question is : Is it possible to use a variable or not? Where did I make
> a
> mistake?
> Another question: How can I create a LOGICAL calculated field in this
> SELECT
> command?
> Could anyone help me?
> Thanks in advance.
> --
> Sandor

Stefan
Tue Nov 30 01:41:08 CST 2004


With MS SQLServer and other RDBMS, a question mark would make
Vfp read and submit variable content:
... WHERE cust_code = ?one_cust


Does that help?
-Stefan

"Sandor Hajdu" <SandorHajdu@discussions.microsoft.com> schrieb im Newsbeitrag
news:A8034A6D-91F3-4B9D-B72F-F59E993E14D2@microsoft.com...
>I have the following short VFP8 program:
>
> one_cust = '1234'
> CREATE SQL VIEW cust_view Remote CONNECTION AS400 AS ;
> SELECT cust_code,cust_name;
> FROM customer;
> WHERE cust_code =one_cust
> /Memo: customer is an IBM AS400 file/
> If I run this program I get an error message: "ONE_CUST" is not part of the
> table.
> If I use a constans /....WHERE cust_code = '1234' the program works well.
> My question is : Is it possible to use a variable or not? Where did I make a
> mistake?
> Another question: How can I create a LOGICAL calculated field in this SELECT
> command?
> Could anyone help me?
> Thanks in advance.
> --
> Sandor

SandorHajdu
Tue Nov 30 03:07:03 CST 2004

Could you tell me where can I find the other MS SQL query sysntaxs?
Sandor
"Stefan Wuebbe" wrote:

>
> With MS SQLServer and other RDBMS, a question mark would make
> Vfp read and submit variable content:
> ... WHERE cust_code = ?one_cust
>
>
> Does that help?
> -Stefan
>
> "Sandor Hajdu" <SandorHajdu@discussions.microsoft.com> schrieb im Newsbeitrag
> news:A8034A6D-91F3-4B9D-B72F-F59E993E14D2@microsoft.com...
> >I have the following short VFP8 program:
> >
> > one_cust = '1234'
> > CREATE SQL VIEW cust_view Remote CONNECTION AS400 AS ;
> > SELECT cust_code,cust_name;
> > FROM customer;
> > WHERE cust_code =one_cust
> > /Memo: customer is an IBM AS400 file/
> > If I run this program I get an error message: "ONE_CUST" is not part of the
> > table.
> > If I use a constans /....WHERE cust_code = '1234' the program works well.
> > My question is : Is it possible to use a variable or not? Where did I make a
> > mistake?
> > Another question: How can I create a LOGICAL calculated field in this SELECT
> > command?
> > Could anyone help me?
> > Thanks in advance.
> > --
> > Sandor
>
>

Stefan
Tue Nov 30 03:12:54 CST 2004

BOL / "SQLServer Books Online"
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
These groups are full of examples too
http://groups.google.com/advanced_group_search


hth
-Stefan

"Sandor Hajdu" <SandorHajdu@discussions.microsoft.com> schrieb im Newsbeitrag
news:09381EC6-3B37-4B1E-9782-E424D1647480@microsoft.com...
> Could you tell me where can I find the other MS SQL query sysntaxs?
> Sandor
> "Stefan Wuebbe" wrote:
>
>>
>> With MS SQLServer and other RDBMS, a question mark would make
>> Vfp read and submit variable content:
>> ... WHERE cust_code = ?one_cust
>>
>>
>> Does that help?
>> -Stefan
>>
>> "Sandor Hajdu" <SandorHajdu@discussions.microsoft.com> schrieb im Newsbeitrag
>> news:A8034A6D-91F3-4B9D-B72F-F59E993E14D2@microsoft.com...
>> >I have the following short VFP8 program:
>> >
>> > one_cust = '1234'
>> > CREATE SQL VIEW cust_view Remote CONNECTION AS400 AS ;
>> > SELECT cust_code,cust_name;
>> > FROM customer;
>> > WHERE cust_code =one_cust
>> > /Memo: customer is an IBM AS400 file/
>> > If I run this program I get an error message: "ONE_CUST" is not part of the
>> > table.
>> > If I use a constans /....WHERE cust_code = '1234' the program works well.
>> > My question is : Is it possible to use a variable or not? Where did I make a
>> > mistake?
>> > Another question: How can I create a LOGICAL calculated field in this SELECT
>> > command?
>> > Could anyone help me?
>> > Thanks in advance.
>> > --
>> > Sandor
>>
>>

SandorHajdu
Wed Dec 01 10:33:09 CST 2004

Stefan,

I think I need more help.
The short program is part of a form. If I run the form for the first time no
problem, everything is OK. If I chage the parameter and run again /I do not
release the form/
I got the following error:
Driver is probably out of resources.
Could you tell me why I get this message? What's wrong in my program?
/I tried to close the connection and database and open again - what I do
when I start the form - but I have the same message./

Sandor

"Stefan Wuebbe" wrote:

> BOL / "SQLServer Books Online"
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> These groups are full of examples too
> http://groups.google.com/advanced_group_search
>
>
> hth
> -Stefan
>
> "Sandor Hajdu" <SandorHajdu@discussions.microsoft.com> schrieb im Newsbeitrag
> news:09381EC6-3B37-4B1E-9782-E424D1647480@microsoft.com...
> > Could you tell me where can I find the other MS SQL query sysntaxs?
> > Sandor
> > "Stefan Wuebbe" wrote:
> >
> >>
> >> With MS SQLServer and other RDBMS, a question mark would make
> >> Vfp read and submit variable content:
> >> ... WHERE cust_code = ?one_cust
> >>
> >>
> >> Does that help?
> >> -Stefan
> >>
> >> "Sandor Hajdu" <SandorHajdu@discussions.microsoft.com> schrieb im Newsbeitrag
> >> news:A8034A6D-91F3-4B9D-B72F-F59E993E14D2@microsoft.com...
> >> >I have the following short VFP8 program:
> >> >
> >> > one_cust = '1234'
> >> > CREATE SQL VIEW cust_view Remote CONNECTION AS400 AS ;
> >> > SELECT cust_code,cust_name;
> >> > FROM customer;
> >> > WHERE cust_code =one_cust
> >> > /Memo: customer is an IBM AS400 file/
> >> > If I run this program I get an error message: "ONE_CUST" is not part of the
> >> > table.
> >> > If I use a constans /....WHERE cust_code = '1234' the program works well.
> >> > My question is : Is it possible to use a variable or not? Where did I make a
> >> > mistake?
> >> > Another question: How can I create a LOGICAL calculated field in this SELECT
> >> > command?
> >> > Could anyone help me?
> >> > Thanks in advance.
> >> > --
> >> > Sandor
> >>
> >>
>
>

Stefan
Thu Dec 02 03:31:45 CST 2004


When your previous code is part of a form method, you'd
re-create the view over and over again. A "typical" way would be
to do so once, at designtime only.
Not sure though, why that makes your "AS400" connection running
out of resources...

Another approach can be SPT / SQL PassThrough syntax
* MS SqlServer2000 example:
Local cConnString, h, n, one_cust
cConnString = ;
[DRIVER=SQL Server;] + ;
[DATABASE=_test;] + ;
[APP=My Application;] + ;
[SERVER=(local)]

h = SQLSTRINGCONNECT(m.cConnstring)

IF h > 0
cSQL = [SELECT cust_code,cust_name FROM customer WHERE cust_code = ?one_cust]
n = SQLEXEC(h,cSQL)

IF n < 0
&& no result
ELSE
Browse
ENDIF

SQLDISCONNECT(h)
Else
&& no connection
Endif
* eop


hth
-Stefan

"Sandor Hajdu" <SandorHajdu@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C0AD0BB-196D-429F-B467-347B73768F50@microsoft.com...
> Stefan,
>
> I think I need more help.
> The short program is part of a form. If I run the form for the first time no
> problem, everything is OK. If I chage the parameter and run again /I do not
> release the form/
> I got the following error:
> Driver is probably out of resources.
> Could you tell me why I get this message? What's wrong in my program?
> /I tried to close the connection and database and open again - what I do
> when I start the form - but I have the same message./
>
> Sandor
>
<snip>