?
Solved

Read data from text file and insert into datawindow and then into databse

Posted on 2004-11-29
39
Medium Priority
?
342 Views
Last Modified: 2013-12-26
Hi All,

I have a text file with only one column. I want to read it and insert data into database temp table. Then based on the indserted values in need to get data from different tables including temp table.

What is the best way to do this?
can i make a join statment with temp table?

Thanks,
Abhishek
0
Comment
Question by:vjabhi
  • 20
  • 17
  • 2
39 Comments
 
LVL 33

Expert Comment

by:sajuks
ID: 12702738
How many rows will the text file contain ? You could directly insert the txt file using the importfile ( either in a datastore or datawindow).Then check the values.
Are you planning to do this in a stored procedure ? You should be able to do the joins as needed.
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12702752
You could also do  a FileRead.Check the example in PB help file
0
 

Author Comment

by:vjabhi
ID: 12702885
There are going to be around 1000 rows but only one column.
i tried following, but this doesnt seems to work as return value i get is 0.

long ret
ret = dw_1.ImportFile ("C:\custextid.txt",1,1000,1,1,1)

Messagebox("rownum",String(ret))

Thanks,
Abhishek
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 33

Expert Comment

by:sajuks
ID: 12702907
You want to import the whole data dont you ? then just use
ret = dw_1.ImportFile ("C:\custextid.txt")
Messagebox("rownum",String(ret))
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 12702943
HI Abhi,

another alternative is ...

long ret
ret = dw_1.ImportFile ("")

Messagebox("rownum",String(ret))

passing a null filename will prompt u to select a file.... select ur file and all the rows from ur file will get imported into the dw... all other arguments are ignored hence no point in passing them...

Let us know if u need more assistance...

Cheers,
Rosh
0
 

Author Comment

by:vjabhi
ID: 12702998
Hi Rosh

I tried the way u said but mesage displayed is 0. It never prompted to select any file.

Thanks,
Abhishek
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12703016
string ls_null

SetNull(ls_null)
dw_1.ImportFile(ls_null)
0
 

Author Comment

by:vjabhi
ID: 12703025
still messagebox prints 0
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12703054
Is your dataobject an external datawindow or based on some table
0
 

Author Comment

by:vjabhi
ID: 12703182
Hi Got it working..thanks..
now i have id in the data window1, now i want write a query which will result me data based on all these id in datawindow1

Thanks,
Abhishek

0
 
LVL 33

Expert Comment

by:sajuks
ID: 12703193
Use the id as an retrieval argument and build the dataobject accordingly.
For eg the sql might look like select fname,lname from table where id = :al_id
where al_id is the passed argument obtained from the file import.
Then just do a dw_1.Retrieve(id)
0
 

Author Comment

by:vjabhi
ID: 12703307
Hi Saju..
thanks for help..
can u tell me how can i create temp table at run  time and insert all the data which i read from text file to datawindow into temp table.

Can define then another datawindow which takes data from some tables and this temp table?

Thanks,
Abhishek
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12703332
Create a permanent temporary table.Let the dw_1 dataobject be based on this temp table.
Then when you import the data to the datawindow you could directly update the dw itself issuing a lds_datastore.Update.
(prior to calling import file ensure that the table records are deleted ).  Now you could create another dataobject which has a join between the temp permannet table and the other permanent table.



0
 

Author Comment

by:vjabhi
ID: 12703565
Hi Saju,

i am trying following,
ret = dw_getextid.ImportFile(ls_nll)

If ret > 0 Then
    lUpdateStatus = dw_getextid.Update()
   if lUpdateStatus = 1 then
      commit using SQLCA;
   else
     
      RollBack using SQLCA;
      MessageBox( "Update", "Error in update" + &
                  "Error Message: " + SQLCA.SqlErrText )
      return 0
   end if

End if

I get error message saying, Datawindow doesnt have UPDATE capability.
what could be reason for this?

Thanks,
Abhi
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12703585
In the dataobject > rows > update properties > is it checked ?
The table needs to've a pk to allow update.
0
 

