tnson
asked on
Insert unformated data with SQL into Database
Hi,
I’m working on a small thing where I have to save an encrypted string in a Database.
I’m using an Access database at the moment, but want to be able to change database engine later if necessary.
(What I'm trying to do is to break down a structure and save it in a database instead of on file as we do it today.)
The actual problem:
I can successfully store all data extracted from the structure in my database except for the encrypted string below.
/********* Encrypted String ***********/
ã‚[Á‚T ú 0 ÿÐ 0 ÿÐ ÿÐ 0 0 0 0ÿÐÿÐÿеkã+‰!TZ8½1ùcÐÅ$Þq¿¬žkÌ¿(¯Rõ<• &>kIŽ—cÞz 43¡[îùVœd qýHcáÙH@öK @Ô±ËÔJ•Ô †¹ó«¾í'Æã—;¦o+׎$’“ô3AÇ ,Š±h(!ŸW 4˜6~;%¦>׆7”Õí6 ìNMÙiÍÆÚºæÔÑMj +‰=ÌV]8„1ed¯ÅµPØؼ]eé< )óÜVu@”V>o H
½gQz4Zšp;‰)ãg͸”ÁÖt¿µ ëÈ~,ªv#Ó00kšâ7Ÿ žêN‚¨«Åo„wÕƒÐйÕd¼%é1LP= tŒ=i%A'F Ô~µ¿Û{QÆâ¯îL‡éI¼š¸õÐKj*ŽÁK·¢r¸|ð›_Å…p%&òL]
y6ì`*5<‹×Ñs¸Â²‡äÒË¢›ñÛ~î Ÿ‡MúËsÐ&ô£ 6R˜“ ð<ë©V ¬ñ@XѼ¶a_µqæ¶;€]’÷-b ÷(gÒ°k7À tÁÊd1vÚ3D™ˆþ0$–q«?îz\Kgå
Ðs‰Èú°}™“Õÿ›Òúø#‹ËVâè–¤ À1B°ª,™Ùé'ºXRup3Ñ{ú¢XÁ+i6@ ¶ Áq+ ZmY;ÌÃòºòPÓU(–¿Œ«½ '„0¥°{¥“;Þ±D;²£ç-qNÜKr¡{ÑW ~S`ü)†ÇÌCd :v©œ¬W&*•
-¯m
+ª•à+vÁ§¥Èü>›¯åµŸ%%Ž| ¬è³~¹ UÊ¿t(!lwM™f81¥eðË…QœhMúOlÀ)tÃ:UP7@ èAPR9 d ÿ f¨´y¸$}ÌX”ίÔXkÖtsÚ Äfæ” ^èÄ
ãêÈö$JU
K ‰@u—|)刅… ™¤z¾…
Â)
/************************* ********** ********** */
The problem is that when I try to store the encrypted data, I only succeed to save the first 11 characters for some reason.
(The field in the database is set as PM to be able to take anything very long.)
The encrypted data is stored in a structure that looks like this:
typedef struct bioapi_hrs_bir
{
BioAPI_BIR_HEADER Header;
BioAPI_BIR_BIOMETRIC_DATA_ PTR BiometricData; /* length indicated in */
/* header */
BioAPI_DATA_PTR Signature; /* NULL if no signature; length is inherent */
/* in this type */
} BioAPI_BIR, *BioAPI_BIR_PTR;
The encrypted biometric string is stored in pBIR->BiometricData (in my code)
If I save the whole structure into a file with fwrite it works, but when I want to create a string in SQL to save the same information in my database it doesnt.
(I would guess that it has to do with sprintf that stops at the next NULL.)
I create the string with sprintf (I know that sprintf stops at the next NULL, but I have also tryed to override this by specifying the precision)
sprintf(strSqlQuery,"INSER T INTO fingerprint(fID, fBIR, fhLength, fhType, fhfFormatId, fhfFormatOwner, fhQuality, fhPurpose, fhFactorsMask, fsLength, fsData) VALUES('%s', '%s', %d, %d, %d, %d, %d, %d, %d, %d, '%s')",strName, pBir->Biostring, pBIR->Header.Length, pBIR->Header.Type, pBIR->Header.Format.Format ID, pBIR->Header.Format.Format Owner, pBIR->Header.Quality, pBIR->Header.PurposeMask, pBIR->Header.FactorsMask, 0, "");
If i print the result of the abowe, it looks like this:
INSERT INTO fingerprint(fID, fBIR, fhLength, fhType, fhfFormatId, fhfFormatOwner, fhQuality, fhPurpose, fhFactorsMask, fsLength, fsData) VALUES('Tomas Nilsson4', 'ã‚[Á‚T', 879, 4, 1, 11, 91, 1, 8, 0, '')
Anyway, the problem is (as I wrote before) that only the first 11 characters gets stored in the database (ã‚ [Á‚ T ú)
Is there any way to do this without binding my self to a specific database?
I solved this problem before in VB by using ADO instead of writing the whole sql string, but I can’t bind towards Access this time.
Does anyone have any ideas?
( I understand if you don't understand what I'm trying to explain, but just post me and I'll try to do it better ;-) )
Thanks in advance!
Kind Regards, Tomas
I’m working on a small thing where I have to save an encrypted string in a Database.
I’m using an Access database at the moment, but want to be able to change database engine later if necessary.
(What I'm trying to do is to break down a structure and save it in a database instead of on file as we do it today.)
The actual problem:
I can successfully store all data extracted from the structure in my database except for the encrypted string below.
/********* Encrypted String ***********/
ã‚[Á‚T ú 0 ÿÐ 0 ÿÐ ÿÐ 0 0 0 0ÿÐÿÐÿеkã+‰!TZ8½1ùcÐÅ$Þq¿¬žkÌ¿(¯Rõ<•
½gQz4Zšp;‰)ãg͸”ÁÖt¿µ ëÈ~,ªv#Ó00kšâ7Ÿ žêN‚¨«Åo„wÕƒÐйÕd¼%é1LP=
y6ì`*5<‹×Ñs¸Â²‡äÒË¢›ñÛ~î
Ðs‰Èú°}™“Õÿ›Òúø#‹ËVâè–¤
-¯m
+ª•à+vÁ§¥Èü>›¯åµŸ%%Ž| ¬è³~¹ UÊ¿t(!lwM™f81¥eðË…QœhMúOlÀ)tÃ:UP7@
ãêÈö$JU
K ‰@u—|)刅… ™¤z¾…
Â)
/*************************
The problem is that when I try to store the encrypted data, I only succeed to save the first 11 characters for some reason.
(The field in the database is set as PM to be able to take anything very long.)
The encrypted data is stored in a structure that looks like this:
typedef struct bioapi_hrs_bir
{
BioAPI_BIR_HEADER Header;
BioAPI_BIR_BIOMETRIC_DATA_
/* header */
BioAPI_DATA_PTR Signature; /* NULL if no signature; length is inherent */
/* in this type */
} BioAPI_BIR, *BioAPI_BIR_PTR;
The encrypted biometric string is stored in pBIR->BiometricData (in my code)
If I save the whole structure into a file with fwrite it works, but when I want to create a string in SQL to save the same information in my database it doesnt.
(I would guess that it has to do with sprintf that stops at the next NULL.)
I create the string with sprintf (I know that sprintf stops at the next NULL, but I have also tryed to override this by specifying the precision)
sprintf(strSqlQuery,"INSER
If i print the result of the abowe, it looks like this:
INSERT INTO fingerprint(fID, fBIR, fhLength, fhType, fhfFormatId, fhfFormatOwner, fhQuality, fhPurpose, fhFactorsMask, fsLength, fsData) VALUES('Tomas Nilsson4', 'ã‚[Á‚T', 879, 4, 1, 11, 91, 1, 8, 0, '')
Anyway, the problem is (as I wrote before) that only the first 11 characters gets stored in the database (ã‚ [Á‚ T ú)
Is there any way to do this without binding my self to a specific database?
I solved this problem before in VB by using ADO instead of writing the whole sql string, but I can’t bind towards Access this time.
Does anyone have any ideas?
( I understand if you don't understand what I'm trying to explain, but just post me and I'll try to do it better ;-) )
Thanks in advance!
Kind Regards, Tomas
tomas,
I'm not sure if your DB can handle SQL statements containing characters with ASCII code > 128 (such as 'ÄÖÜ') directly. It should (maybe even with unicode), but don't bet on it.
Stefan
I'm not sure if your DB can handle SQL statements containing characters with ASCII code > 128 (such as 'ÄÖÜ') directly. It should (maybe even with unicode), but don't bet on it.
Stefan
ASKER
Hi,
Maybee the only way is to convert all characters in the string to their respective character code!?
What I would really like to do is to just dump the whole string in the table and read it back when necessary...... the easy way ;-)
if I should convert all characters in the string then..... what's the best way to go?
The string is over 800 characters long, so it might take some time to make the conversion, and time is something I have to be carefull with in this case.
Regards, Tomas
Maybee the only way is to convert all characters in the string to their respective character code!?
What I would really like to do is to just dump the whole string in the table and read it back when necessary...... the easy way ;-)
if I should convert all characters in the string then..... what's the best way to go?
The string is over 800 characters long, so it might take some time to make the conversion, and time is something I have to be carefull with in this case.
Regards, Tomas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, sounds like a good idea.
Do you have any ideas of how to do the conversion as well? =)
// Tomas
Do you have any ideas of how to do the conversion as well? =)
// Tomas
tnson,
> Do you have any ideas of how to do the conversion as well?
Unfortunately not... I don't have MS Access here.
Stefan
> Do you have any ideas of how to do the conversion as well?
Unfortunately not... I don't have MS Access here.
Stefan
The problem usually is with binary zeroes and proper escaping of characters.
Use the chr() function for your special characters (Oracle example, might need '+' instead of '||'):
select 'Hello World' || chr(13) || chr(10) || 'Next line' from dual
Cheers,
Stefan