save querie??

As I can keep the result from a querie? my Base of data I need the registries with date for example of 1998 and the result I need to keep it like DB1998.DB

Consult:='Select * from correspondence where date like upper('+chr(39)+'%1998'%'+chr(39)+ ') ';
Query.Close;
Query.SQL.text:=consult;
Query.Open;

as I can keep the result?

Help me!!!
jara_vlaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kretzschmarConnect With a Mentor Commented:
hmm  jara_vla,

a query never has an index, like saved with a create table sql-statement nor with the batchmove method.

well, what you can do is to create an empty destination-table with the structure of the source table and then batappend instead of batcopy like

  ....
  Query1.Open;
  Table1.DatabaseName := query1.Databasename;
  Table1.Tablename := 'Tmp.DB'; //or whatever name
  Try
    Table1.DeleteTable;  //be sure that the table not exist
  except
    //there was no table or another error
  end;
  Table1.DatabaseName := query1.Databasename;
  Table1.Tablename := 'Tmp.DB'; //or whatever name
  //table2 is the sourcetable
  Table2.FieldDefs.Update;      //Retrieve Fieldinformation
  Table1.FieldDefs.Assign(Table2.FieldDefs);
  Table2.IndexDefs.Update;      //Retrieve Indexinformation
  Table1.IndexDefs.Assign(table2.IndexDefs);
  try
    Table1.CreateTable;
    I := Table1.BatchMove(query1,BatAppend);
    Showmessage(IntToStr(I)+' Query Records Saved');
  except
    //an error
  end;


or create the index after the copy with the addindex-method

meikl ;-)


0
 
jpussacqCommented:
To retrieve de first column of the querie, use:

result := Query.Fields[0].AsString;

or use the name of de column:

result := Query.FieldByName('Column_Name').AsString;
0
 
jara_vlaAuthor Commented:
I need to keep multiple registries reason why I need to keep the result from all the querie like a new table DB1998.DB

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
jpussacqCommented:
Consult:='INSERT INTO Table_Name
               Select * from correspondence where date like
               upper('+chr(39)+'%1998'%'+chr(39)+ ') ';
Query.SQL.text:=consult;
Query.ExceSQL;
0
 
jara_vlaAuthor Commented:
but I want to create the table with the result of the querie reason why I cannot insert in Table_name since it does not even exist :(
0
 
jpussacqCommented:
Consult:='CREATE TABLE Table_Name
                                    Select * from correspondence where date like
                                    upper('+chr(39)+'%1998'%'+chr(39)+ ') ';
                     Query.SQL.text:=consult;
                     Query.ExceSQL;
0
 
jara_vlaAuthor Commented:
in
Consult:='CREATE TABLE respald.db Select * from correspondencia'+
         ' where fecharec like upper('+chr(39)+'%1998%'+chr(39)+ ')';
Query.SQL.text:=consult;
Query.ExecSQL;


message
'invalid use of keyword.
Token: select
Line number:1'
??? what is wrong??
0
 
kretzschmarCommented:
hi jara_vla,

Consult:='CREATE TABLE Table_Name As '+  //look at the As here
              'Select * from correspondence where date '+
              'like '+
              'upper('+chr(39)+'%1998'%'+chr(39)+ ') '; Query.SQL.text:=consult;
Query.ExceSQL;

if this not work, then tell me what database you use.
(paradox i guess)

if you use paradox then take a look to the batchmove-method

Consult:='Select * from correspondence where date '+
              'like '+
              'upper('+chr(39)+'%1998'%'+chr(39)+ ') '; Query.SQL.text:=consult;
Query.ExceSQL;
//you need a table component
Table1.DatabaseName := query1.Databasename;
Table1.Tablename := 'Tmp.DB'; //or whatever name
try
  I := Table1.BatchMove(query1,BatCopy);
  Showmessage(IntStr(I)+' Query Records Saved');
except
  //an error
end;


meikl ;-)
0
 
kretzschmarCommented:
oops, sorry some typos

in the second sample use
Query.Open;
instead of
Query.ExceSQL;

and instead of
query1
use
query

meikl ;-)
0
 
jara_vlaAuthor Commented:
thank you very much to kretzschmar! batchmove-method works perfectly with a small problem! it does not index the result :( I need that the result of the querie keeps in another table and that is indexed with the same field that the original one! it is possible to be done?



0
 
jara_vlaAuthor Commented:
Adjusted points to 30
0
 
kretzschmarCommented:
hello ?
0
 
kretzschmarCommented:
jara_vla are you still alive ?
0
 
jara_vlaAuthor Commented:
Very very thanks! kretzschmar  :)
0
 
jara_vlaAuthor Commented:
mmm,when I erase of a table registries with

delete from correspondencia.db where numfolio like '%-003%'

these registries disappears of the table but the size of the file is not reduced! that this badly?


0
 
kretzschmarCommented:
hi jara_vla,

after deleeing you must pack the table
here a sample from borland
you must placed the bde-unit in the uses clause

/ Pack a Paradox or dBASE table
     // The table must be opened execlusively before calling this function...
     procedure PackTable(Table: TTable);
     var
       Props: CURProps;
       hDb: hDBIDb;
       TableDesc: CRTblDesc;

     begin
       // Make sure the table is open exclusively so we can get the db handle...
       if Table.Active = False then
         raise EDatabaseError.Create('Table must be opened to pack');
       if Table.Exclusive = False then
         raise EDatabaseError.Create('Table must be opened exclusively to pack');

       // Get the table properties to determine table type...
       Check(DbiGetCursorProps(Table.Handle, Props));

       // If the table is a Paradox table, you must call DbiDoRestructure...
       if Props.szTableType = szPARADOX then
       begin
         // Blank out the structure...
         FillChar(TableDesc, sizeof(TableDesc), 0);
         //  Get the database handle from the table's cursor handle...
         Check(DbiGetObjFromObj(hDBIObj(Table.Handle), objDATABASE, hDBIObj(hDb)));
         // Put the table name in the table descriptor...
         StrPCopy(TableDesc.szTblName, Table.TableName);
         // Put the table type in the table descriptor...
         StrPCopy(TableDesc.szTblType, Props.szTableType);
         // Set the Pack option in the table descriptor to TRUE...
         TableDesc.bPack := True;
         // Close the table so the restructure can complete...
         Table.Close;
         // Call DbiDoRestructure...
         Check(DbiDoRestructure(hDb, 1, @TableDesc, nil, nil, nil, FALSE));
       end
       else
         // If the table is a dBASE table, simply call DbiPackTable...
         if Props.szTableType = szDBASE then
           Check(DbiPackTable(Table.DBHandle, Table.Handle, nil, szDBASE, TRUE))
         else
           // Pack only works on PAradox or dBASE; nothing else...
           raise EDatabaseError.Create('Table must be either of Paradox or dBASE ' +
                    'type to pack');

       Table.Open;
     end;

meikl
0
 
jara_vlaAuthor Commented:

it is necessary to pack the table so that the space occupied by the erased registries is released? or entendi badly? there is no another form?
0
All Courses

From novice to tech pro — start learning today.