Showing posts with label store. Show all posts
Showing posts with label store. Show all posts

Tuesday, March 27, 2012

Converting bytes [] to an SQL CE 3 image type to store

Hi,

I was wondering if anyone knows how to convert an array of bytes to an SQL CE 3 image type and vice versa.

I am using the SDF Signature control and I would like to store the signature as an Image. It needs to be an image so it can be synced with a desktop access 2003 database.

Cheers

Simon

I have got around this by changing the Image type to nText then converted the byte array to a base64 string and this works a treat.

Cheers

Simon.

|||

Better way to do it would be to create memory stream from byte array from SQL and pass it to bitmap constructor:

Bitmap bmp = new Bitmap(new MemoryStream(byteArrayFromSqlCe));

Thursday, March 22, 2012

Converting (too big)Raw into Hex

Hi there,

Here at my work(in Germany) i have a big problem:

Once a week there is a sqlscript, which should run and store data to a txt file.

There is a tablle in which imagedata is stored in RAW format(that one with small boxes, Qmarks and dots...).

Now i want to export it to the txtfile as HEX, so i can decode it by php further.

With the thumbnail, also stores there everything is fine, the SQL looks that way:

SELECt hex(thumbnail) FROM picTable where id_pic=54985

The Data is then displayed as HEX. But the standart picture is to big. When i use the statement:
SELECt hex(bigpic) FROM picTable where id_pic=54985

i got the error message:

An error occurred while running the query.

The data type, length, or value of argument 1 of HEX is invalid.

(SQL code = -171, SQL state = 42815)

After some research i found that the maximum lenght is 16 336 bytes for the HEX-operation. The picture is nearly 30 000 bytes... :shocked:
Have anyone an idea how to fix that?
I am not able here to accsess to the database direct...

I have been working for days on it.Uahh, wrong forum. Can anyone move this to SQL?|||the SQL forum is for standard SQL

i'm guessing you want this moved to the SQL Server forum

:)|||Still not solved...|||What is this "hex" function you are referring to? I could not find any reference to it in BOL (though it certainly would be a handy one to have!).

Regardless, assuming that there actually IS such an animal, can you consider breaking up the input data by sub-sections under your maximum size in the hex() function?

Such as using SUBSTRING ( expression , start , length )?

And then loop until the whole RAW data block is processed completely in bite-size chunks?

Tuesday, March 20, 2012

Convert word doc to XML and store and retrive form SQL 2000

Hi All,
i want to store the word document files as xml files in the sql server 2000 . then i have to retrive the XML Files in to aspx pages. the document files size are 5 mb , 6 mb like that . plz give the solution that how to do it .
Thanks in Advance .
Regards ,
raja

why do they need to be stored in a database? can't they just be stored as files - maybe index them in the database?

Regards

|||

I agree, why destroy those data caches with big useless blobs?

Sunday, February 19, 2012

Convert Informix to SQL Server

Hello, i need to convert this store procedure from Informix to SQL
Server and only obtain error's. Can anyone help me?
CREATE PROCEDURE atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
DECLARE @.aux_numcarta INT,
@.aux_numcarta1 INT,
@.var_medant LIKE mudmed.medant,
@.var_cartatipo LIKE mudmed.cartatipo,
@.var_ramo LIKE mudmed.ramo,
@.var_apolice LIKE mudmed.apolice,
@.var_dataalter LIKE mudmed.dataalter,
@.var_registo LIKE mudmed.registo,
@.aux_medant LIKE mudmed.medant,
@.aux_cartatipo LIKE mudmed.cartatipo,
@.aux_ramo LIKE mudmed.ramo,
@.aux_apolice LIKE mudmed.apolice,
@.aux_dataenvio LIKE mudmed.dataenvio,
@.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
@.ja_esta_em_trans INT,
@.sql_error_num INT,
@.isam_error_num INT,
@.error_msg VARCHAR(100)
--ON EXCEPTION
-- SET sql_error_num, isam_error_num, error_msg
IF ja_esta_em_trans <> 1
ROLLBACK WORK
RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
END EXCEPTION;
LET ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
LET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF aux_numcarta IS NULL
LET aux_numcarta = 0
IF aux_numcarta1 IS NULL
LET aux_numcarta1 = 0
IF aux_numcarta1 > aux_numcarta
LET aux_numcarta = aux_numcarta1
END IF
LET @.aux_medant = -1;
LET @.aux_cartatipo = -1;
LET @.aux_ramo = -1;
LET @.aux_apolice = -1;
LET @.aux_numcarta1 = -1;
LET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999))
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta = aux_numcarta + 1;
LET aux_numcarta1 = aux_numcarta;
IF aux_cartatipo == var_cartatipo AND
aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
LET aux_dataenvio = NULL;
ELSE
LET aux_ramo = var_ramo;
LET aux_apolice = var_apolice;
LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END IF;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
Thanks,
Apaxe2000If you understand t-SQL as well as the Informix's SQL dialect, it shouldn't
be hard. Instead of translating line by line, understand the overall logic
in the stored procedure and re-write using t-SQL.
Anith|||But the problem is i don't know SQL Server dialect and need to convert
this.
This is the only thing i don't know how resolve in the project i have
to do. Programming in VB is easy. Programming in SQL Server not.|||Apaxe2000 wrote:

