Append to sql from dbf

[Terrymorgan]

I currently have an append to sql from dbf using a standard append operation to an active link table. This is slow & am sure would be quicker with an sql insert.
what is the basic outline of the code to achieve this.
Thanks

[Roland]

Use AlphaDAO to insert records to your SQL database. First, create a named connection string (Click on "Tools"->"External Databases"->"AlphaDAO Connection Strings"). Sample code below.

dim args as sql::arguments
args.Add("my_field1", value1)
args.Add("my_field2", value2)
args.add("my_field3", value3)

dim sql_command as C = "INSERT INTO my_table_name (my_field1, my_field2, my_field3) VALUES (:my_field1, :my_field2, :my_field3)"

' connect to database
dim cn as sql::Connection
if .not. cn.open("::Name::ConnectionStringName")
     if cn.CallResult.text <> ""
          ui_msg_box("", cn.CallResult.text)
     else
          ui_msg_box("", "Connection failed." )
     end if
end if

' execute query
if .not. cn.execute(sql_command, args)
     ui_msg_box("", cn.CallResult.text)
elseif cn.CallResult.RowsAffected = 1
    ui_msg_box("", "successful")
end if

' close connection
if cn.IsOpen
     cn.Close()
end if

You may download the attached sample workspace. It shows how to interact with AlphaDAO database (select, insert, update, delete).

[Terrymorgan]

Presumably there is no append function so I have to wrap this in fetching through the records if the dbf
Thanks

[Ted Giles]

Does this need to be automated or with manual intervention?
You could export to Excel and import to the Sql Table.

[Terrymorgan]

Automatically append all or some of the records from the dbf to the sql plus also not all fields
i will actually be taking data from 15 dbf’s to 1 sql

[madtowng]

Alpha is pretty quick about creating text files.
I would then use load data to import the text file to the sql table.
Special note, if you are use MS SQL, you will have to get the text file to the sql server machine.
You would not have that same limitation using mySQL.

[menehune]

Here's a snippet that should get you started. Search the docs for ResultSetFromDBF and InsertData.

dim conn as SQL::Connection
dim rs as SQL::ResultSet

tbl = table.open("local")
'query.filter = "Doc_ID = 3"
'tbl.query_create()
rs = conn.ResultSetFromDBF(tbl)

if .not. conn.InsertData("", "local_test", rs)
ui_msg_box("Error", "Could not insert data into back-end table" + crlf()+ conn.CallResult.text)
conn.close()
end
end if

tbl.close()
conn.close()

[Terrymorgan]

It looks like resultsetfromdbf would require structure / naming of dbf & sql to match, which is not the case for me.
Thanks

[Ted Giles]

Seing as you are stuffing many dbf's (tables or whole databases?) into one SQL table, why not build - and I am assuming you meant Tables - a new composite table in Alpha and then just upsize it?.
If you really meant 15 databases you could still do it that way in just one workspace.

[Terrymorgan]

Yes 15 dbf tables into 1 sql table for all sorts of searching & reporting including online
The new 1 sql table
will is built populated - currently using append operation but it is slow
will be rebuilt periodically (once a week)- currently deletes all records & is using append operation but it is slow
on the save of a new record in one of the 15 tables the 1 sql table will have new record added - currently using sql insert function that is quick but needs a manual list of the 100 fields taking time to create the script/fieldsValuePairs
on the change of a record in one of the 15 tables the 1 sql table will be updated- currently using sql update function that is quick but needs a manual list of the 100 fields taking time to create the script/fieldsValuePairs
I have a similar system for a different collection of tables all built & working well, all, in dbf but as I am in the slow process of moving to sql am going this route - also part of the learning curve

If there is a quicker method i am open to suggestions

[mumfie]

Hi Terry,
We used a third party product https://www.spectralcore.com/databases/dbf to export dbf tables from DBF to MSSqlserver as part of overnite process. It was called as a scheduled process and generated a script which was executed on the sql server. Similar third party products are available but you might need to check support for memo fields etc if used.

[Terrymorgan]

I have changed to
The new 1 sql table
is built / populated - using fetch through dbf table records using sql insert function for each record, this is perhaps 10 times faster than append to active link
will be rebuilt periodically (once a week) - currently deletes all records in SQL & is using fetch through dbf table records using sql insert function for each record, this is perhaps 10 times faster than append to active link
on the save of a new record in one of the 15 tables the 1 sql table will have new record added - currently using sql insert function that is quick but needs a manual list of the 100 fields taking time to create the script/fieldsValuePairs
on the change of a record in one of the 15 tables the 1 sql table will be updated- currently using sql update function that is quick but needs a manual list of the 100 fields taking time to create the script/fieldsValuePairs

The only issue i am really left with is the laborious task of setting up the fieldValuePairs as these also differ between the dbf's

Thanks for the help

[Ted Giles]

Well I'm glad you have a workable solution, but - and you won't want to hear this - you will have to migrate to SQL at some time and build an OLAP Cube for reporting and searches I suspect.
I have built them for reporting purposes in Alpha where there was specific reporting and search requirements in a complex data set of 3 million records. Built the Cube overnight. Cuts down on traffic enormously.
Still, you have a working option and that's the main thing.