[firebird-support] Re: Is there an easy way for input rows from a script file?

[hv...@users.sourceforge.net [firebird-support]]

---In firebird-support@yahoogroups.com,  wrote :
 
Hello everybody
 

 If I connect to a database "Database1" using ISQL, I can write something like:
 

 OUTPUT MyFile.DAT;
 SELECT * FROM MyTable;
 OUTPUT;
 

 And all the rows of "MyTable" will go to the text file "MyFile.DAT". That's ok 
and works fine.
 

 But now, I want to connect to "Database2", which also have a table called 
"MyTable" and with the same structure.
 

 After that, I want to insert into "MyTable" (of "Database2") the rows 
contained in "MyFile.DAT"
 

 How can I do such thing without a lot of effort writing an INSERT command in 
each line of "MyFile.DAT"?

If you need to export some data from one Firebird database to another Firebird 
database, use EXECUTE STATEMENT ... ON EXTERNAL and forget about files.

Regards,
Vlad

 

Re: [firebird-support] Is there an easy way for input rows from a script file?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you, Sean

Greetings.

Walter.


On Wed, Feb 15, 2017 at 6:43 PM, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support]  wrote:

>
>
> Walter,
>
> > If I connect to a database "Database1" using ISQL, I can write something
> like:
> >
> > OUTPUT MyFile.DAT;
> > SELECT * FROM MyTable;
> > OUTPUT;
> >
> > And all the rows of "MyTable" will go to the text file "MyFile.DAT".
> That's ok
> > and works fine.
> >
> > But now, I want to connect to "Database2", which also have a table
> called
> > "MyTable" and with the same structure.
> >
> > After that, I want to insert into "MyTable" (of "Database2") the rows
> > contained in "MyFile.DAT"
> >
> > How can I do such thing without a lot of effort writing an INSERT
> command in
> > each line of "MyFile.DAT"?
>
> The Firebird ISQL tool does not support such a feature.
>
> You could create 2 output files and then use command line utility to
> combine results into single file.
>
> There are some third-party tools which could allow you to script this
> process (I know that IBExpert supports it)
>
>
> Sean
> 
>

Re: [firebird-support] Is there an easy way for input rows from a script file?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much Set, I will try your advice.

Off topic: can you say me what the letters HTH means?

Greetings.

Walter.


On Wed, Feb 15, 2017 at 6:31 PM, setysvar setys...@gmail.com
[firebird-support]  wrote:

>
>
> Den 15.02.2017 17:07, skrev 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support]:
>
> Hello everybody
>
> If I connect to a database "Database1" using ISQL, I can write something
> like:
>
> OUTPUT MyFile.DAT;
> SELECT * FROM MyTable;
> OUTPUT;
>
> And all the rows of "MyTable" will go to the text file "MyFile.DAT".
> That's ok and works fine.
> But now, I want to connect to "Database2", which also have a table called
> "MyTable" and with the same structure.
> After that, I want to insert into "MyTable" (of "Database2") the rows
> contained in "MyFile.DAT"
> How can I do such thing without a lot of effort writing an INSERT command
> in each line of "MyFile.DAT"?
>
> Greetings.
> Walter.
>
>
> Hi Walter. I'm not directly answering your question (mainly due to not
> using isql myself, so I don't know the answer), but I can think of two
> alternative ways.
>
> (1) (the more standard answer) Make sure your output file is in a fixed
> length format, then create an external table for this file, transfer the
> data using INSERT INTO "MyTable" SELECT * FROM
>  and then finally drop the external table.
>
> (2) Use qli rather than isql to transfer data directly using something
> similar to this recipe:
> https://www.ibphoenix.com/resources/documents/how_to/doc_42
> qli is an ancient part of InterBase, probably used before SQL was
> invented, you can find it in the same directory as isql.
>
> I've only used qli once many years ago (probably on Fb 0.9.4 or 1.5), but
> it worked nicely when I needed it. Though I don't think qli has been
> updated for the last few Firebird versions, so maybe it doesn't work
> anymore. And you may have a hard time finding information about it beyond
> the document referred to above. Though I suppose:
>
> ready "Database1" as src;
> ready "Database2" as trg;
> trg."MyTable" = src."MyTable";
>
> doesn't need much explanation and should be simple for you to try on a
> test database. I expect it to replace the target rather than add to it
> (though I don't know), but the one time I used qli, I think I was
> transferring to an empty table.
>
> HTH,
> Set
>
> 
>

RE: [firebird-support] Is there an easy way for input rows from a script file?

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Walter,

> If I connect to a database "Database1" using ISQL, I can write something like:
> 
> OUTPUT MyFile.DAT;
> SELECT * FROM MyTable;
> OUTPUT;
> 
> And all the rows of "MyTable" will go to the text file "MyFile.DAT". That's ok
> and works fine.
> 
> But now, I want to connect to "Database2", which also have a table called
> "MyTable" and with the same structure.
> 
> After that, I want to insert into "MyTable" (of "Database2") the rows
> contained in "MyFile.DAT"
> 
> How can I do such thing without a lot of effort writing an INSERT command in
> each line of "MyFile.DAT"?

The Firebird ISQL tool does not support such a feature.

You could create 2 output files and then use command line utility to combine 
results into single file.

There are some third-party tools which could allow you to script this process 
(I know that IBExpert supports it)


Sean

[firebird-support] Is there an easy way for input rows from a script file?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

If I connect to a database "Database1" using ISQL, I can write something
like:

OUTPUT MyFile.DAT;
SELECT * FROM MyTable;
OUTPUT;

And all the rows of "MyTable" will go to the text file "MyFile.DAT". That's
ok and works fine.

But now, I want to connect to "Database2", which also have a table called
"MyTable" and with the same structure.

After that, I want to insert into "MyTable" (of "Database2") the rows
contained in "MyFile.DAT"

How can I do such thing without a lot of effort writing an INSERT command
in each line of "MyFile.DAT"?

Greetings.

Walter.

RE: [firebird-support] FIREBIRD PIVOT function or Matrix

['Stef' s...@autotech.co.za [firebird-support]]

Thanks Mark 

 

Agreed the PIVOT feature is wonderful. However I am stuck with the customer 
using Firebird(no complaints). But I still need a solution as to how go ahead? 

 

Please any suggestions or guidance, seeing as you have done this already?

 

Stef van der Merwe

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 15 February 2017 11:53 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] FIREBIRD PIVOT function or Matrix

 

  