> But the problem is i don't know SQL Server dialect and need to convert
> this.
> This is the only thing i don't know how resolve in the project i have
> to do. Programming in VB is easy. Programming in SQL Server not.
You need to spec the problem properly and work from a spec rather than
someone else's code. At least that may be what you'll have to do if you
want an answer from this forum.
The code you posted appears to be a cursor and that probably isn't the
best way to accomplish the same thing in SQL Server. But without more
information on keys, constraints and your data it's difficult to give
you a good answer.
The following article explains the best way to describe your problem
here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||It seems that you've tried converting the procedure but you are getting
errors.
Why not show us what you've done and the errors that you're getting.
You would have to be lucky to find someone here that knows both dialects and
is inclined to do all the work for you.
Besides, there are few comments in the procedure showing us what's going on
and we don't know your table structures.
@.var_medant LIKE mudmed.medant - nothing like this in SQL. You'll have to
use the parameter name @.var_medant in the procedure
LET = SET in SQL server
http://www.sommarskog.se/ for error handling in SQL server
FOREACH would be a Cursor in SQL server although you may be able to write
this without one
CURRENT = getdate()
"Apaxe2000" <Apaxe2000@.gmail.com> wrote in message
news:1135096937.302611.60280@.g14g2000cwa.googlegroups.com...
> Hello, i need to convert this store procedure from Informix to SQL
> Server and only obtain error's. Can anyone help me?
> CREATE PROCEDURE atribui_num_carta
> @.dia_env INT,
> @.mes_env INT,
> @.ano_env INT
> DECLARE @.aux_numcarta INT,
> @.aux_numcarta1 INT,
> @.var_medant LIKE mudmed.medant,
> @.var_cartatipo LIKE mudmed.cartatipo,
> @.var_ramo LIKE mudmed.ramo,
> @.var_apolice LIKE mudmed.apolice,
> @.var_dataalter LIKE mudmed.dataalter,
> @.var_registo LIKE mudmed.registo,
> @.aux_medant LIKE mudmed.medant,
> @.aux_cartatipo LIKE mudmed.cartatipo,
> @.aux_ramo LIKE mudmed.ramo,
> @.aux_apolice LIKE mudmed.apolice,
> @.aux_dataenvio LIKE mudmed.dataenvio,
> @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
> @.ja_esta_em_trans INT,
> @.sql_error_num INT,
> @.isam_error_num INT,
> @.error_msg VARCHAR(100)
> --ON EXCEPTION
> -- SET sql_error_num, isam_error_num, error_msg
> IF ja_esta_em_trans <> 1
> ROLLBACK WORK
>
> RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
> END EXCEPTION;
> LET ja_esta_em_trans = 0;
> BEGIN
> ON EXCEPTION IN (-535)
> LET ja_esta_em_trans = 1;
> END EXCEPTION;
> BEGIN WORK;
> END
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
> FROM mudmedhist_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> IF aux_numcarta IS NULL
> LET aux_numcarta = 0
> IF aux_numcarta1 IS NULL
> LET aux_numcarta1 = 0
> IF aux_numcarta1 > aux_numcarta
> LET aux_numcarta = aux_numcarta1
> END IF
>
> LET @.aux_medant = -1;
> LET @.aux_cartatipo = -1;
> LET @.aux_ramo = -1;
> LET @.aux_apolice = -1;
> LET @.aux_numcarta1 = -1;
> LET aux_dataenvio_comp = CURRENT;
> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
> IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
> OR ((var_medant>=800000 AND var_medant<=899999)
> OR (var_medant>=5003000 AND var_medant<=5003500) OR
> (var_medant>=5012000 AND var_medant<=5012999))
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta1 = -1;
> ELSE
> IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
> aux_cartatipo <> var_cartatipo
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta = aux_numcarta + 1;
> LET aux_numcarta1 = aux_numcarta;
> IF aux_cartatipo == var_cartatipo AND
> aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
> LET aux_dataenvio = NULL;
> ELSE
> LET aux_ramo = var_ramo;
> LET aux_apolice = var_apolice;
> LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
> END IF;
> UPDATE mudmed
> SET numcarta = aux_numcarta1,
> dataenvio = aux_dataenvio,
> dataenvio_comp = aux_dataenvio_comp
> WHERE registo = var_registo;
> END FOREACH ;
> IF ja_esta_em_trans <> 1 THEN
> COMMIT WORK;
> END IF;
> END PROCEDURE;
> Thanks,
> Apaxe2000
>|||Hi.
This is the procedure i used:
CREATE PROCEDURE dbo.atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
AS
DECLARE @.aux_numcarta INT
DECLARE @.aux_numcarta1 INT
DECLARE @.var_medant LIKE mudmed.medant
DECLARE @.var_cartatipo LIKE mudmed.cartatipo
DECLARE @.var_ramo LIKE mudmed.ramo
DECLARE @.var_apolice LIKE mudmed.apolice
DECLARE @.var_dataalter LIKE mudmed.dataalter
DECLARE @.var_registo LIKE mudmed.registo
DECLARE @.aux_medant LIKE mudmed.medant
DECLARE @.aux_cartatipo LIKE mudmed.cartatipo
DECLARE @.aux_ramo LIKE mudmed.ramo
DECLARE @.aux_apolice LIKE mudmed.apolice
DECLARE @.aux_dataenvio LIKE mudmed.dataenvio
DECLARE @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp
DECLARE @.ja_esta_em_trans INT
DECLARE @.sql_error_num INT
DECLARE @.isam_error_num INT
DECLARE @.error_msg VARCHAR(100)
IF @.ja_esta_em_trans <> 1 BEGIN
ROLLBACK WORK
RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
END EXCEPTION;
SET @.ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
SET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF (aux_numcarta) IS NULL BEGIN
SET @.aux_numcarta = 0
END
IF aux_numcarta1 IS NULL BEGIN
SET @.aux_numcarta1 = 0
END
IF aux_numcarta1 > aux_numcarta
SET @.aux_numcarta = @.aux_numcarta1
END
SET aux_medant = -1;
SET aux_cartatipo = -1;
SET aux_ramo = -1;
SET aux_apolice = -1;
SET aux_numcarta1 = -1;
SET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999)) BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta = aux_numcarta + 1;
SET aux_numcarta1 = aux_numcarta;
END
IF aux_cartatipo == var_cartatipo AND aux_ramo = var_ramo AND
aux_apolice = var_apolice BEGIN
SET aux_dataenvio = NULL;
ELSE
SET aux_ramo = var_ramo;
SET aux_apolice = var_apolice;
SET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
I obtain this errors:
Error 156: Incorrect Syntax near the keyword 'LIKE'
Line 32: Incorrect Syntax near 'EXCEPTION'
Incorrect Syntax near the keyword 'ON'
Incorrect Syntax near the keyword 'END'
Line 75: Incorrect Syntax near ','
Line 82: Incorrect Syntax near '='
Line 87: Incorrect Syntax near '='
Line 94: Incorrect Syntax near '='
Line 95: Incorrect Syntax near '='
Line 108: Incorrect Syntax near 'FOREACH'
Incorrect Syntax near the keyword 'END'
The variable are:
LIKE mudmed.medant -> integer
LIKE mudmed.cartatipo -> smallint
LIKE mudmed.ramo -> smallint
LIKE mudmed.apolice -> smallint
LIKE mudmed.dataalter -> datetime
LIKE mudmed.registo -> integer
LIKE mudmed.dataenvio -> datetime
LIKE mudmed.dataenvio_comp -> datetime
Thanks for your help,
Apaxe2000|||Apaxe2000 (Apaxe2000@.gmail.com) writes:
> This is the procedure i used:
I don't know if you seriously expect someone to rewrite your Informix
code to SQL Server for you for free. Since I don't know Informix, I
don't know how far from Informix you have strayed, but you have quite
some way to go, before you are in SQL Server land.
If you are not interesting in learning T-SQL, and this is just a one-off,
I suggest that you try the local phonebook for consultants.
If you want to learn SQL Server, I will give you some hints.

> DECLARE @.var_medant LIKE mudmed.medant
Oracle has a similar feature, but SQL Server does not. You can only
declare a variable to be of a certain type. You can create your
own data-type alias, so that you can say:
EXEC sp_addtype mytype, 'varchar(12)'
and then you can can use mytype both in tables and for variables. But
I suspect that this does not help you here, as your tables supposedly
already exists. You will have to declare @.var_medant explicitly as
whatever type you need.

> IF @.ja_esta_em_trans <> 1 BEGIN
> ROLLBACK WORK
> RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
Note that if you pass a number to RAISERROR, this must be a number >= 50000,
and should have been defined with sp_addmessage. The most common is to
pass a string to RAISERROR with the error message.
The other two parameters are severity and state. Severity should be >= 11
and <= 16 and state is best set to 1.

> BEGIN
> ON EXCEPTION IN (-535)
> SET ja_esta_em_trans = 1;
> END EXCEPTION;
This syntax is not in SQL Server. You did not say which SQL Server you
are using, and this is an area where there are great difference. In SQL
2005, you can say:
BEGIN TRY
.. statements here
END TRY
BEGIN CATCH
.. error handling here
END CATCH
But in SQL 2000, you have no other choice than checking the global
variable @.@.error after each statement. Notice that this variable changes
value after each statement, so you need to put it into a local variable.
Also, note that in SQL 2000, there are errors you cannot catch at all,
since they abort the batch and rolls back the current transaction.
As for what you should check for in your error handler, I don't know,
but -535 is not an error number in SQL Server.

> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
I don't know what this statement does in Informnix, but in SQL Server
it creates a table, and it fails since you did not provide a name
for the column. If aux_numcarta is a variable, the syntax is
SELECT @.aux_numcarta = Max(M1.NUMCARTA)
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = @.ano_env;
Assuming that DATAENVIO is indexes, the WHERE clause is best written as
WHERE M1:DATAENVIO BETWEEN ltrim(str(@.ano_env)) + '0101' AND
ltrim(str(@.ano_env)) + '1231'
This is because, if you put an indexed column into an expresson, you
nullify the benefit of the index. (I would suspect that this applies to
Informix as well.)

> SET aux_dataenvio_comp = CURRENT;
This means nothing in SQL Server.

> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
I don't know what this FORACH means, but it looks like a syntax for a
cursor. But looking at the code, I can't see anything that calls for
an iteration at all. You should proably rewrite this as a single
UPDATE statement in Informix first.
The CASE expression can be handy:
SELECT @.x = CASE WHEN @.y > 10 THEN 234
WHEN @.y > 6 AND @.w > 12 THEN 123
ELSE -23
END
This is ANSI, so it should work on Informix as well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||At 15/01/06 i go to another project in Oracle and VB.NET but i need to
do this before, but i continue obtain errors and can anyone helpme only
this time?
Thanks,
Apaxe2000|||Apaxe2000 wrote:
> At 15/01/06 i go to another project in Oracle and VB.NET but i need to
> do this before, but i continue obtain errors and can anyone helpme only
> this time?
> Thanks,
> Apaxe2000
Do you really think that "continue obtain errors" is an adequate
specification of a problem? No-one's likely to be able to offer much
help without a fuller description.
I don't want to give you bad advice based on my wild guesses about the
meaning of your legacy code. That's why I asked for the extra
information I did and gave you the article explaining how to do it. You
still haven't provided any DDL, sample data or even shown us what end
result you want. If you take the time to do that you'll be pretty sure
to get more responses.
David Portas
SQL Server MVP
--

Friday, February 10, 2012

convert datatype from db2 to sql

i am running a package which extracts result of count(x), count(y),sum(z) from db2(as400) servers

i want to store the result of all the three in a table to a column of datatype varchar(25).

can someone help me with the convert/cast function in db2 which i can include in the query.

something like : select cast(count(x) as varchar(25)) from lib.file

No, but we can help you use SSIS to convert them...

Have you searched IBM's site for a SQL reference document containing commands?|||

yaa Phil,even i thought it is better to use a data converter transformation.

the problem iam running into is iam trying to store int and decimal datatypes into the same output column.

which is a varchar.

the convertor works fine for int datatype but is giving error for decimal.

"datatype of output column 'colname' doesnot mach with the datatype 'system.decimal' of the source column 'columnname' "

"component DataReaderSource failed validation and returned validation status VS_NEEDSNEWMETADATA"

what i understand is it not problem with the conver transformation its problem with the data source reader.

let me be more clear i have 6 querys running agains AS400 in a loop 1,2,4,5 querys give output in int and querys 3,6 give output numeric.

when i put each of this querys(1,2,4,5) in datasource reader the output datasource reader pics DT_I4 as the result datatype and for querys 3,6 it pics DT_NUMERIC

i think i am left with option to change in the query itself

help needed.... thanks in advance