Database Buffer

Hi,

What is the maximum size of the database buffer? I was running a database query and it returned an out of memory error. Thanks.

The amount of free virtual memory available on your machine or 2GB whatever is reached first.

My guess your database query is incorrect. Try running it in SQL editor and checking how many records it returns. If it returns more then a few thousands records you are definitely doing something wrong. Either the query is wrong, bad WHERE part or the job goal.

I was basically trying to transfer tables from one database to another, using the DatabaseSave command to create csv files and subsequently importing the records to the target database. After further reading of the help files, I think the DatabasePipe command might be a better choice.

I've just tested the command and received the primary key violation error from the target database. I followed the help file and made sure my destination table has a primary key so I am not exactly sure why that error occurred. Is there something else I am missing? Thanks!

The problem is that you are inserting non-unique data that violates your primary key. You should either correct the primary key definition and add additional columns to make the key really unique or clean up the source data and remove duplicates.

I think I know what is the issue. After I truncated the records in my target table, then the DatabasePipe command worked. It seems the command is not deleting the records from the target table, which I thought it should have done when the INSERT method is used. Perhaps I am misunderstanding the help file, but this is how I thought the DatabasePipe, insert method, works:

1. Run the sql query in the source database and get the recordset.
2. Delete the records from the target database where the primary key of the recordset from step 1 equals to the primary key of the target db.
3. Insert the recordset from step 1 into the target db.

Is that the correct process? If not, please let me know where I misunderstood. Thanks.

I am not sure what the help page for DatabasePipe says is actually accurate. I think with "INSERT" method DatabasePipe only generates INSERTs, with "UPDATE" only generates UPDATEs. To make sure the destination table is clean, you can put DatabaseExecute( "TRUNCATE TABLE mytable", rows ) before DatabasePipe call. If you need to replace only the existing rows, but do not delete everything, use an intermediate table to pump the source data, then execute a couple of additional SQL statements to complete the data synchronization, for example DELETE FROM my_table WHERE EXISTS (SELECT * FROM staging_table WHERE staging_table.key = my_table.key) and after that INSERT INTO my_table SELECT * FROM staging_table.