On 14-2-2017 15:48, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] wrote:
> 14.02.2017 14:15, 'Stef' s...@autotech.co.za [firebird-support] wrote:
>> Any suggestions on my DB design and query to display the values in matrix 
>> (pivot) format?
>
> Simple client-side code can show you the matrix in any convenient form. There 
> is no
> point to do that on server.

Having used SQL Server PIVOT and having done a similar thing manually, I 
have to strongly disagree with that. PIVOT is a strong feature and 
greatly simplifies producing a crosstab, especially if you want to do 
additional processing in a query with the result of that crosstab.

Mark
-- 
Mark Rotteveel

Re: [firebird-support] FIREBIRD PIVOT function or Matrix

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 14-2-2017 15:48, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] wrote:
> 14.02.2017 14:15, 'Stef' s...@autotech.co.za [firebird-support] wrote:
>> Any suggestions on my DB design and query to display the values in matrix 
>> (pivot) format?
>
>Simple client-side code can show you the matrix in any convenient form. 
> There is no
> point to do that on server.

Having used SQL Server PIVOT and having done a similar thing manually, I 
have to strongly disagree with that. PIVOT is a strong feature and 
greatly simplifies producing a crosstab, especially if you want to do 
additional processing in a query with the result of that crosstab.

Mark
-- 
Mark Rotteveel

RE: [firebird-support] FIREBIRD PIVOT function or Matrix

['Stef' s...@autotech.co.za [firebird-support]]

Thanks all

 

But this still does not answer my question as to db design and query?

 

Regards

 

Stef van der Merwe

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 14 February 2017 06:03 PM
To: 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support]
Subject: Re: [firebird-support] FIREBIRD PIVOT function or Matrix

 

  

I go with Sean.

Btw, this is already in the tracker:
http://tracker.firebirdsql.org/browse/CORE-1738

Vote on it!

Carlos
www.firebirdnews.org - www.FireBase.com.br

>> > Any suggestions on my DB design and query to display the values in
matrix
>> (pivot) format?
>> 
>> Simple client-side code can show you the matrix in any convenient form.
>> There is no point to do that on server.

LSSBcfs> I disagree!

LSSBcfs> There are times when I would love to be able to pivot data
LSSBcfs> on the server, to further process the data an SP.

LSSBcfs> Also, performing the pivot on the server could reduce the
LSSBcfs> total amount of data transferred to the client.

LSSBcfs> Sean





[Non-text portions of this message have been removed]

Re: [firebird-support] FIREBIRD PIVOT function or Matrix

['Carlos H. Cantu' lis...@warmboot.com.br [firebird-support]]

I go with Sean.

Btw, this is already in the tracker:
http://tracker.firebirdsql.org/browse/CORE-1738

Vote on it!

Carlos
www.firebirdnews.org - www.FireBase.com.br



>> > Any suggestions on my DB design and query to display the values in matrix
>> (pivot) format?
>> 
>>Simple client-side code can show you the matrix in any convenient form.
>> There is no point to do that on server.

LSSBcfs> I disagree!

LSSBcfs> There are times when I would love to be able to pivot data
LSSBcfs> on the server, to further process the data an SP.

LSSBcfs> Also, performing the pivot on the server could reduce the
LSSBcfs> total amount of data transferred to the client.


LSSBcfs> Sean