Source language: Translate to:

Understanding dbpExecSQL results table

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Understanding dbpExecSQL results table

Postby BRobinsonS » Tue Jun 04, 2013 10:55 am

Using a Test App.
After researching SQL, I believe I have to use INNER JOIN to get my results.
However, I am having trouble dumping the results into a table for reporting on.

The help file example uses a Temp1 table, but Help says you can use the name of an existing table.

When I do this, nothing gets dumped into the existing table and the Rectangle object suggest dpbExecSQL has created a temporary table for displaying the results. Code below:

dbpExecSQL "TestSQL" "Select Contacts.LastName,DuesPaid.DatePaid|FROM Contacts|INNER JOIN DuesPaid |ON Contacts.MemNum = DuesPaid.MemNum|ORDER BY Contacts.LastName" "DuesbyName"
dbpOpenTable "TestSQL" "DuesbyName" ""
dbpShowGrid "TestSQL" "DuesbyName" "Rectangle3"


There was a question on a similar post why | was there. This is generated by NeoBookDB Pro.
I guess to separate the statements.

I built the DuesbyName table to match the fields I want to get from the SQL statement.
Hoping to report from combine tables by SQL to get dues paid (DuesPaid table) by members (Contacts table) in alphabetical order between two dates e.g. 3/1/2013 and 3/31/2013.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Understanding dbpExecSQL results table

Postby Neosoft Support » Tue Jun 04, 2013 2:32 pm

The help file example uses a Temp1 table, but Help says you can use the name of an existing table.


If the results table already exists, it must contain the same fields as the SQL select statement. If they are different it won't work and if you have errors turned off you won't know it. Try using a temporary results table like "temp1" instead.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Understanding dbpExecSQL results table

Postby BRobinsonS » Tue Jun 04, 2013 5:24 pm

If the results table already exists, it must contain the same fields as the SQL select statement.

Tried 'Temp1' and I get the results I am looking for. Does the table order matter?

I have matched the fields in the existing table to the sql result table and in the same order as the table.
Comes up looking like it works (in the Rectangle object), but the table itself comes up empty.
Also, the attempt seems to put a blank record in the original tables sometimes. :?:
Might be a hint of what is not working here.

They are:
MemNum String(12),MemType String(5),DatePaid DateTime,AmtPaid Currency,ReceiptNo String(15),FirstName String(25),LastName String(25)


if you have errors turned off you won't know it

Where do I look for this?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Understanding dbpExecSQL results table

Postby Neosoft Support » Wed Jun 05, 2013 10:45 am

It's the database engine that controls how the query results are generated. It may be that because the query draws information from two different tables that the results can only be displayed properly in a temporary table. The database engine needs to create the table itself to insure that the structure is correct. Also, if the results table already contained data, then you may need to clear it in order for the query to insert the new data correctly. However, I think for the type of query you're doing, a temporary table is what you want anyway.

The DBPro action ShowErrors can be used to turn error messages on or off. Error messages are on by default.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Understanding dbpExecSQL results table

Postby BRobinsonS » Wed Jun 05, 2013 5:18 pm

Then, the question comes up. How do I do a report on Temporary Table?
Can I create a report if I know the resulting fields in the Temporary Table? (seems to me I read this elsewhere)
And How do I address the table? Is it Database.temp1 ?

Thoughts come to mind. Can I copy the Temporary table rows to an active table (matching the Temporary Table) in the database so I can create/run a report?

By the way, the table I was trying to populate was empty.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Understanding dbpExecSQL results table

Postby dpayer » Thu Jun 06, 2013 7:36 am

BRobinsonS wrote:Then, the question comes up. How do I do a report on Temporary Table?
Can I create a report if I know the resulting fields in the Temporary Table? (seems to me I read this elsewhere)
And How do I address the table? Is it Database.temp1 ?

Thoughts come to mind. Can I copy the Temporary table rows to an active table (matching the Temporary Table) in the database so I can create/run a report?

By the way, the table I was trying to populate was empty.



You are asking some things that differ according to the type of database server you are using. For example, Access uses the DBO engine that is built into windows and it handles things different than the MS SQL server.

If you want to make a new table based on the results of a query from existing tables, look at the SELECT INTO command. (not sure if this exists in Access DB but ask Mr. Google on this, he is your friend). SELECT INTO allows you to take an existing query and import it into a new table (it creates the table first).

Do take time to look at some SQL tutorial sites for help. There is enough similarity among the differing SQL engines that such sites are useful but they often will help you chart a course. They also give you the vocabulary that will help you create effective searches for your questions.

I spend a lot of times on the w3schools.com site:
http://www.w3schools.com/sql/default.asp

Best wishes.

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: Understanding dbpExecSQL results table

Postby BRobinsonS » Tue Jun 11, 2013 9:20 am

Back to working on SQL and the report I require.

I am working on using the Temp table and copying the records I need to a regular table with same fields by using a LOOP.
dbpOpenTable "TestSQL" "DuesbyName" ""
dbpEmptyTable "TestSQL" "DuesbyName"
Loop "1" "[TestSQL.Temp1.$RecCount]" "[Counter]"
dbpAddRecord "TestSQL" "DuesbyName"
SetVar "[TestSQL.DuesbyName.MemNum]" "[TestSQL.Temp1.MemNum]"
SetVar "[TestSQL.DuesbyName.MemType]" "[TestSQL.Temp1.Memtype]"
SetVar "[TestSQL.DuesbyName.DatePaid]" "[TestSQL.Temp1.DatePaid]"
SetVar "[TestSQL.DuesbyName.AmtPaid]" "[TestSQL.Temp1.AmtPaid]"
SetVar "[TestSQL.DuesbyName.ReceiptNo]" "[TestSQL.Temp1.ReceiptNo]"
SetVar "[TestSQL.DuesbyName.FirstName]" "[TestSQL.Temp1.FirstName]"
SetVar "[TestSQL.DuesbyName.LastName]" "[TestSQL.Temp1.LastName]"
dbpNext "TestSQL" "Temp1"
EndLoop


Works except doesn't copy the date field to the regular table date field both named DatePaid.
I have looked for a typo but I don't see one.

Even, tried FORMAT(Temp1.DatePaid,'mm/dd/yyyy') as DatePaid when creating the Temp file.
Again, doesn't copy DatePaid field.

Suggestions?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Understanding dbpExecSQL results table

Postby Neosoft Support » Tue Jun 11, 2013 10:14 am

Try this:

SetVar "[TestSQL.DuesbyName.DatePaid]" "![TestSQL.Temp1.DatePaid]"

SetVar may be interpreting the date as a mathematical formula. The "!" tells it not to.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Understanding dbpExecSQL results table

Postby BRobinsonS » Tue Jun 11, 2013 11:22 am

Bingo ! :P
That did it.

Thank you.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest