Importing Data into Alpha from SQL/ADO Reports

[David Brown]

Problem: My data is stored in SQL Server tables. I need to print reports using this data from my Web Application.

A possible solution to this problem is to import the data from SQL Server into Alpha 5 tables.

Question: What is the best way to automate this process as it will need to be repeated on a regular bases.

What other methods are being used to give reporting capabilities to your web apps that are using ADO data?

Thank you,

David
Miami, FL

[Edward Larrabee]

Version 7 has a new ADO Import Genie that can retrieve the data. You could add the code to have it run on a schedule. See ADO Import Genie.

[David Brown]

Thanks for the reply. What is the best way to do this in version 6. Can it be done?

I could use a select statement and loop through all of the result records adding each to my table. But is there a better way?

[Edward Larrabee]

I believe it can be done. You should check the forum and webhelp for sample code that shows how to read from an ADO compatible source.

[gregz]

Just have MSSQL export the data to a .dbf file on a nightly basis.

[David Brown]

Thanks for the reply. Great suggestion.

Since the data needed to be synchronized every time a report is printed I chose to write a routine that selects records from the sql tables and adds them to the Alpha dbf tables every time a report is printed.

The code below is similar to what I did, but not as complex.
----------------------------------------------
Select * From SQLtbl1

dim A5tbl1 as P
A5tbl1 = table.open("A5tbl1")
While .not. rs.eof()
   A5tbl1.enter_begin

   A5tbl1.fld1 = rs.fields.item(0).value
   A5tbl1.fld2 = rs.fields.item(1).value

   A5tbl1.enter_end
end while

-------------------------------------------------

-David

[gregz]

What does the rest of your code look like?

[Selwyn Rabins]

Some things to think about:

The ADO record set has a GetString() method (I think that's its name - could be something else), that allows you to dump an entire record set to a text file.

Alpha Five has an extremely fast text import command.

So, instead of looping through all of the records in the record set, one by one, you could get a huge increase in performance by dumping the data to a text file and then importing it into an a5 table.

[gregz]

Any examples? I would like to test this on a SQL database.
Greg

[Selwyn Rabins]

look at the ado import genie in a5v7. it uses this technique

[David Brown]

Using stored procedure to export records to text

MICROSOFT SQL SERVER
A stored procedure to export/import data using BCP
By: Eli Leiba
02.26.2002

This is a stored procedure that can be run from a master database to export or import a table to/from a given file path+name from/to a given database.

  
create Proc Sp_Export_Or_import_Table @dbName     varchar(30), 
                                      @tbName     varchar(30), 
                                      @filePath   varchar(80), 
                                      @cmode      char(6), 
                                      @sep        char(1), 
                                      @usr        varchar(30), 
                                      @pwd        varchar(30) 
as 
declare @cmd varchar(200) 
begin 
  IF @cmode = 'EXPORT' 
  begin 
      set @cmd = 'bcp.exe ' +  
                 @dbName + '..' + @tbName +  ' out '  + 
                 @filePath + ' -c -q -C1252 -U ' + @usr + 
                 ' -P ' + @pwd + ' -t' + @sep 
      print @cmd + '...' 
      exec xp_cmdShell @cmd 
  end 
  IF  @cmode = 'IMPORT' 
  begin 
     set @cmd = 'bcp.exe ' +  
                 @dbName + '..' + @tbName +  ' in '  + 
                 @filePath + ' -c -q -C1252 -U ' + @usr + 
                 ' -P ' + @pwd + ' -t' + @sep 
      print @cmd + '...' 
     exec xp_cmdShell @cmd   
  end 
End 
go 
-- *********************************************************** 
-- How To Use it 
-- Export a Table From a given DataBase to a File. 
Exec Sp_Export_Or_import_Table 'Northwind', 
                               'Orders', 
                               'c:tempdbNorthwind_Orders.Dat', 
                               'EXPORT', 
                               '@', 
                               'iecdba', 
                               'sapwd' 
-- Import a table to a given DataBase from a File 
use pubs 
go 
select * into newOrders from northwind..orders where 0 = 1 
go 
use master 
go 
Exec Sp_Export_Or_import_Table  'pubs', 
                               'NewOrders', 
                               'c:tempdbNorthwind_Orders.Dat', 
                               'IMPORT', 
                               '@', 
                               'iecdba', 
                               'sapwd' 
-- ***********************************************************

[AaronBBrown]

David,

I added

[code]
[/code]


around your code to preserve the formatting. You can also do this by clicking the button on the editor toolbar that looks like a # sign, next to the one that looks like "< >"