SQL Insert from other table

[Mulyadi]

Hi all

I have a problem with running SQL with ADO RecordSet.

I have 2 table with same structure, 1 table to temporary use.
( Table1 and Table2 ) structure:
KODE VARCHAR2(5)
KET VARCHAR2(30)

Table1 data:
--------------------
KODE KET
001 AAAAAAAA
002 BBBBBBBB
003 CCCCCCCC

Table2 data: is empty


if i want to add record from table1 to table2, than error

insert into table2 ( select * from table1 ); --> error

but, i do it with long statemen, the result is ok.
ex: insert into TABLE2 (kode,ket) values ('001','test'); --> ok.

this is my code:

Code: Select all  Expand view

#include "fivewin.ch"

func main
  local oCon, oRes

  oCon := CREATEOBJECT( "ADODB.Connection" )

  TRY
    oCon:Open( cConnStr )
  CATCH
    MsgInfo( "Error in Opening connection to Oracle" )
    RETURN(.F.)
  END TRY

  Try

    // cSql := "insert into table2 ( select * from table1 where kode='001' )"   // result: error
    // or
    // cSql := "insert into table2 ( select * from table1 )"                            // result: error
   
    cSql  := "insert into TABLE2 (kode,ket) values ('001','test')"    //  result: ok.

    oRes  := FW_OpenRecordSet( oCon, cSQL )
    if oRes = NIL
       msginfo("Error")
    endif

  Catch
    MsgInfo( "Statement error" )
    RETURN(.F.)
  End Try

return nil
// eof
 

somebody can help me..?



Mulyadi

[Enrico Maria Giordano]

insert into table2 ( select * from table1 ); --> error

I never used it. Are you sure it is a correct syntax? Please try it in a SQL tool.

EMG

[Mulyadi]

Halo, Enrico..
Thanks for reply.

Yes, I try in sql tool with Oracle SQL Developer its fine..
and, if I use this syntax in VS2010 is not problem too..

Why ... or this a ADO problem...?

[Enrico Maria Giordano]

I don't know, sorry.

EMG

[Mulyadi]

Oke, thanks Enrico...
maybe other person can give me a solution..

Mulyadi.

[Mulyadi]

Halo.... any body help me...?
.
.

[Rick Lipkin]

It appears you have opened the connection but are mis matching connections with a recordset .. Change this line from

Code: Select all  Expand view

oRes  := FW_OpenRecordSet( oCon, cSQL )
 

To this

Code: Select all  Expand view

oCn:Execute( cSQL )
 

Once you have inserted your records, then create your recordset and view the results.

Rick Lipkin

[Mulyadi]

Hi,
Thanks Mr. Rick Lipkin.

the solution is works fine.

but I dont understand because sometime in the other case i using FW_OpenRecordSet
and to now it not working and then must be using :Execute
how difference method the two of them..?

Mulyadi.

[Rick Lipkin]

When you create a connection

Code: Select all  Expand view

oCon := CREATEOBJECT( "ADODB.Connection" )

TRY
    oCon:Open( cConnStr )
CATCH
    MsgInfo( "Error in Opening connection to Oracle" )
    RETURN(.F.)
END TRY

 

It allows you to use SQL Commands such as INSERT,UPDATE,etc ( globally ) on a table.

A Recordset is a 'fetch' of rows with data that can be viewed or manipulated based on a "SELECT" statement

Code: Select all  Expand view

oRes  := FW_OpenRecordSet( oCon, cSQL )
xBrowse( oRes )
 

Hope that helped.

Rick Lipkin

[Mulyadi]

Oke Mr..
Thanks for the explanation..
now I understand how must be do so..



Mulyadi.