Author Comment

by:vjabhi
ID: 12703678
Hi,
I cant see any property like that.
i am using PB 6.5.
Table have only one coulmn and i have put index on that.
please suggest

Thanks,
Abhi
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12703725
Where did you check ? this is in the datawindow painter where you create the datawindow .
In the data window painter menu select Rows,Update Properties and
check the AllowUptades checkbox.
0
 

Author Comment

by:vjabhi
ID: 12703880
Hi,

can u tell me what i am mising here,
ret = dw_getextid.ImportFile(ls_nll)

If ret > 0 Then
      disconnect;
      SQLCA.DBMS = sDbms
      SQLCA.ServerName = ServerName
      SQLCA.Database = dbName
      SQLCA.logid = UserId
      SQLCA.logpass = Password
      CONNECT USING SQLCA;
      dw_getextid.SetTransObject(SQLCA)
      lUpdateStatus = dw_getextid.Update()
   if lUpdateStatus = 1 then
      commit using SQLCA;
   else
     
      RollBack using SQLCA;
      MessageBox( "Update", "Error in update" + &
                  "Error Message: " + SQLCA.SqlErrText )
      return 0
   end if

Erro rmessage i get saying,
Database Transaction Information not avaliable.
call setTrans or SetTransObject method

Thanks,
Abhi
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12703920
//constructor event
       SQLCA.DBMS = sDbms
     SQLCA.ServerName = ServerName
     SQLCA.Database = dbName
     SQLCA.logid = UserId
     SQLCA.logpass = Password
     CONNECT USING SQLCA;
     dw_getextid.SetTransObject(SQLCA)


in the clicked of the button
ret = dw_getextid.ImportFile(ls_nll)
If ret > 0 Then
     lUpdateStatus = dw_getextid.Update()
   if lUpdateStatus = 1 then
      commit using SQLCA;
   else
     
      RollBack using SQLCA;
      MessageBox( "Update", "Error in update" + &
                  "Error Message: " + SQLCA.SqlErrText )
     
   end if
end if

in the destructor event of the datawindow you could do the
disconnect();

0
 

Author Comment

by:vjabhi
ID: 12704024
still same issue,

actually i have use disconnect as before this my  connection was to different databse.

Thanks,
Abhi
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12704076
Instead of connecting to sqlca create another instance of transaction and connect using that method.
something like
transaction ln_tr
ln_tr = create transaction
  ln_trDBMS = sDbms
     ln_trServerName = ServerName
     ln_tr.Database = dbName
     ----

connect using  ln_tr;
dw_1.update(ln_tr)
disonnct using ln_tr
destroy ln_tr

and see if that works
0
 

Author Comment

by:vjabhi
ID: 12704155
still same problem.
why this issue when u set transaction object

Thanks,
Abhishek
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12704179
what  is the retrun value for  dw_getextid.SetTransObject(SQLCA) or dw_getextid.SetTransObject(ln_tr).
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 12704280
hi Abhi,

in the code pasted by sajuks have u done the following

connect using  ln_tr;

//the following set trans object line
dw_1.SetTransObject(ln_tr)

dw_1.update(ln_tr)
disonnct using ln_tr
destroy ln_tr


Cheers,
Rosh
0
 

Author Comment

by:vjabhi
ID: 12704321
hi,

its working some connection issue were there.
one quick one...
how do i delete table records prior to calling import file.

Thanks,
Abhishek
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12704369
issue a delete statement .:-)
delete from tablename ;
0
 

Author Comment

by:vjabhi
ID: 12704447
example please...
is it same as update thing??
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12704479
Just issue a simple delete command. I assume that the table name ,temporary table , is tmp_forimport.
then just do a delete from tmp_forimport; before importing.

In the click of the button for import


delete from tmp_forimport;
ret = dw_getextid.ImportFile(ls_nll)
if ret > 0 ....

You could also delete the table when not needed ie in the close of the window
0
 

Author Comment

