Author Topic: SQL/DBExpert  (Read 701 times)

Per E. Johannessen

  • Full Member
  • ***
  • Thank You
  • -Given: 3
  • -Receive: 0
  • Posts: 101
    • View Profile
SQL/DBExpert
« on: October 24, 2017, 10:09:17 pm »
Hi,

Anyone who knows how to use "INSERT INTO" in a DBExpert macro?

Trying to achieve this:

SUM = 10000
dbeRunSQL(INSERT INTO TABLENAME(TABLEFIELD)) SUM

The SUM I actually get from a form and I need the macro to run from this form.

(I've tried moving/adding/deleting " ( ",  " ) ", TOTAL and " ' " in all the positions I can think of but no success.)

Running INSERT in the query designer works, but there it gets the value to insert from a table like;
INSERT INTO TABLEA(FIELDA) SELECT TABLEB.FIELDB FROM TABLEB

Ideas welcome,
Per



Greg Pringle

  • Jr. Member
  • **
  • Thank You
  • -Given: 1
  • -Receive: 2
  • Posts: 79
    • View Profile
Re: SQL/DBExpert
« Reply #1 on: October 24, 2017, 10:31:04 pm »
It is unclear what you are trying to do.
First, I have never used DBExpert but have used many other SQL engines.

INSERT can be used to create new rows in a database but cannot alter existing rows.

Your description sounds like you are trying to update a field in a row of a database.

That is done with the ALTER statement.

Dariusz Piatkowski

  • Sr. Member
  • ****
  • Thank You
  • -Given: 12
  • -Receive: 6
  • Posts: 290
    • View Profile
Re: SQL/DBExpert
« Reply #2 on: October 25, 2017, 12:15:32 am »
Are you simply missing the 'VALUES' expression?

So my familiarity with SQL comes from Oracle enterprise dbms. In the example you provided below, the values come from another table, so the parser figures this out on it's own.

Here is a link to the Oracle docs on this specific subject => https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems025.htm

Beyond the above syntax specific comment, I am thinking the following: your statement reads like the SUM is outside the 'INSERT' expression itself:

dbeRunSQL (INSERT INTO TABLENAME(TABLEFIELD)) SUM

I would re-write it as:

dbeRunSQL(INSERT INTO TABLENAME(TABLEFIELD) SUM)

Per E. Johannessen

  • Full Member
  • ***
  • Thank You
  • -Given: 3
  • -Receive: 0
  • Posts: 101
    • View Profile
Re: SQL/DBExpert
« Reply #3 on: October 25, 2017, 03:02:31 am »
I'm trying to create a new row/record.
(For updating I use the command UPDATE which works fine from a macro.)

Already tried and it does not work

dbeRunSQL(INSERT INTO TABLENAME(TABLEFIELD) SUM)

Also tested with 'VALUES' expression.


roberto

  • Sr. Member
  • ****
  • Thank You
  • -Given: 13
  • -Receive: 6
  • Posts: 313
    • View Profile
Re: SQL/DBExpert
« Reply #4 on: October 25, 2017, 11:27:52 am »
These are two valid examples that are running.
The first is a string with sum.
The second is a dadena with insert.

SELECT QAPTS1Y3X.ASIEN, SUM(QAPTS1Y3X.DB), SUM(QAPTS1Y3X.HB)
FROM QAPTS1Y3X
GROUP BY QAPTS1Y3X.ASIEN


CAD='INSERT INTO TPSUFORPRINT (LOSVAL,ACT3, ACT1, CL10, ORDEN, ACL5) SELECT',
   '  '"'"LOSVALCAL"'"' AS VAL7, '"'"ACT3R"'"' AS VAL1, '"'"ACT1R"'"' AS VAL2, '"'"ACT3R"'"' AS VAL3, 1 AS VAL4, '"'"ACT1R"'"' AS VAL5 FROM CATALIZA'
RC=DBERUNSQL(CAD)
In the chain with insert, note that says--- from cataliza-----
Cataliza is a table created only to execute the insert command. For all the insert comands in your sqls.
I have this table created as two columns of text of 254 characters, without indices, but I think this is the least.
I work with DBF tables.
Saludos

Per E. Johannessen

  • Full Member
  • ***
  • Thank You
  • -Given: 3
  • -Receive: 0
  • Posts: 101
    • View Profile
Re: SQL/DBExpert
« Reply #5 on: October 25, 2017, 04:15:43 pm »
The sum I get from a form so I'm not sure what you mean with the first example.

DBE crashes when running the second example, although when running it as "Single step" all seems fine until it actually runs RC=DBERUNSQL(CAD).

roberto

  • Sr. Member
  • ****
  • Thank You
  • -Given: 13
  • -Receive: 6
  • Posts: 313
    • View Profile
Re: SQL/DBExpert
« Reply #6 on: October 25, 2017, 04:48:54 pm »
The sum I get from a form so I'm not sure what you mean with the first example.
It was just an example of a query with a sum
Quote
DBE crashes when running the second example, although when running it as "Single step" all seems fine until it actually runs RC=DBERUNSQL(CAD).
You can put the string that makes you break the dbexpert.?
Saludos

Per E. Johannessen

  • Full Member
  • ***
  • Thank You
  • -Given: 3
  • -Receive: 0
  • Posts: 101
    • View Profile
Re: SQL/DBExpert
« Reply #7 on: October 25, 2017, 05:40:24 pm »
I created the tables and used exactly the same code as you posted, (copy&paste).

CAD='INSERT INTO TPSUFORPRINT (LOSVAL,ACT3, ACT1, CL10, ORDEN, ACL5) SELECT',
   '  '"'"LOSVALCAL"'"' AS VAL7, '"'"ACT3R"'"' AS VAL1, '"'"ACT1R"'"' AS VAL2, '"'"ACT3R"'"' AS VAL3, 1 AS VAL4, '"'"ACT1R"'"' AS VAL5 FROM CATALIZA'
RC=DBERUNSQL(CAD)

roberto

  • Sr. Member
  • ****
  • Thank You
  • -Given: 13
  • -Receive: 6
  • Posts: 313
    • View Profile
Re: SQL/DBExpert
« Reply #8 on: October 25, 2017, 06:49:10 pm »
The string that I put on was for you to have an example of a syntax that is valid and works. But you must adjust to your needs and your tables.
With your example, it would be something similar to:

SUM = 10000
CAD='INSERT INTO Tablename (tablefield) SELECT',
   '  '"'"sum"'"' AS SUMA FROM CATALIZA'
dbeRunSQL(CAD)

But I can not say for sure.

Per E. Johannessen

  • Full Member
  • ***
  • Thank You
  • -Given: 3
  • -Receive: 0
  • Posts: 101
    • View Profile
Re: SQL/DBExpert
« Reply #9 on: October 25, 2017, 08:40:07 pm »
For testing purposes I created tables/fields/values to be used with your code. When/if I get it to work I will of course
do the necessary adjustments. Somehow I now get the macro to run without an error but it does not insert the values to the table.
Makes me wonder if the crashes I had has caused some unwanted changes somewhere, think I'll reinstall DBE and make a new app.
 

Per E. Johannessen

  • Full Member
  • ***
  • Thank You
  • -Given: 3
  • -Receive: 0
  • Posts: 101
    • View Profile
Re: SQL/DBExpert
« Reply #10 on: October 25, 2017, 09:08:06 pm »
Created a new app and it now works. (Didn't have to reinstall DBE.)

Thanks a lot!