Database migration to local computer.

[JThan]

I need to export or save the database from sql server by programming in powerbuilder.

I'm not sure if it's about exporting or saving the database, but here's what I want to do...

1. The database is on the server which contain all information about the products including the pictures. (database size is about 4GB and update daily)

2. We are using an application written by PowerBuilder to see the data inside sql server.

3. In order for a saleperson to be able to use this data out side of the office, the database have to be saved into local computer.

4. The database that is saved or exported should be able to be view using the same application which is using in the office.

Note: We are not creating online applicatoin for the saleperson to view it online because the saleperson has to go out into some remotely area which doesn't have an internet connection.

Please give me any suggestion and idea on how to apply this task using PowerBuilder...

Thank you so much

 

[JThan]

I forgot to add this, I kind of look into exporting or save it into an excel format, but I'm not sure about the picture files.

 

[miguelleeuwe]

Realize that with Excel you'll be limited to 65.000 and something records. As for the picture I think it'll also be dificult with excel.

Why don't you just install sql server on the local computer / a more limited version of it and then do database dumps and restore them ? The advantage would be that you don't have to do any modifications to your powerbuilder program.

hope it helps.
Miguel

regards,
Miguel L.

 

[JThan]

Thanks miguelleeuwe, I'm thinking about that too.
May be I should install sql server express on the local computers.

However, are there any suggestion on dumping the database into the local computers by writing some code in PowerBuilder. So the staffs can do it by themselves without bothering the admin.

Again, thank you so much

 

[miguelleeuwe]

Yes you can execute any sqlserver commands that are not included in 'embedded sql' of powerbuilder by using the
function

exectute immediate :ls_sql;
for example:
------------
string Mysql
Mysql = "CREATE TABLE Employee "&
+"(emp_id integer not null,"&
+"dept_id integer not null, "&
+"emp_fname char(10) not null, "&
+"emp_lname char(20) not null)"
EXECUTE IMMEDIATE :Mysql ;

if you need to pass values to be returned you can use for example (attention this uses sqlSa and not sqlCa):
-----------------------------------------------------
These statements prepare a DELETE statement with one parameter in SQLSA and then execute it using the value of the PowerScript variable Emp_id_var:

INT Emp_id_var = 56

PREPARE SQLSA
FROM "DELETE FROM employee WHERE emp_id=?" ;
EXECUTE SQLSA USING :Emp_id_var ;

These statements prepare an INSERT statement with two parameters in SQLSA and then execute it using the value of the PowerScript variables Dept_id_var and Dept_name_var (note that Dept_name_var is null):
INT Dept_id_var = 156
String Dept_name_var

SetNull(Dept_name_var)
PREPARE SQLSA
FROM "INSERT INTO dept VALUES (?,?)" ;
EXECUTE SQLSA USING ept_id_var,ept_name_var ;

(above information you can obtain it from powerbuilder help file, searching "dynamic sql" (syntax 1 and 2).
)

The only thing is you'll probably have to close down your (and everybody's connections) before you can do the 'dump' 'load' operations.
for the correct syntax of doing a dump in sqlserver I'd have to look around, but I'm sure google will we a quicker solution).

have fun,
Miguel L.


regards,
Miguel L.

 

[miguelleeuwe]

you might have to connect to the 'master' database (if there is one in sqlserver) to do the dump/load operations).


regards,
Miguel L.

 

[miguelleeuwe]

another and maybe more maintainable solution would be to write stored procedures that you launch from powerbuilder that would do the same thing as you might do with 'execute immediate ...'.



regards,
Miguel L.

 

[JThan]

I'm working on that and will let you know the result

Thanks

 

[JThan]

I forgot to ask this question. Does PowerBuilder has any function or command that I can use to copy the image files from the server to the local computer?

Thanks

 

[miguelleeuwe]

I think you can use the FileCopyA function (part of the windows API) or you can simply create your batch file that copies everything you need: copy_all.bat.
From powerbuilder you can do a: Run("copy_all.bat")


regards,
Miguel L.

 

[JThan]

Thank you so much it's all working now

 

[miguelleeuwe]

No thanks, (I responded your shortcut keys question again, check it out)...

regards,
Miguel L.

 

[miguelleeuwe]

oeps, that last question wasn't yours


regards,
Miguel L.

 

[mbalent]

Depending upon the situation, you may want to consider using replication within SQL server. This way you can control how the data is sync'ed between the 'main' database and the 'on the go' database.

Matt

"Nature forges everything on the anvil of time