by:vjabhi
ID: 12712666
Hi Saju,

all working fine now. thanks for help
I am reading connection from ini file.
it reads sometime.. and doesnt read some time...
this started only after i added new scrren with code for update datawindow.
if i dont cal this window its reads connection from ini else it doesnt.

Thanks,
Abhi
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12712753
Can you pinpoint in which case its not reading from the ini file ? You'd mentioned earlier that there are two different databases
involved.You disconenct from one then you connect to another for the import screen.After the close of the import screen do you reconnect to the other database and close this db connection ?
0
 

Author Comment

by:vjabhi
ID: 12712788
my question is .. everytime i read values from ini file when user open login screen. and i fill database server field. if user changes databse server i store that information in ini file on ok click.
Reading from ini file should happen all the times corret.
but when i work on GUI apart from mport screen everything is working as i want. But while running application if i use import screen then and then some other screens og GUI this doesnt happen.. it doesnt read ini file.
to read ini file i am writing..
sDbms = ProfileString("connection.ini","ABHI","dbms","")
ServerName = ProfileString("connection.ini","ABHI","servername","")
DBase = ProfileString("connection.ini","ABHI","DatabaseName","")

Thanks,
Abhishek

0
 
LVL 33

Expert Comment

by:sajuks
ID: 12712815
I've a doubt though not that sure, when you are calling the import file i assume you are allowing the user to select the file.That means he/she will be selecting the file location .Is it possible then that the path of your ini file is not found ?
Am not certain about it, instead of selecting the file hardcode it ( importfile(c:\anc.txt) ) and see if the error is still there.
If yes then you need to specify  the current directory again.See if that helps.
0
 

Author Comment

by:vjabhi
ID: 12713022
Hey!!
u are right...
i am putting all dll, ini and exe in one folder.
how can i set current folder to this one as soon as user open application

Thanks,
Abhishek
0
 
LVL 33

Accepted Solution

by:
sajuks earned 1000 total points
ID: 12713207
If you check in windows 98 iyou wont've this problem cos i believe it started with windows 2000 and such.
Also with pb9 and above you got a flag which you could set as false ( to indicate not to change the current directory).

What you could do is before selecting the  folder use DirList and a hidden listbox and static text to get
the name of the current directory and save it in a variable.After your import ( or update ) use DirList again with the
previously saved directory to set it back to the original directory.

something like this
Boolean Function wf_Get_directory( string as_pathname  by reference )
// Return the current directory pathname.
boolean lb_rtn

lb_rtn = lb_cache.DirList( "*.!!!", 1, st_cache )
as_pathname = st_cache.Text
RETURN lb_rtn

Boolean Function wf_Set_directory( string as_pathname  )
// Set the current directory.
boolean lb_rtn
string ls_directory

ls_directory = as_pathname + "\"
lb_rtn    = lb_cache.DirList( ls_directory, 0 )
RETURN lb_rtn

or USE API call
(1) Declare the external function:
Function Boolean of_SetCurrentDirectory32(String s_path) library "kernel32.dll"
alias for "SetCurrentDirectoryA"

(2) Use the external function:
lb_success = of_SetCurrentDirectory32("c:\mydir")
0
 

Author Comment

by:vjabhi
ID: 12713317
Thnaks ..
anyway i have made restiction to select file from ini file location.
Is it possible to click on listview coulm and sort based on that??

thanks,
Abhishek
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12713349
How would that help here ? Can you explain more ?
0
 

Author Comment

by:vjabhi
ID: 12713391
well.. as user will be running exe .. where i have ini file.. and the file  which i am importing so path will be same.
its just another query....
Is it possible to click on listview coulm and sort based on that column clicked??

thanks,
Abhishek
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12713430
another query another question ;-)
 Thats EE rules.
and yes it is possible to sort listview , check its function in the help file.
0
 
LVL 33

Expert Comment

by:sajuks
ID: 12713481
Glad it got all sorted out.
Thanks for the points and grade.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand column That will then direct you to their download page. From that p…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question