Database

Re: Optionally use BCP in stored proc

6-Nov-07 1:41

I have a set of procs I use to select data for an export to an accounting package API. Now I would like to modify these procs so that I can optionally also output the accounting data to a CSV file, to assist in regression testing. I see two options here:

1. Change the select queries to insert queries into temp tables, then optionally write the temp table through BCP, then always select from the temp table for output.
2. Encapsulate the select queries into a string, then optionally pass this string to BCP, then always execute the string.

Which method strikes the experts as better?

MY BLOG

Brady Kelly6-Nov-07 2:30

Copy data between databases [modified]

6-Nov-07 2:30

I've just tried the temp table method, and in BCP the server complains about the name of the temp table, then the proc goes on to select from the temp table, so it does exist.

SELECT blah blah...,
	TaxRefNo 
INTO 
	#cpPastel_GetPartners_Temp
FROM 
	[caCustomerForExport] 
WHERE 
	CustomerID in (select exportID from caExportID where exportItem = @partnerType)
EXEC master..xp_cmdshell 'BCP #cpPastel_GetPartners_Temp OUT "test.txt" -S(local) -T -c'
SELECT 
	*
FROM 
	#cpPastel_GetPartners_Temp

MY BLOG

Nick De Decker5-Nov-07 23:02

Nick De Decker

5-Nov-07 23:02

Hi,

I've spent hours trying to copy data from a table in database A to a similar table in database B.
My code doesn't return any errors but I simply don't get any rows in my db.

Can someone tell me how to do this ?

I'm using mysql as database.

Dim CnERP As MySql.Data.MySqlClient.MySqlConnection = Febelfin.Administratie.Propertys.DatabaseConnectie<br />
Dim CnSite As New MySql.Data.MySqlClient.MySqlConnection<br />
CnSite.ConnectionString = "Database=databaseB;Data Source=host;User Id=nick; Password=test"<br />
'databaseconnectie was predefined ...<br />
<br />
<br />
Dim Cmd As New MySql.Data.MySqlClient.MySqlCommand<br />
Cmd.CommandText = "SELECT * FROM opleiding WHERE opl_publiceren=1"<br />
Cmd.Connection = CnERP<br />
<br />
        Dim DTerp, DTsite As New DataTable<br />
<br />
        Dim AdaptErp As New MySql.Data.MySqlClient.MySqlDataAdapter(Cmd)<br />
        AdaptErp.Fill(DTerp)<br />
<br />
        Cmd.Connection = CnSite<br />
<br />
<br />
        Dim Cmd2 As New MySql.Data.MySqlClient.MySqlCommand<br />
        Cmd2.CommandText = "SELECT * FROM opleiding_nick"<br />
        Cmd2.Connection = CnSite<br />
<br />
        Dim adaptSite As New MySql.Data.MySqlClient.MySqlDataAdapter(Cmd2)<br />
        adaptSite.Fill(DTsite)<br />
<br />
        Dim CBsite As New MySql.Data.MySqlClient.MySqlCommandBuilder(adaptSite)<br />
<br />
        For Each dr As DataRow In DTerp.Rows<br />
            DTsite.ImportRow(dr)<br />
            dr.SetAdded()<br />
            dr.AcceptChanges()<br />
        Next<br />
<br />
        DTsite.AcceptChanges()<br />
        CnSite.Open()<br />
        adaptSite.Update(DTsite)<br />
        CnSite.Close()<br />
<br />
        DTsite.AcceptChanges()<br />
<br />

.... so i've tried a lot, but nothing seems to work.
I'm probably forgetting something.

How come it does not insert the rows in the destination db ?

thanks !

-- modified at 5:22 Tuesday 6th November, 2007

Re: Copy data between databases

belloSoft7-Nov-07 10:55

belloSoft

7-Nov-07 10:55

I am not sure about the code you wrote , but one simple way to solve the problem fast is
1- write the code to get the data from the 1st table and print it on the screen, to make sure that the data is complete and correct
2- Now write a code to insert data on the second database and test it independantely ; to make sure that the insert is Ok!

3- Now form one procedure from the two parts you created in point 1 and 2

NOTE : be aware about the constraints , primary keys , and referencing to other tables
[You may want to delete the row from the original table and try to write it back to its original place using the same code you use to insert on the second database ; this will make you sure that there is no problem with the new code ]

Important : USE ONLY YOUR TEST TABLES NOT THE ORIGINAL ONES YOU DEPEND ON FOR YOUR BUSENESS OR SO. and have packups all the time

--BelloSoft

GUID in Access 2000

Christian Graus4-Nov-07 23:44

Christian Graus

4-Nov-07 23:44

I have Access 2003. I need to write a C# app to import the contents of a (very simple) Access 2000 database. In this database are three tables, linked by primary keys. The keys have the datatype of 'number', but when I look at them in Access 2003, they are GUIDs. I can't work out how to write the SQL to get the records associated with a GUID. Based on some reading, I tried this:

OleDbCommand cmd = Myconnection.CreateCommand();
cmd.CommandText = "SELECT * FROM Records where Key = {GUID {" + clientKey + "}}";
dbReader = cmd.ExecuteReader();

but the reader is always empty. What's the syntax for filtering on a number data type that turns out to be a guid ?

Thanks

Christian Graus - Microsoft MVP - C++

"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

Database Testing Concurrency [modified]

Brady Kelly4-Nov-07 22:47

4-Nov-07 22:47

I hope this isn't considered too much of a programming question, and the solution shouldn't involve any programming.

