Source language: Translate to:

use "[" and "]" in dbpExecSQL

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

use "[" and "]" in dbpExecSQL

Postby michi » Fri Sep 19, 2014 8:52 am

Hi all, I've another problem with dbpExecSQL.
I must create a query with MSAccess where there are some field determinated by expression.

For example:

FieldX
FieldY

FieldZ is determinated by "FIELD X" - "FIELD Y"

SQL basic code is:

SELECT FieldX, FieldY, [FieldX]-[FieldY] AS FieldZ
[...]

but now Neobook think that [FieldX] is a publication variable and on publication run doesn't execute the query but show error message.

Someone could help me to solve this trouble?

thanks in advance guys
Michi

Started to use Neobook in 2003 when I was 12 to make interactive books. Coders saw me and smiled.
Nowadays I'm still using Neobook to create advanced and powerful softwares. Coders see me and say: «how did you make that?»
michi
 
Posts: 33
Joined: Wed Jun 25, 2014 3:01 am
Location: Italy

Re: use "[" and "]" in dbpExecSQL

Postby Gaev » Fri Sep 19, 2014 9:40 am

michi:

I haven't verified this myself within the context of an SQL statement being passed to the database engine ... but in keeping with the general workaround elsewhere in NeoBook, try ...
Code: Select all
SELECT FieldX, FieldY, [#91]FieldX[#93]-[#91]FieldY[#93] AS FieldZ
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: use "[" and "]" in dbpExecSQL

Postby michi » Sun Sep 21, 2014 4:32 am

Hi Gaev and thanks for your reply.
I've tried but it doesn't work.

I've tried in MSAccess to obmit [] so...

SELECT FieldX-FieldY AS FieldZ

and works.

In neobook dbpExecSQL go on error "Cannot perform this operation on a closed dataset".

SQL code works until I use FieldX-FieldY AS FieldZ statement.

I'm going crazy
Michi

Started to use Neobook in 2003 when I was 12 to make interactive books. Coders saw me and smiled.
Nowadays I'm still using Neobook to create advanced and powerful softwares. Coders see me and say: «how did you make that?»
michi
 
Posts: 33
Joined: Wed Jun 25, 2014 3:01 am
Location: Italy

Re: use "[" and "]" in dbpExecSQL

Postby michi » Sun Sep 21, 2014 4:59 am

This is the code:

Code: Select all
SELECT Sum(Warehouse.IN) AS IN, Sum(Warehouse.OUT) AS OUT, Warehouse.ProductCode, IN-OUT AS STOCK
FROM Warehouse
GROUP BY Warehouse.ProductCode;


Neobook reply "Cannot perform this operation on a closed dataset"

I can't understand why Neobook reject this basic code.

If you obmit IN-OUT AS STOCK all works fine.
Michi

Started to use Neobook in 2003 when I was 12 to make interactive books. Coders saw me and smiled.
Nowadays I'm still using Neobook to create advanced and powerful softwares. Coders see me and say: «how did you make that?»
michi
 
Posts: 33
Joined: Wed Jun 25, 2014 3:01 am
Location: Italy

Re: use "[" and "]" in dbpExecSQL

Postby Gaev » Sun Sep 21, 2014 5:59 am

michi:

Neobook reply "Cannot perform this operation on a closed dataset"

Try ...
Code: Select all
dbpOpenAccessDatabase "database id" "file name" "options"
dbpOpenTable "database id" "Warehouse" ""
... before doing the dbpExecSQL command

I can't understand why Neobook reject this basic code.
If you obmit IN-OUT AS STOCK all works fine.

Try and place the expression (IN-OUT) within brackets ... like so ...
Code: Select all
SELECT Sum(Warehouse.IN) AS IN, Sum(Warehouse.OUT) AS OUT, Warehouse.ProductCode, (IN-OUT) AS STOCK
FROM Warehouse
GROUP BY Warehouse.ProductCode;
... or if you want to be consistent, specify the Table name for each of the fields in the expression ... like so ...
Code: Select all
SELECT Sum(Warehouse.IN) AS IN, Sum(Warehouse.OUT) AS OUT, Warehouse.ProductCode, (Warehouse.IN-Warehouse.OUT) AS STOCK
FROM Warehouse
GROUP BY Warehouse.ProductCode;
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: use "[" and "]" in dbpExecSQL

Postby michi » Sun Sep 21, 2014 9:18 am

Hi Gaev.

Database is opened, and tables too.

I've tried to use brackets without success.

Same error: "cannot perform this operation on a closed dataset"

So I try to not specify temporary result table in dbpExecSQL action... and error message now change:

"Impossibile eseguire una query che non include l'espressione "IN-OUT" specificata come parte di una funzione di aggregazione"

in english can be: Impossible to execute a query that not include expression "IN-OUT" specified as a part of aggregation function

code is the same

Code: Select all
SELECT Sum(Warehouse.IN) AS IN, Sum(Warehouse.OUT) AS OUT, Warehouse.ProductCode, (IN-OUT) AS STOCK
FROM Warehouse
GROUP BY Warehouse.ProductCode;


(IN-OUT) AS STOCK generates the problem. I can't use warehouse.in - warehouse.out as STOCK as you suggest, because IN AND OUT are sum, not single records.

:?: :?: :?:
Michi

Started to use Neobook in 2003 when I was 12 to make interactive books. Coders saw me and smiled.
Nowadays I'm still using Neobook to create advanced and powerful softwares. Coders see me and say: «how did you make that?»
michi
 
Posts: 33
Joined: Wed Jun 25, 2014 3:01 am
Location: Italy

Re: use "[" and "]" in dbpExecSQL

Postby michi » Sun Sep 21, 2014 9:33 am

:D now works.

I changed name of generated field.

before I used sum of warehouse.IN as IN. Maybe go on error because the engine consider the IN (the sum) as warehouse.IN.

Changing sum field name command is OK!!!
Michi

Started to use Neobook in 2003 when I was 12 to make interactive books. Coders saw me and smiled.
Nowadays I'm still using Neobook to create advanced and powerful softwares. Coders see me and say: «how did you make that?»
michi
 
Posts: 33
Joined: Wed Jun 25, 2014 3:01 am
Location: Italy

Re: use "[" and "]" in dbpExecSQL

Postby Gaev » Sun Sep 21, 2014 11:01 am

michi:

before I used sum of warehouse.IN as IN. Maybe go on error because the engine consider the IN (the sum) as warehouse.IN.
Changing sum field name command is OK!!!

See list of reserved words in MS Access ... IN is one of them ... never use Reserved Words as Field Names.

However, I wonder how you were previously able to achieve this before ...
If you obmit IN-OUT AS STOCK all works fine.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: use "[" and "]" in dbpExecSQL

Postby michi » Mon Sep 22, 2014 12:16 am

it was to explain that error was generated when I add this part of code.

Finally I solve the problem. Thanks for your support!

Michi
Michi

Started to use Neobook in 2003 when I was 12 to make interactive books. Coders saw me and smiled.
Nowadays I'm still using Neobook to create advanced and powerful softwares. Coders see me and say: «how did you make that?»
michi
 
Posts: 33
Joined: Wed Jun 25, 2014 3:01 am
Location: Italy


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron