1. 

   RobertAZ, Member

   I'd like to import a spreadsheet into a temporary table for downstream mining. The manual doesn't really say what the procedure for 'dbr.import' 'sp_DBR_import_do' should have in it.... An insert statement for each row? Do we need to code in checks for SQLSTATE within a while block that retrieves one row at a time? Just how does the import work? If I use an insert statement can it reference the columns by name, or only by number?

   And do the prepare and "finish" procedures need to be stored separately for the call by dbr.import? If they do, I can't save the procedure until it's actually executed...and I can't define multiple procedures within a procedure...so I'm confused.

   Thanks
2. 

   myDBR Team, Key Master

   Hi,
   the manual is bit vague. We'll make some improvements there.

   The import file is a tab separated text file with each record on it's own line. Each values on row are matched with the import_procedure's input variables.

   So if you have text file like this:

   ---------
   12<tab>Acros
   13<tab>Brennan
   ---------

   your import procedure would be of form

   create procedure sp_dbr_import ( inID int, inName varchar(30) )
   begin

   insert into mytable
   values ( inID, inName );

   end;

   Prepare and finish procedures can be used if you wish to do preparations before or after the import has been completed. Usually preparing cleans up some temp table and finish procedures then does everything to all rows at once.

   --
   myDBR Team
3. 

   RobertAZ, Member

   Thanks for such a quick reply. So effectively the import procedure is invoked for each row of the input file? Will the below work as well? The select clause refers to column names in an excel spreadsheet...or can we only import tab-delimited text?

   Here's my current import procedure:
   DROP PROCEDURE IF EXISTS sp_DBR_Import_Reloads_do
   $$
   CREATE PROCEDURE sp_DBR_Import_Reloads_do()
   BEGIN
   insert into reloads (date, team, closer, client_name, coach, leads, price)
   select 'Date','Team','Closer','Client Name','Coach','Leads','POSTED';
   END
   $$

   My "prepare" procedure looks like this:
   DROP PROCEDURE IF EXISTS sp_DBR_Import_Reloads_pre
   $$
   CREATE PROCEDURE sp_DBR_Import_Reloads_pre()
   BEGIN
   /* Create temporary table to insert the reload info */
   drop temporary table if exists reloads;
   create temporary table reloads (
   date char(10),
   team char(12),
   closer char(20),
   client_name char(30),
   coach char(12),
   leads int,
   price decimal(10.0)
   );
   END
   $$

   And my top-level report procedure is -
   DROP PROCEDURE IF EXISTS sp_DBR_Import_Reloads
   $$
   CREATE PROCEDURE sp_DBR_Import_Reloads()
   BEGIN

   select 'dbr.import.prepare', 'sp_DBR_Import_Reloads_pre';
   select 'dbr.import.finish', 'sp_DBR_Import_Reloads_post';
   select 'dbr.import', 'sp_DBR_Import_Reloads_do';
   END
   $$
4. 

   myDBR Team, Key Master

   So effectively the import procedure is invoked for each row of the input file?

   Correct.

   Will the below work as well? The select clause refers to column names in an excel spreadsheet...or can we only import tab-delimited text?
   

   myDBR reads a text file row by row and calls the import procedure for each row. The import procedure must have same number of parameters as the row has values.

   The command:

   insert into reloads (date, team, closer, client_name, coach, leads, price)
   select 'Date','Team','Closer','Client Name','Coach','Leads','POSTED';

   would just insert same texts ('Date' etc) for each row. Use the variables for the procedure instead.

   You also might want to use normal table instead of temporary table since the temporary table is removed when the connection is closed (if you do not process the rows in sp_DBR_Import_Reloads_post).

   --
   myDBR Team
5. 

   RobertAZ, Member

   OK, I changed my import procedure to -

   DROP PROCEDURE IF EXISTS sp_DBR_Import_Reloads_do
   $$
   CREATE PROCEDURE sp_DBR_Import_Reloads_do(
   date char(10),
   team char(12),
   closer char(20),
   client_name char(30),
   coach char(12),
   leads int,
   price decimal(10.0))
   BEGIN
   /* Create temporary tabl to insert the reload info */

   insert into reloads values (date, team, closer, client_name, coach, leads, price);

   END
   $$

   But then when I ran the top-level report (see previous note above) I got this -

   03:07:44 PM: Query execution time: 0.01 sec
   dbr.import.prepare sp_DBR_Import_Reloads_pre
   dbr.import.prepare sp_DBR_Import_Reloads_pre
   # 1

   dbr.import.finish sp_DBR_Import_Reloads_post
   dbr.import.finish sp_DBR_Import_Reloads_post
   # 1

   dbr.import sp_DBR_Import_Reloads_do
   dbr.import sp_DBR_Import_Reloads_do
   # 1

   What does it mean? Thanks.
6. 

   RobertAZ, Member

   Excuse me. What I posted was the result from storing the procedure. Forgot the execute tab. So when I ran the top-level, I got this....

   Incorrect number of arguments for PROCEDURE gfnreports.sp_DBR_Import_Reloads_do; expected 7, got 0

   If I coded -
   select 'dbr.import', 'sp_DBR_Import_Reloads_do(
   date char(10),
   team char(12),
   closer char(20),
   client_name char(30),
   coach char(12),
   leads int,
   price decimal(10.0)';

   Where would the values for these come from??
7. 

   RobertAZ, Member

   Wow...all I'm getting on the results page is --

   CREATE PROCEDURE sp_DBR_Import_Reloads()
   BEGIN

   select 'dbr.import.prepare', 'sp_DBR_Import_Reloads_pre';
   select 'dbr.import.finish', 'sp_DBR_Import_Reloads_post';
   select 'dbr.import', 'sp_DBR_Import_Reloads_do(,,,,,,)';
   END
   03:19:29 PM: Query execution time: 0.01 sec
   call sp_DBR_Import_Reloads()
   03:19:39 PM: Query execution time: 0.01 sec
   dbr.import.prepare sp_DBR_Import_Reloads_pre
   dbr.import.prepare sp_DBR_Import_Reloads_pre
   # 1

   dbr.import.finish sp_DBR_Import_Reloads_post
   dbr.import.finish sp_DBR_Import_Reloads_post
   # 1

   dbr.import sp_DBR_Import_Reloads_do(,,,,,,)
   dbr.import sp_DBR_Import_Reloads_do(,,,,,,)
   # 1
   Notice that I got around the parameter error by sending in nulls...but I didn't get any request for a file name. Isn't that supposed to happen?
8. 

   RobertAZ, Member

   Here's another try using the input file name. It didn't use it...as far as I can see.

   03:24:13 PM: Query execution time: 0.01 sec
   CREATE PROCEDURE sp_DBR_Import_Reloads()
   BEGIN

   select 'dbr.import.prepare', 'sp_DBR_Import_Reloads_pre';
   select 'dbr.import.finish', 'sp_DBR_Import_Reloads_post';
   select 'dbr.import', 'sp_DBR_Import_Reloads_do(,,,,,,)', 'C:\Reloads_Aug12.txt';
   END
   03:24:13 PM: Query execution time: 0.01 sec
   call sp_DBR_Import_Reloads()
   03:24:23 PM: Query execution time: 0.01 sec
   dbr.import.prepare sp_DBR_Import_Reloads_pre
   dbr.import.prepare sp_DBR_Import_Reloads_pre
   # 1

   dbr.import.finish sp_DBR_Import_Reloads_post
   dbr.import.finish sp_DBR_Import_Reloads_post
   # 1

   dbr.import sp_DBR_Import_Reloads_do(,,,,,,) C:Reloads_Aug12.txt
   dbr.import sp_DBR_Import_Reloads_do(,,,,,,) C:Reloads_Aug12.txt
   # 1

   Here's my post procedure -
   DROP PROCEDURE IF EXISTS sp_DBR_Import_Reloads_post
   $$
   CREATE PROCEDURE sp_DBR_Import_Reloads_post()
   BEGIN
   /* Display contents of temporary table reloads */

   select
   r.date,
   r.team,
   r.closer,
   r.client_name,
   r.coach,
   r.leads,
   r.price
   from reloads r;
   END
   $$
9. 

   RobertAZ, Member

   Changing the order of the dbr.import statements didn't make a difference.

   03:28:19 PM: Query execution time: 0.01 sec
   CREATE PROCEDURE sp_DBR_Import_Reloads()
   BEGIN

   select 'dbr.import.prepare', 'sp_DBR_Import_Reloads_pre';
   select 'dbr.import', 'sp_DBR_Import_Reloads_do(,,,,,,)', 'C:\Reloads_Aug12.txt';
   select 'dbr.import.finish', 'sp_DBR_Import_Reloads_post';
   END
   03:28:19 PM: Query execution time: 0.01 sec
   call sp_DBR_Import_Reloads()
   03:28:29 PM: Query execution time: 0.01 sec
   dbr.import.prepare sp_DBR_Import_Reloads_pre
   dbr.import.prepare sp_DBR_Import_Reloads_pre
   # 1

   dbr.import sp_DBR_Import_Reloads_do(,,,,,,) C:Reloads_Aug12.txt
   dbr.import sp_DBR_Import_Reloads_do(,,,,,,) C:Reloads_Aug12.txt
   # 1

   dbr.import.finish sp_DBR_Import_Reloads_post
   dbr.import.finish sp_DBR_Import_Reloads_post
   # 1
   - or using 'now'--

   CREATE PROCEDURE sp_DBR_Import_Reloads()
   BEGIN

   select 'dbr.import.prepare', 'sp_DBR_Import_Reloads_pre';
   select 'dbr.import', 'sp_DBR_Import_Reloads_do(,,,,,,)', 'C:\Reloads_Aug12.txt','now';
   select 'dbr.import.finish', 'sp_DBR_Import_Reloads_post';
   END
   03:30:21 PM: Query execution time: 0.01 sec
   call sp_DBR_Import_Reloads()
   03:30:31 PM: Query execution time: 0.01 sec
   dbr.import.prepare sp_DBR_Import_Reloads_pre
   dbr.import.prepare sp_DBR_Import_Reloads_pre
   # 1

   dbr.import sp_DBR_Import_Reloads_do(,,,,,,) C:Reloads_Aug12.txt now
   dbr.import sp_DBR_Import_Reloads_do(,,,,,,) C:Reloads_Aug12.txt now
   # 1

   dbr.import.finish sp_DBR_Import_Reloads_post
   dbr.import.finish sp_DBR_Import_Reloads_post
   # 1
10. 

    RobertAZ, Member

    Hey, could I use a LOAD DATA statement? If so, any advice?
11. 

    RobertAZ, Member

    Apparently not....

    CREATE PROCEDURE sp_DBR_Import_Reloads()
    BEGIN

    select 'dbr.import.prepare', 'sp_DBR_Import_Reloads_pre';
    select 'dbr.import', 'sp_DBR_Import_Reloads_do(,,,,,,)', 'C:\Reloads_Aug12.txt','now';
    select 'dbr.import.finish', 'sp_DBR_Import_Reloads_post';

    call sp_DBR_Import_Reloads_pre();
    LOAD DATA INFILE 'C:\Reloads_Aug12.txt' INTO reloads;
    call sp_DBR_Import_Reloads_post();
    END

    LOAD DATA is not allowed in stored procedures

    Your procedure has not been saved to database.
    Fix the error or revert back to original.
12. 

    RobertAZ, Member

    If I use a normal table, where will it be stored? Is there a naming scheme based on report folders...? Or can I just refer to it by name in later procedures?

    Thanks, Robert
13. 

    RobertAZ, Member

    I got a strange result this time...

    CREATE PROCEDURE sp_DBR_Import_Reloads()
    BEGIN

    -- select 'dbr.import.prepare', 'sp_DBR_Import_Reloads_pre';

    -- select 'dbr.import.finish', 'sp_DBR_Import_Reloads_post';

    call sp_DBR_Import_Reloads_pre();
    select 'dbr.import', 'sp_DBR_Import_Reloads_do(,,,,,,)', 'C:\Reloads_Aug12.txt','now';
    call sp_DBR_Import_Reloads_post();
    END

    03:49:26 PM: Query execution time: 0.01 sec
    call sp_DBR_Import_Reloads()
    03:49:37 PM: Query execution time: 0.01 sec
    dbr.import sp_DBR_Import_Reloads_do(,,,,,,) C:Reloads_Aug12.txt now
    dbr.import sp_DBR_Import_Reloads_do(,,,,,,) C:Reloads_Aug12.txt now
    # 1

    date team closer client_name coach leads price

    Notice that my variable names got printed out by the post procedure as column headings, but the table remains empty. So how do I get this import to work?
14. 

    myDBR Team, Key Master

    Robert,
    the import call should be just:

    select 'dbr.import', 'sp_DBR_Import_Reloads_do';

    (myDBR will automatically read the parameters and match them to input file)

    Also, make sure you attach the procedure as a report and run it as a report. If you run the procedure in SQL Editor, all you get is the output of the procedure.

    Btw, you might want to avoid using names like "date" in column names since they are reserved words.

    --
    myDBR Team

Reply

You must log in to post.