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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.