If I represent the statement string as

.data
szCreateMot db "Create table Mot?tg?rder (ID COUNTER, Titel TEXT)",0

the SQLExecDirect function is succeessful

But if I represent the statement string as

.data
szCreateMot db "Create table Mot?tg?rder ("
db "ID COUNTER, "
db "Titel TEXT)",0

the SQLExecDirect function fails.

I can't see any differences between the two ways of representation.

My question is. Is there any difference?

Regards
Posted on 2004-01-18 15:21:10 by minor28
well yes..

note that:


szCreateMot db "Create table Mot?tg?rder ("
db "ID COUNTER, "
db "Titel TEXT)",0


creates 3 variables, 1 with a name, and 2 with no name (yes it is possible).
thus to access the vars with no name u need to know their address, or offset from szCreateMot .
this is usually done with dummpy variables.
so if passing to ur SQL statement szCreateMot that is declaired above, it will send: "Create table Mot?tg?rder ("
such code above is usually for matrix.

note that, if adding CRLF -> "lala",CRLN (0Dh,0Ah) after each line execpt the last which will hold NULL will cause the whole text to be shown in for example MessageBox :), but still not good for ur sql statement.
good luck.
Posted on 2004-01-18 16:13:03 by wizzra
There is no difference between the two strings, datawise.

However, if you are using SIZEOF or LENGTH or similar operators, the second form will report a smaller size because SIZEOF does not look for a 0 byte.
Posted on 2004-01-18 16:26:38 by tenkey
Are you using any function to get the string length of szCreateMot? I don't quite remember the name of the masm keyword (LENGTH maybe?) but if thats what your using it will only return the string length of the variable szCreateMot and not the remainder of the string defined in db statements. Solultion hard code the length or use a real string length function.

EDIT
you beat me to it tenkey
Posted on 2004-01-18 16:26:56 by ENF
Not related to your problem, but I'd be cautious with using non-ascii for table names etc... probably won't cause you any trouble, but I wouldn't be surprised to find some SQL provider that has flaky handling of it.
Posted on 2004-01-18 17:28:33 by f0dder
Yes tenkey and ENF you were right. The cause of failer was due to "sizeof" and not to .data representation. Neither "lengthof" nor "lenght" works so all three seems not to look for a 0 byte.

This works
invoke lstrlen,addr szCreateMot ;szCreateTable1

invoke SQLExecDirect,hStmt,addr szCreateMot,eax


f0dder. I have never experienced any problems with using non-ascii for table names, at least not in the Swedish MSAccess application. In fact however, it is not necessary to use non-ascii for table names in this case because the user should not operate direct in the database only through my application. Perhaps it would be better to follow your advise not to use non-ascii.

Thanks
Posted on 2004-01-19 01:11:16 by minor28
Well, you probably won't have problems with microsoft access, but other SQL/ODBC vendors might b0rk - especially if you use some smaller 3rd party stuff. I generally treat stuff like table names like variable names: standard ascii only. Data content should be fine, though - even if some vendors might have problems with sorting :)
Posted on 2004-01-19 04:56:11 by f0dder
In connection to the previous question I have an additional question.

It is in fact a SQL question but the application is masm. After hours of attempts and seaching in SQL reference I have to ask if anyone know how to change a column property to allow a zero length sting.
.data

szCreateTable db "Create table TName ("ID COUNTER, CName TEXT)",0

.code
invoke lstrlen,addr szCreateTable
invoke SQLExecDirect,hStmt,addr szCreateTable,eax


For example "CName TEXT NOT NULL" change required property to yes

Regards
Posted on 2004-01-19 06:21:54 by minor28
Well, it seems as if there is nobody who knows the answer of my last question. My seach after an answer have ended with the conclusion that the zero length string property isn't standard, and it's not something you can change with standard SQL.

For those who are interested, my solution to the problem is as follows.
.data

szCreatePrioTable db "Create table Priority (PriorityID INT,Prioritytext TEXT,Date DATE)",0

This will set the columns property of reguired to no (default). To insert a new item or update an item you have to check if the value is zero length before execution. This could be done with the function below where first parameter is address of buffer and second parameter is a boolean for string or not.
QuotationMarks proc uses esi edi edx pString:dword,ascii:BOOL

LOCAL no:dword

invoke lstrlen,pString
mov no,eax
.if no==0
invoke lstrcpy,pString,addr szNull ;db "NULL",0

.elseif ascii==TRUE
xor ecx,ecx
xor eax,eax
lea esi,pString
mov esi,[esi]
mov al,byte ptr [esi+ecx]
mov byte ptr [esi+ecx],"'"
.while no!=0
inc ecx
xchg al,byte ptr [esi+ecx]
dec no
.endw
mov byte ptr [esi+ecx+1],"'"
mov byte ptr [esi+ecx+2],0
.endif
ret

QuotationMarks endp



I hope this will be of use for database coders.

Regards
Posted on 2004-01-22 16:06:01 by minor28