Author |
Topic |
user182368
Starting Member
5 Posts |
Posted - 2012-12-24 : 14:42:53
|
Hi there,I have an SQL Server Compact Edition database file which has .sdf extension. The database is created by a book cataloging application. The database has many tables, among them a table called "Book" which contains one row for each book in the database. The table has many columns among them a column called "FrontCover" which contains a string value which has 2 parts: path part and file name part of the image file for the front cover of a book, for example:Documents and Settings\Boson\My Documents\Books\Covers\0596003978.jpgIn this example path part is:Documents and Settings\Boson\My Documents\Books\Coverswhile file part is:0596003978.jpgSome books do not contain any value for the column "FrontCover" because the front cover is not available. For such books column "FrontCover" is empty. However if a book has a front cover image file then the string value has the same path part but different file part. For example for another book column "FrontCover" has this value:Documents and Settings\Boson\My Documents\Books\Covers\1590596633.jpgAs we can see the path part is the same as in the first example, namely:Documents and Settings\Boson\My Documents\Books\Coversbut the file part is different:1590596633.jpg------------------------------------------------------------PROBLEM:------------------------------------------------------------I want to change table "Book" so that string values of the column "FrontCover" are modified in such a way that file part is kept the same but the path part is changed from:Documents and Settings\Boson\My Documents\Books\CoverstoBooks\AEM database\CoversThe string value of the column "FrontCover" for the book in the first example would thus change from:Documents and Settings\Boson\My Documents\Books\Covers\0596003978.jpgtoBooks\AEM database\Covers\0596003978.jpgFile part is the same but the path part is changed.The book cataloging application which owns the .sdf database file is stupid and cannot do the job. Therefore I have installed a simple open source SQL viewing/editing application called CompactView (http://sourceforge.net/p/compactview/home/Home/) which runs on top of free Microsoft SQL Server Compact Edition 4.0 run-time. CompactView can open .sdf database files and accepts SQL commands in order to modify the .sdf database file.Can you please help me with the SQL commands which can do the job?Thank you very much in advance for your help.best regards |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-24 : 15:26:13
|
in t-sql you can use REPLACE for thissomething likeUPDATE BooksSET FrontCover=REPLACE(FrontCover,'Documents and Settings\Boson\My Documents\Books\Covers\','Books\AEM database\Covers\')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-24 : 15:28:35
|
One of these maybe? The first works only for your example, where jpgs start with a number, the secondDECLARE @From varchar(1000) = 'Documents and Settings\Boson\My Documents\Books\Covers\0596003978.jpg'DECLARE @to varchar(100) = 'Books\AEM database\Covers\'select @From, STUFF(@from,1,PATINDEX('%[0-9]%',@from),@to),STUFF(@FROM,1,LEN(@FROM) - PATINDEX('%\%',REVERSE(@FROM))+1,@to)JimEveryday I learn something that somebody else already knew |
|
|
user182368
Starting Member
5 Posts |
Posted - 2012-12-25 : 06:32:59
|
quote: Originally posted by visakh16 in t-sql you can use REPLACE for thissomething likeUPDATE BooksSET FrontCover=REPLACE(FrontCover,'Documents and Settings\Boson\My Documents\Books\Covers\','Books\AEM database\Covers\')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Thank you for your answer, however the instructions you proposed caused a software exception to occur in CompactView application which I use to edit .sdf files. I also tried another open source application which can open .sdf files and execute SQL queries, namely SQL Compact Query Analyzer (http://sqlcequery.codeplex.com/) and it reported the error message:"The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = REPLACE ]"Do you know an application which can execute T-SQL queries and edit .sdf (SQL Server Compact Edition database files)?thank you. |
|
|
user182368
Starting Member
5 Posts |
Posted - 2012-12-25 : 06:41:52
|
quote: Originally posted by jimf One of these maybe? The first works only for your example, where jpgs start with a number, the secondDECLARE @From varchar(1000) = 'Documents and Settings\Boson\My Documents\Books\Covers\0596003978.jpg'DECLARE @to varchar(100) = 'Books\AEM database\Covers\'select @From, STUFF(@from,1,PATINDEX('%[0-9]%',@from),@to),STUFF(@FROM,1,LEN(@FROM) - PATINDEX('%\%',REVERSE(@FROM))+1,@to)JimEveryday I learn something that somebody else already knew
Thank you for your answer.Since I do not know SQL could you please clarify me if the code section you proposed updates the whole "Book" table or just one field for one book entry (from your code: 'Documents and Settings\Boson\My Documents\Books\Covers\0596003978.jpg')I need an SQL query which has to update the whole "Book" table! That is to say in every row (i.e. for each book entry) in the "Book" table I need to replace part of the string value from 'Documents and Settings\Boson\My Documents\Books\Covers' to 'Books\AEM database\Covers'. The ending part of the string (file name part) has to be unchanged. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-26 : 00:09:06
|
quote: Originally posted by user182368
quote: Originally posted by visakh16 in t-sql you can use REPLACE for thissomething likeUPDATE BooksSET FrontCover=REPLACE(FrontCover,'Documents and Settings\Boson\My Documents\Books\Covers\','Books\AEM database\Covers\')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Thank you for your answer, however the instructions you proposed caused a software exception to occur in CompactView application which I use to edit .sdf files. I also tried another open source application which can open .sdf files and execute SQL queries, namely SQL Compact Query Analyzer (http://sqlcequery.codeplex.com/) and it reported the error message:"The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = REPLACE ]"Do you know an application which can execute T-SQL queries and edit .sdf (SQL Server Compact Edition database files)?thank you.
whats data type of FrontCover column?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
user182368
Starting Member
5 Posts |
Posted - 2012-12-26 : 04:38:36
|
quote: Originally posted by visakh16
quote: Originally posted by user182368
quote: Originally posted by visakh16 in t-sql you can use REPLACE for thissomething likeUPDATE BooksSET FrontCover=REPLACE(FrontCover,'Documents and Settings\Boson\My Documents\Books\Covers\','Books\AEM database\Covers\')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Thank you for your answer, however the instructions you proposed caused a software exception to occur in CompactView application which I use to edit .sdf files. I also tried another open source application which can open .sdf files and execute SQL queries, namely SQL Compact Query Analyzer (http://sqlcequery.codeplex.com/) and it reported the error message:"The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = REPLACE ]"Do you know an application which can execute T-SQL queries and edit .sdf (SQL Server Compact Edition database files)?thank you.
whats data type of FrontCover column?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
According to CompactView (it generates SQL Schema in a separate tab with details about the Book table) column FrontCover is of type NTEXTI first thought that the SQL parser in the application I use does not accept REPLACE function (normally it highlights the reserved words it recognizes and it doesn't highlight REPLACE), however I modified the command you proposed and I typed:UPDATE BookSET FrontCover = REPLACE ('Covers12345Covers67890','Covers','ABCDEF')and after executing the query all the rows in the table Book have been changed in the column FrontCover with this string value:ABCDEF12345ABCDEF67890This means that the function REPLACE has been recognized and that it does not accept FrontCover as the first argument. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-26 : 07:36:00
|
>>column FrontCover is of type NTEXTREPLACE function won't allow NTEXT variable.. So covert it to REPLACE (CAST(FrontCover AS NVARCHAR(MAX)) ,'Covers','ABCDEF')--Chandu |
|
|
user182368
Starting Member
5 Posts |
Posted - 2012-12-26 : 08:21:44
|
quote: Originally posted by bandi >>column FrontCover is of type NTEXTREPLACE function won't allow NTEXT variable.. So covert it to REPLACE (CAST(FrontCover AS NVARCHAR(MAX)) ,'Covers','ABCDEF')--Chandu
Thank you very much for your valuable input. That was improvement because I do not receive any more the previous error code, however I had to replace MAX with a number. After typing:UPDATE BookSET FrontCover = REPLACE (CAST(FrontCover AS NVARCHAR(100)), 'Documents and Settings\Boson\My Documents\Books\Covers\', 'Books\AEM database\Covers\')The table is successfully updated.Thank you all very much for your valuable help. Merry Christmas and Happy New Year. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-26 : 08:40:37
|
If FrontCover is of below 8000 size data, then replace that MAX with 8000--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-26 : 23:45:30
|
quote: Originally posted by user182368The table is successfully updated.Thank you all very much for your valuable help. Merry Christmas and Happy New Year.
Welcome.. wish you the same--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-26 : 23:54:32
|
quote: Originally posted by user182368
quote: Originally posted by bandi >>column FrontCover is of type NTEXTREPLACE function won't allow NTEXT variable.. So covert it to REPLACE (CAST(FrontCover AS NVARCHAR(MAX)) ,'Covers','ABCDEF')--Chandu
Thank you very much for your valuable input. That was improvement because I do not receive any more the previous error code, however I had to replace MAX with a number. After typing:UPDATE BookSET FrontCover = REPLACE (CAST(FrontCover AS NVARCHAR(100)), 'Documents and Settings\Boson\My Documents\Books\Covers\', 'Books\AEM database\Covers\')The table is successfully updated.Thank you all very much for your valuable help. Merry Christmas and Happy New Year.
compact wont support blob data (max) so you've to use number------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|