At the moment, two of us are testing related applications on local copies of our test database, to not get in each other's way. However, I would like to set up a shared, central test database, where I can be sure the configuration, i.e. versions of views and stored procedures, are consistent and can serve as a baseline for releases. However, I would also like us to be able to independently manipulate data, on separate copies of the data, with identical configuration.

So, I'd like all DDL operations to always be replicated, but all DML operations to remain local most of the time. What aspects of replication should I be looking at to achieve this?

MY NEW BLOG

-- modified at 8:21 Monday 5th November, 2007

Pete O'Hanlon4-Nov-07 22:51

4-Nov-07 22:51

I'd actually consider using this[^] if this is the way you want to operate. This way, you can synchronise your test database as and when you need to.

Deja View - the feeling that you've seen this post before.

Mustafa Ismail Mustafa4-Nov-07 23:17

Mustafa Ismail Mustafa

4-Nov-07 23:17

Do you know of a tool or a way where we can actually track the dependencies and changes of DDLs? Something similar to source control only for databases.

Because this is giving me a headache to maintain versions for differing releases and the merging of them and all that.

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rick Cook

"There is no wealth like knowledge, no poverty like ignorance." Ali ibn Abi Talib

Re: OT

Pete O'Hanlon4-Nov-07 23:24

4-Nov-07 23:24

The tool in the link I posted is designed to do just that. To use it, you just compare a source and a destination database and it generates the synchronisation scripts for you.

Deja View - the feeling that you've seen this post before.

Re: OT

Brady Kelly5-Nov-07 0:06

5-Nov-07 0:06

On our main dev tree, we use Subversion for this, with an automated scripting and commit process. I'm sorry I don't have details right now as I am not involved in that side of things at the moment.

MY NEW BLOG

SimulationofSai4-Nov-07 22:57

SimulationofSai

4-Nov-07 22:57

Use DDL triggers to trigger a change in both your DB's. You dont need to do anything for the DML part.

SG
Cause is effect concealed. Effect is cause revealed.

Pete O'Hanlon4-Nov-07 23:02

4-Nov-07 23:02

Relying on this behaviour is actually quite dangerous. What happens if the target database is down for instance? You need to ensure that ACID is followed in an operation like this, so the second update should really be disconnected from the main update.

Deja View - the feeling that you've seen this post before.

Brady Kelly4-Nov-07 23:16

4-Nov-07 23:16

Pete O'Hanlon wrote:
[Rep

I initially planned to host both test databases on the same instance as the 'master' test database, where at the moment they are locally hosted on our individual machines. Hosting them on the same server would mitigate the risk you mention.

MY NEW BLOG

Pete O'Hanlon4-Nov-07 23:25

4-Nov-07 23:25

Brady Kelly wrote:
I initially planned to host both test databases on the same instance as the 'master' test database

But you are still going to be making your changes locally? If so, the risk is exactly the same.

Deja View - the feeling that you've seen this post before.

Brady Kelly4-Nov-07 23:36

4-Nov-07 23:36

No, if I host one test database each, TestDB1 and TestDB2, and a master test database, TestDBM, on the same server, DML changes we individually make will apply to TestDB1 and TestDB2, not affecting each other, and DDL changes we make to TestDBM will always be able replicate to the other two because their availability will practically always be the same as TestDBM.

MY NEW BLOG

Pete O'Hanlon4-Nov-07 23:44

4-Nov-07 23:44

Brady Kelly wrote:
No, if I host one test database each, TestDB1 and TestDB2

The point I am trying to get across is where are you going to be making the changes initially? Surely not to a test database? You should always develop against a development installation, and then replicate your changes across to the test environment. This is where the issue lies - not in synchronising between test databases, but synchronising between development and test databases.

In general, your development environment should be kept well away from your test environment.

Deja View - the feeling that you've seen this post before.

Brady Kelly4-Nov-07 23:58

4-Nov-07 23:58

I want us each to have a personal dev environment, as well as a personal test environment, the latter preferably hosted together with the master test environment.
DDL changes will be made locally, in our individual dev environments, yes, but will be manually deployed to the master test, from which they should propagate back to our individual test environments.

MY NEW BLOG

Pete O'Hanlon5-Nov-07 0:01

5-Nov-07 0:01

So, isn't an automated approach a better choice then? Remove the danger that changes are missed between the dev and test environments.

Deja View - the feeling that you've seen this post before.

Brady Kelly5-Nov-07 0:04

5-Nov-07 0:04

It is and will be, but for this release an untested, automated approach seems a bit risky. I would like to move toward that though, using November to verify the the automated tasks before a December and future releases.

MY NEW BLOG

SimulationofSai5-Nov-07 0:08

SimulationofSai

5-Nov-07 0:08

I wouldn't worry too much about it in a testing/development environment like what's being intended here. It's not production and whatever error that comes out of using DDL triggers are traceable and rectifiable.

SG
Cause is effect concealed. Effect is cause revealed.

Pete O'Hanlon5-Nov-07 0:22

5-Nov-07 0:22

I always worry whenever I hear people comment that consistency between dev and testing isn't too big a deal. The issue you have here is that you introduce something else into your testing process that doesn't need to be there. Why is this an issue?

Well, you have a development environment, which may or may not be different to a testing environment, which may or may not be different to your production environment. How do you ensure that what you did in the development phase makes it into the production phase?

Testing should replicate the production environment as much as is practicable, and should really be done up front. It shouldn't need manual intervention to ensure that "script A is run and then script B and then script C, and oh yes, we've got to run script D as well - mustn't forget that because it recreates the views that only get hit once a week, but are absolutely vital".

Deja View - the feeling that you've seen this post before.