| View previous topic :: View next topic |
| Author |
Message |
Henrik
Valued Newbie
Joined: 09 Jul 2000
Posts: 35
Location: Copenhagen, Denmark
|
 Posted: Mon Feb 18, 2002 8:55 pm Post subject: Using multiple line values with VDSDB's queries ? |
 |
|
Hi guys !
I am trying to use multiple lines in a record field with the VDS DB DLL
I use this testing code:
The problems seems to lie in the fact that %%CODE / test.txt is a multiple line string and it appears that only one line strings are accepted with the VDS DB when making queries. However %%EXTFILES and %%TODO are also multi-line and they don't cause problems
Therefore: How do I make the %%CODE string into a oneline string making it possible to split it back into a multiline string
Code:
| Code: |
rem ***
rem *** DB CREATE TABLE
rem ***
:DB_CREATE
db query,DROP TABLE PROJECTS;
rem dialog set,status1,Opretter database ...
list add,FILE,CREATE TABLE PROJECTS(SnippetID int",
list add,FILE,Project varchar(80)",
list add,FILE,SName varchar(100)",
list add,FILE,SDesc varchar(200)",
list add,FILE,Deadline varchar(9)",
list add,FILE,Language varchar(40)",
list add,FILE,Todo text",
list add,FILE,Status varchar(100)",
list add,FILE,Extfiles text",
list add,FILE,Code text
list add,FILE,");"
%%QUERY = @text(FILE)
db query,%%QUERY
list clear,FILE
rem *** TESTING
list create,6
list loadfile,6,c:\windows\skrivebord\test.txt
%%CODE = @text(6)
dialog set,TEMP,%%CODE
%9 = %%CODE
gosub SEARCH_AND_REPLACE
%%CODE = %9
info %%CODE
%%SNIPPETID = 1
%%PROJECT = TESTPROJECT
%%SNIPPETNAME = Test Snippet
%%SNIPPETDESCRIPTION = This is a test snippet
%%DEADLINE = 01/01/01
%%LANGUAGE = Java/java
%%STATUS = Not Done
rem *** TO DO SHOULD BE GRABBED FROM AN EDIT BOX
%%TODO = There is nothing@cr()I can do@cr()about it !
rem *** EXTFILES IS A LIST OF FILES ON THE DIALOG
%%EXTFILES = c:\windows\skrivebord\1.txt@cr()c:\windows\skrivebord\1.txt
%%QUERY = INSERT INTO PROJECTS VALUES('%%SNIPPETID'","'%%PROJECT'","'%%SNIPPETNAME'","'%%SNIPPETDESCRIPTION'","'%%DEADLINE'","'%%LANGUAGE'","'%%TODO'","'%%STATUS'","'%%EXTFILES'","'%%CODE');
db query,%%QUERY
wait 1
rem *** GET TESTDATA
db query,"SELECT * FROM PROJECTS;"
db fetchrow
if @ok()
%a =
repeat
%a = %a@cr()CODE: @db(get,Code)@cr()SNIPPETID: @db(get,SnippetID)@cr()PROJECT: @db(get,Project)@cr()SNIPPET NAME: @db(get,Sname)@cr()DESCRIPTION: @db(get,SDesc)@cr()STATUS: @db(get,Status)@cr()DEADLINE: @db(get,Deadline)@cr()LANGUAGE: @db(get,Language)@cr()TODO: @db(get,Todo)@cr()EXTFILES: @db(get,Extfiles)@cr()DONE
db fetchrow
until @not(@ok())
info %a
end
exit
|
_________________
Henrik Skov
Email: henrikskov@mail.dk |
|
| Back to top |
|
 |
Tommy
Admin Team
Joined: 16 Nov 2002
Posts: 746
Location: The Netherlands
|
| Posted: Tue Feb 19, 2002 6:51 pm Post subject: |
|
|
Hi Henrik,
I think it works fine if you duplicate quotes first (so that they won't be considered string ending quotes):
| Code: |
option errortrap,error
DIALOG CREATE,New Dialog,-1,0,240,160
DIALOG ADD,LIST,FILE,8,8,224,144
DIALOG SHOW
directory change,@path(%0)
external vdsdb.dll
db connect,vdsworld,admin,
rem ***
rem *** DB CREATE TABLE
rem ***
:DB_CREATE
rem db query,DROP TABLE PROJECTS;
rem dialog set,status1,Opretter database ...
list add,FILE,CREATE TABLE PROJECTS(SnippetID int",
list add,FILE,Project varchar(80)",
list add,FILE,SName varchar(100)",
list add,FILE,SDesc varchar(200)",
list add,FILE,Deadline varchar(9)",
list add,FILE,Language varchar(40)",
list add,FILE,Todo text",
list add,FILE,Status varchar(100)",
list add,FILE,Extfiles text",
list add,FILE,Code memo
list add,FILE,");"
%%QUERY = @text(FILE)
db query,%%QUERY
list clear,FILE
rem *** TESTING
list create,6
list loadfile,6,c:\dsc2html\dsc2html.dsc
%%CODE = @text(6)
list close,6
rem dialog set,TEMP,%%CODE
rem %9 = %%CODE
rem gosub SEARCH_AND_REPLACE
rem %%CODE = %9
%%addslashes = %%CODE
gosub addslashes
%%CODE = %%addslashes
%%SNIPPETID = 1
%%PROJECT = TESTPROJECT
%%SNIPPETNAME = Test Snippet
%%SNIPPETDESCRIPTION = This is a test snippet
%%DEADLINE = 01/01/01
%%LANGUAGE = Java/java
%%STATUS = Not Done
rem *** TO DO SHOULD BE GRABBED FROM AN EDIT BOX
%%TODO = There is nothing@cr()I can do@cr()about it !
rem *** EXTFILES IS A LIST OF FILES ON THE DIALOG
%%EXTFILES = c:\windows\skrivebord\1.txt@cr()c:\windows\skrivebord\1.txt
%%QUERY = INSERT INTO PROJECTS VALUES('%%SNIPPETID'","'%%PROJECT'","'%%SNIPPETNAME'","'%%SNIPPETDESCRIPTION'","'%%DEADLINE'","'%%LANGUAGE'","'%%TODO'","'%%STATUS'","'%%EXTFILES'","'%%CODE"');"
db query,%%QUERY
wait 1
rem *** GET TESTDATA
db query,"SELECT * FROM PROJECTS;"
db fetchrow
if @ok()
%a =
repeat
%a = %a@cr()CODE: @db(get,Code)@cr()SNIPPETID: @db(get,SnippetID)@cr()PROJECT: @db(get,Project)@cr()SNIPPET NAME: @db(get,Sname)@cr()DESCRIPTION: @db(get,SDesc)@cr()STATUS: @db(get,Status)@cr()DEADLINE: @db(get,Deadline)@cr()LANGUAGE: @db(get,Language)@cr()TODO: @db(get,Todo)@cr()EXTFILES: @db(get,Extfiles)@cr()DONE
db fetchrow
until @not(@ok())
info %a
end
exit
:addslashes
%%len = @len(%%addslashes)
if @greater(%%len,0)
%i = 0
repeat
%i = @succ(%i)
%%char = @substr(%%addslashes,%i)
if @equal(%%char,')
%%addslashes = @strins(@strdel(%%addslashes,%i),%i,'')
%i = @succ(%i)
end
until @greater(@succ(%i),%%len)
end
exit
:stripslashes
%%len = @len(%%stripslashes)
if @greater(%%len,0)
%i = 0
repeat
%i = @succ(%i)
%%char = @substr(%%stripslashes,%i)
if @equal(%%char,')
%%stripslashes = @strdel(%%stripslashes,%i)
end
until @equal(%i,%%len)
end
exit
:error
warn Error: @error(E)@cr()@db(sqlerror)@cr()@db(odbcerror)@cr()
|
Please note that :stripslashes isn't used or neccesary, it would only be needed if you would like to revert your text after you apply :addslashes. It mustn't be applied on results returned by the DLL, for those the additional quote that was added has already been removed by the DLL.
Remember that the MS-Access's text fields' maximum length is 255, for longer texts you could use a "memo" field instead.
Also you must keep in mind the difference inbetween just @CR() and @CR()@CHR(10) (CR-LF). CR-LF usually appears in Windows, for example it's returned from lists if using @TEXT(). Not all applications recognize @CR() on its own as a line feed, for example MS-Access doesn't.
Best regards,
Tommy |
|
| Back to top |
|
|
Henrik
Valued Newbie
Joined: 09 Jul 2000
Posts: 35
Location: Copenhagen, Denmark
|
| Posted: Tue Feb 19, 2002 9:16 pm Post subject: |
|
|
Hi Tommy !
I did solve it eventually and it was of course the slashes making trouble (as is the case in any programming language almost), but I also found that | cannot be used so I write routines to strip and add slashes to the data.
However, there are a few things that I learned from your reply, mainly the thing about the memofield. I tried text because in MySQL this is a truely variable-length field if I recall correctly and I have mainly worked with MySQL.
Also, it was great to clear up @cr() contra @chr() problem as I could not figure that one out but I knew I did something a long time ago to avoid problems.
Anyway, do you know why the MDB file grows from approx. 44 KB when empty with no tables and to almost 8 MB when I continously CREATEs the above tabke, adds one record, DROP the table and then runs the code over again ? I found that you could compress it wih the Control Panel, but it would be nice to have this functionality accessible from within the DLL
Thanks Tommy !
Henrik
_________________
Henrik Skov
Email: henrikskov@mail.dk |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
@vdsworld